Load DB2 Table Information by Unload job

This job can be used before creating the extraction method. Be sure to specify a sequential unload of the files and insert the process identifier for reading the file.

Complete the values listed in the Secondary Options window for the Load DB2 Table Information by Unload job as shown below:

Insert the fields related to the sequential unload:

SYSPUNCH or DCLGEN name
The name of the SYSPUNCH or DCLGEN file.
Unload data set name
The name of the unload data set.
Process identifier
Process identifier used for the management of DB2 files with sequential access.
Application identifier
Application identifier (if applicable).

For the z/OS solution, the only DB2 utility for the UNLOAD of table supported by Data Express is DSNTIAUL or a direct JCL Unload with some limitations.

The following is a sample of the JCL: 

“//DSURUNL2  JOB (00001),DMP,MSGCLASS=A,CLASS=1,NOTIFY=&SYSUID           
//JOBLIB   DD DISP=SHR,DSN=DSN810.SDSNLOAD
//* ---------------------------------------------------------------- *
//UNLOAD3  EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB  DD DISP=SHR,DSN=DSN810.SDSNLOAD
//SYSTSPRT DD  SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN  DD  *
 DSN SYSTEM(HADB)
 RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIB81) -
       LIB('DB2HAL.RUNLIB.LOAD')
//SYSREC00  DD DSN=URADAR.RUMYANA.SYSREC.D44444,
//             DISP=(NEW,CATLG,CATLG),
//             UNIT=SYSDA,SPACE=(TRK,(30,30))
//SYSPUNCH  DD DSN=URADAR.RUMYANA.SYSPUN(D44444),
//             DISP=OLD
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
             "D444444444444444444444444444444444444444444444444444444444
4444444444444444444444444444444444444444444444444444444444444444444444".
"D4444444444444444444444444444444444444444444444444444444444444444444444
444444444444444444444444444444444444444444444444444444444"
“

The following is a sample SYSPUNCH generated by the JCL described above: 

“
  LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
      "D4444444444444444444444444444444444444444444444444444444444444444
444444444444444444444444444444444444444444444444444444444444444"."D44444
444444444444444444444444444444444444444444444444444444444444444444444444
44444444444444444444444444444444444444444444444444"
   (
   CARD_TYPE                              POSITION(       1         )
   CHAR(                      1) ,
   CARD_NUM                               POSITION(       2         )
   CHAR(                     16) ,
   ACCOUNT_OPER                           POSITION(      18:      30)
   DECIMAL                       ,
   DATE_OPRT                              POSITION(      31         )
   CHAR(                      8)
   )
“

The following is a sample of the UNLOAD's JCL: 

“//DSURUNL8  JOB (00001),DMP,MSGCLASS=A,CLASS=1,NOTIFY=&SYSUID
//JOBLIB   DD DISP=SHR,DSN=DSN810.SDSNLOAD
//* ---------------------------------------------------------------- *
//UNLOAD3  EXEC DSNUPROC,SYSTEM=HADB,UID='UNLOA1',
//  UTPROC=''
//STEPLIB  DD DISP=SHR,DSN=DSN810.SDSNLOAD
//SYSREC    DD DSN=URADAR.STEFANO.SYSREC.PIPPO,
//             DISP=(NEW,CATLG,CATLG),
//             UNIT=SYSDA,SPACE=(TRK,(30,30))
//SYSPUNCH  DD DSN=URADAR.STEFANO.SYSPUNCH.PIPPO,
//             DISP=(NEW,CATLG,CATLG),
//             UNIT=SYSDA,SPACE=(TRK,(1,1))
//SYSIN     DD *
    UNLOAD FROM TABLE
 NRT40NAME_TESTXM.TEST_LENGTH37CHARACT
 HEADER NONE
“

The previous JCl is supported only for those tables having owner name and or table name not longer than 40 character.

The following SYSPUNCH sample is the only format generated by the JCL described above which is supported by Data Express: 

“
LOAD DATA INDDN SYSREC   LOG NO  RESUME YES
 EBCDIC  CCSID(00037,00000,00000)
 INTO TABLE
 "NRT40NAME_TESTXM".
 "TEST_LENGTH37CHARACT"
 ( "CODCUS"
  POSITION(  00001:00010) CHAR(00010)
 , "NAME"
  POSITION(  00011:00040) CHAR(00030)
 , "SURNAME"
  POSITION(  00041:00070) CHAR(00030)
 )
 “

The following SYSPUNCH sample is the BMC UNLOAD format which is supported by Data Express: 

LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
    OWURSVIL.BMCTAB99
 (
 CODCUS99                               POSITION(        1:        5)
 DECIMAL
      NULLIF BMC_NULL1=X'6F',
      BMC_NULL1 POSITION(6) CHAR (1),
 NAME99                                 POSITION(        7:       26)
 CHAR (20)
      NULLIF BMC_NULL2=X'6F',
      BMC_NULL2 POSITION(27) CHAR (1),
 SURNAME99                              POSITION(       28:       47)
 CHAR (20)
      NULLIF BMC_NULL3=X'6F',
      BMC_NULL3 POSITION(48) CHAR (1),
 ADDRESS99                              POSITION(       49:       88)
 CHAR (20)
      NULLIF BMC_NULL4=X'6F',
      BMC_NULL4 POSITION(89) CHAR (1),

The following SYSPUNCH sample is a format generated by the JCL described above which is not supported by Data Express: 

“
LOAD DATA INDDN SYSREC   LOG NO  RESUME YES
 EBCDIC  CCSID(00037,00000,00000)
 SORTKEYS                  72
 INTO TABLE
 "D444444444444444444444444444444444444444" ||
 "4444444444444444444444444444444444444444" ||
 "4444444444444444444444444444444444444444" ||
 "44444444".
 "D444444444444444444444444444444444444444" ||
 "4444444444444444444444444444444444444444" ||
 "4444444444444444444444444444444444444444" ||
 "44444444"
 ( "CARD_TYPE"
  POSITION(  00001:00001) CHAR(00001)
 , "CARD_NUM"
  POSITION(  00002:00017) CHAR(00016)
 , "ACCOUNT_OPER"
  POSITION(  00018:00030) DECIMAL
 , "DATE_OPRT"
  POSITION(  00031:00038) CHAR(00008)
 )
 “
Parent topic: Job creation