Loading data described by a copybook or template

You can use the LOAD utility to load data in a sequential data set, providing a copybook or template exists that describes the format of the input data.

In this case, you use a series of panels:
  • On the LOAD Utility panel, you specify the name of the data set containing the input data, and the name of the target table.
  • On the second panel, you specify the From copybook or template that describes the input data.
  • On the third panel, you can specify the To template that describes the target table. Alternatively, you can use FM/Db2 to generate a template for the target table using the information in the Db2® catalog.

    At this stage, you can define the mapping between fields in the input template and fields in the output template.

To load data described by a copybook or template:

  1. In the entry fields for input data, specify the name of the data set (Data set name), and optionally the name of the member (Member), containing the data to be loaded.
  2. In the entry fields for the target Db2® table (Table owner, Table name, and optionally Database and Table space), specify the name of the table into which the data is to be loaded. In any of these fields, you can enter either an asterisk (*) to display a selection list, or wildcards.
  3. Select option 2 (The data is described by a template or copybook)
  4. Press Enter.
    If you have specified a default name for an INDDN clause (in the INDDN template name field on the second TEMPLATE Options panel), FM/Db2 displays an interim panel showing the default name (for example, LODINDDN) in the Template name field for the input data: 
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)                    LOAD Utility

 Input Data:
    Template name . . . LODINDDN

 Target Db2 Table:
                                                Database . .          (optional)
    Owner . . . . . . . FMNUSER           +     Table space           (optional)
    Name  . . . . . . . EMP                                                 +










 Command ===>                                                                  
  F1=Help      F2=Split     F3=Exit      F4=Expand    F7=Backward  F8=Forward
  F9=Swap     F10=Left     F11=Right    F12=Cancel

    FM/Db2 displays a panel where you can specify the input template or copybook.

  5. Specify the From template details. Perform one of these actions:
    • Specify a copybook or template:
      • In the entry fields for the input template or copybook, specify the name of the data set (Data set name), and optionally the name of the member (Member), for the template or copybook that describes the format of the input data.
      • Select 1 (Above) for the Template usage option.
    • Use the last (previously used) template:
      • Select 2 (Previous) for the Template usage option.
  6. To edit the template for the input data before proceeding to the LOAD utility To template panel, select the View template option. You can select this option by entering either a ⁄ or an “A”.
    Note: The template edit operations have no effect when the template is used to generate Db2® LOAD utility control statements. If you select this option, you cannot update and save changes to the template.
  7. Press Enter.

    FM/Db2 displays a panel where you can specify the template for the target Db2® table.

  8. Specify the To template details using a combination of the To Template and Template usage entry fields. You can:
    • Specify a template that describes the format of the input data:
      • Use Data set name, and optionally Member, to specify the template.
      • Select 1 (Above).
    • Use the last (previously used) template:
      • Select 2 (Previous).
    • Use FM/Db2 to generate a template using the information in the Db2® catalog:
      • Select 3 (Generate from table).
    • Use FM/Db2 to generate a template using the information in the Db2® catalog and store the generated template using the name you specify (if the member already exists, FM/Db2 replaces it):
      • Use Data set name, and optionally Member, to specify the name that you want FM/Db2 to use when storing the generated template.
      • Select 4 (Generate/Replace).
  9. To change the options for the current LOAD session, select Edit load utility options by entering either a ⁄ or an “A”.

    FM/Db2 displays the Load Utility Options panel for editing.

    Note: Any options you set on this panel apply for the current LOAD session only. When you exit from the LOAD session, the load options revert to the global LOAD options.
  10. To map the columns (or fields) in the “From” template to the columns in the “To” template, select Edit load utility options by entering either a ⁄ or an “A”. FM/Db2 displays the Template Mapping panel.
  11. Press Enter.
    FM/Db2 constructs a batch job to run the Db2® LOAD utility job using the input details you have specified. Sample JCL generated for LOAD Utility using data described by a copybook or template (continued in next figure) shows an example of generated JCL for loading a table using data described by a copybook or template.
    Figure 1. Sample JCL generated for LOAD Utility using data described by a copybook or template (continued in next figure)
       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 LOAD A TABLE
 000011 //*
 000012 //**********************************************************************
 000013 //*
 000014 //**********************************************************************
 000015 //* STEP SETPARM: OVERRIDE ANY GLOBAL DATASET SIZING VALUES HERE
 000016 //**********************************************************************
 000017 //SETPARM SET ALLOC='TRK',PRI='1',SEC='1'
 000018 //*
 000019 //**********************************************************************
 000020 //* STEP LOAD: LOAD THE TABLE
 000021 //**********************************************************************
 000022 //LOAD     EXEC DSNUPROC,SYSTEM=DFA2,UID='FMNUSER'
 000023 //DSNUPROC.STEPLIB  DD DSN=DB2V810.DFA2.SDSNEXIT,DISP=SHR
 000024 //         DD DSN=DB2.V810.SDSNLOAD,DISP=SHR
 000025 //DSNUPROC.SYSREC00 DD DISP=SHR,             INPUT DATA
 000026 //         DSN=FMNUSER.DATA(FMNCDATA)
 000027 //DSNUPROC.SYSDISC  DD SYSOUT=*              DISCARDED RECORDS
 000028 //DSNUPROC.SYSERR   DD UNIT=SYSALLDA,
 000029 //         SPACE=(TRK,(1,1))                 ERROR INFORMATION
 000030 //DSNUPROC.SYSMAP   DD UNIT=SYSALLDA,
 000031 //         SPACE=(TRK,(1,1))                 INTERNAL MAPPING DATA SET
 000032 //DSNUPROC.SYSUT1   DD UNIT=SYSALLDA,
 000033 //         SPACE=(&ALLOC,(&PRI,&SEC),RLSE)
 000034 //DSNUPROC.SORTOUT  DD UNIT=SYSALLDA,
 000035 //         SPACE=(&ALLOC,(&PRI,&SEC),RLSE)
 000036 //DSNUPROC.SYSIN    DD *
 000037 LOAD DATA
 000038 PREFORMAT
 000039 LOG YES
 000040 INDDN SYSREC00
 000041 RESUME YES
 000042 FLOAT(IEEE)
 000043 ASCII
 000044 SORTKEYS 100
 000045 SORTDEVT SYSDA
 000046 INTO TABLE
 000047 "FMNUSER"."EMP"
 000048 (
 000049  "EMPNO             " POSITION(1     :2     ) CHAR(6)
 000050 ,"SALARY            " POSITION(27    :30    ) DECIMAL
 000051 )
 ****** **************************** 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