UNLOAD (tables)

Use the UNLOAD (tables) utility to unload data into a sequential data set. You can choose to replace any existing data with the new data, or to add the new data to the data already in the data set. The UNLOAD (tables) utility provides the most efficient means of adding large amounts of data to a data set.

When you use the UNLOAD utility, you can:
  • Select the columns to be unloaded by specifying the required columns in the From template
  • Reformat data during the unload by mapping table columns to fields in the output data set (as defined by a template)
  • Create and save a File Manager "base" template that describes the unloaded data
  • Select which rows to unload
  • Specify the number of rows to be unloaded

To include a PUNCHDDN clause in the generated UNLOAD statement, specify the required template name in PUNCHDDN template name on the second TEMPLATE Options panel.

To include a UNLDDN clause in the generated UNLOAD statement, specify the required template name in UNLDDN template name for the UNLOAD statement on the second TEMPLATE Options panel.

The following description applies if you did not specify the name of a LISTDEF on the Db2® Utilities panel. If you did specify the name of a LISTDEF on the Db2® Utilities panel, see UNLOAD (tables) with LISTDEF specified.

On the UNLOAD Utility (Tables) panel:

  1. The table name details you entered on the Db2® Utilities panel are copied to the UNLOAD Utility (Tables) panel. If you do not want to unload all of the rows in the source table, specify the number of rows you want to unload in the Unload Count field.
  2. If you are using a specific template for the source table, specify the name of the template in the From Template entry fields.
  3. In the Processing Options, specify the template you want FM/Db2 to use for the source table, and whether you want to edit the template before FM/Db2 builds the utility batch job.
  4. To proceed, press Enter.
    If you selected the Edit template option, FM/Db2 displays the Column Selection/Edit panel. Use this panel to restrict the data in the source table by:
    • Selecting or deselecting specific columns for unloading
    • Specifying row selection criteria. FM/Db2 selects only the rows whose contents satisfy the criteria for unloading
    For example, the template shown in Column Selection/Edit panel showing row selection criteria and selected columns has been edited to select certain columns and only rows whose contents meet certain criteria. If you use this template as the From template for an unload, the data unloaded is restricted to:
    • The columns FIRSTNME, LASTNAME, WORKDEPT, and JOB, but only where WORKDEPT equals MNT and JOB equals PAINTER.
    Figure 1. Column Selection/Edit panel showing row selection criteria and selected columns
       Process   Options   Utilities   Help
 ────────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)               Column Selection/Edit                 Line 1 of 16
 TABLE FMNUSER.EMP
 ------- Row Selection Criteria ---- (Use SQL/PF4 for full screen edit) -------
 1 Sel: WHERE WORKDEPT = 'MNT' AND JOB = 'PAINTER'                            +

 Cmd Seq SHEX CL# Column name          Data type(length)  Null Default  Order A/D
                  ****  Top of data  ****
                1 EMPNO                CHARACTER(6)            None             
         S      2 FIRSTNME             VARCHAR(12)             None             
                3 MIDINIT              CHARACTER(1)            None             
         S      4 LASTNAME             VARCHAR(15)             None             
         S      5 WORKDEPT             CHARACTER(3)        Y   Null             
                6 PHONENO              CHARACTER(4)        Y   Null             
                7 HIREDATE             DATE                Y   Null             
         S      8 JOB                  CHARACTER(8)        Y   Null             
                9 EDLEVEL              SMALLINT            Y   Null             
               10 SEX                  CHARACTER(1)        Y   Null             
               11 BIRTHDATE            DATE                Y   Null             
               12 SALARY               DECIMAL(9,2)        Y   Null             
  Command ===> _____________________________________________________ Scroll PAGE
   F1=Help      F2=Split     F3=Exit      F4=SQL       F5=RFind     F6=RunTemp
   F7=Up        F8=Down      F9=Swap     F10=Left     F11=Right    F12=Cancel
    If you did not select the Edit template option (or have completed the edit of the template option), and the value of the UNLDDN template name field on the TEMPLATE Options (2 of 2) panel is blank, FM/Db2 displays the UNLOAD Utility To panel.
    Note: If the UNLDDN template name field is not blank, FM/Db2 does not display the UNLOAD Utility To panel as the output is already defined.
  5. To temporarily change the settings for the UNLOAD options that FM/Db2 uses in building the current batch job, select Edit UNLOAD utility options to display the UNLOAD Options panel.

    If you change any of the original settings, they remain in effect only for as long as you use the Db2® Utilities UNLOAD feature in which the settings were changed. If you exit from the UNLOAD Utility (Tables) panel and return to this panel later, the settings changed in the previous UNLOAD session are unavailable.

  6. On the UNLOAD Utility To panel, specify the sequential data set that is the target of the unload by entering the To data set details using the Data set name and, optionally, Volume, entry fields.
  7. If you are using a specific template or copybook for the To data set, you can specify the name of the template or copybook data set and, optionally, the member name, in the To Data Template or Copybook entry fields.

    The data format used by the Db2® UNLOAD utility is fixed. Therefore, if you specify a copybook or template data set, the copybook or template should reflect the Db2® UNLOAD data format to avoid data mismatch problems.

    When a template is specified for the Db2® unload data set, it must be a File Manager "base" template; a FM/Db2 template cannot be used.

    When the Allow padding option on the Db2® UNLOAD utility options panel is not selected, FM/Db2 cannot generate a template that describes the unloaded data. In this situation, all fields related to template processing are protected when the UNLOAD utility To panel is displayed and the template usage option is set to 5 (NONE).

  8. Select the Template usage and Disposition options you want.
  9. You can review the Db2® UNLOAD options by selecting the View UNLOAD options field. No changes can be made when the Db2® UNLOAD utility options panel is displayed. To make changes return to the Db2® UNLOAD utility From panel and select Edit UNLOAD options.
  10. To edit the template mapping for the source table, select Edit template mapping to display the Template Mapping panel.
  11. To proceed, press Enter.

    FM/Db2 builds a batch job to execute the Db2® UNLOAD utility job using the specified input details.

    Sample JCL generated for UNLOAD (Tables) shows an example of the generated JCL for unloading a table object.
    Figure 2. Sample JCL generated for UNLOAD (Tables)
       File  Edit  Edit_Settings  Menu  Utilities  Compilers  Test  Help
────────────────────────────────────────────────────────────────────────────────
 EDIT       FMNUSER.SPFTEMP1.CNTL                           Columns 00001 00072
 ****** ***************************** Top of Data ******************************
 000001 //FMNUSERB JOB (IBMGSA),
 000002 //             FMNUSER,MSGCLASS=A,
 000003 //             NOTIFY=FMNUSER,CLASS=A,
 000004 //             MSGLEVEL=(1,1)
 000005 //*
 000006 //         JCLLIB ORDER=(DB2V810.DFA2.PROCLIB)
 000007 //*
 000008 //**********************************************************************
 000009 //*
 000010 //* FM/Db2 GENERATED JOB TO UNLOAD A TABLE
 000011 //*
 000012 //**********************************************************************
 000013 //*
 000014 //**********************************************************************
 000015 //* STEP UNLOAD: UNLOAD FROM TABLE
 000016 //*   "FMNUSER"."EMP"
 000017 //**********************************************************************
 000018 //UNLOAD1  EXEC DSNUPROC,SYSTEM=DFA2,UID='FMNUSER'
 000019 //DSNUPROC.STEPLIB  DD DSN=DB2V810.DFA2.SDSNEXIT,DISP=SHR
 000020 //         DD DSN=DB2.V810.SDSNLOAD,DISP=SHR
 000021 //DSNUPROC.SYSREC00 DD DISP=(MOD,CATLG,DELETE),UNIT=SYSALLDA,
 000022 //         BLKSIZE=0,
 000023 //         AVGREC=U,
 000024 //         LRECL=82,RECFM=FB,DSORG=PS,
 000025 //         SPACE=(82,(42,4),RLSE),
 000026 //         DSN=FMNUSER.FRED
 000027 //DSNUPROC.SYSPUNCH DD DISP=(NEW,CATLG,DELETE), UTIL CONTRL DATA
 000028 //         UNIT=SYSALLDA,SPACE=(TRK,(1,1),RLSE),
 000029 //         DSN=FMNUSER.FMNUSER.EMP.D2581536.SYSPUNCH
 000030 //DSNUPROC.SYSIN    DD *
 000031 UNLOAD DATA FROM TABLE
 000032 "FMNUSER"."EMP"
 000033   HEADER NONE
 000034   LIMIT ALL
 000035   (
 000036    "FIRSTNME          " POSITION(1     ) VARCHAR
 000037   ,"LASTNAME          " POSITION(15    ) VARCHAR
 000038   ,"WORKDEPT          " POSITION(33    ) CHAR(3)
 000039   ,"JOB               " POSITION(38    ) CHAR(8)
 000040   ,"BONUS             " POSITION(48    ) DECIMAL
 000041   ,"COMM              " POSITION(66    ) DECIMAL
 000042   )
 000043   WHEN
 000044   (
 000045   "WORKDEPT" = 'MAINT' AND "JOB" = 'PAINTER'
 000046   )
 000047   UNLDDN(SYSREC00)
 000048   NOSUBS
 000049 /*
 ****** **************************** Bottom of Data ****************************

 Command ===>                                                  Scroll ===> PAGE
  F1=Help      F2=Split     F3=Exit      F5=Rfind     F6=Rchange   F7=Up
  F8=Down      F9=Swap     F10=Left     F11=Right    F12=Cancel

Related tasks

Related references