DSNUTILB UNLOAD DB2 TABLE INTO DATASET

Like DSNTIAUL, another utility DSNUTILB can also be used to unload a table into a dataset.

//UNLDTBL  JOB (DSNTEP4),'UNLDTBL',REGION=0M,CLASS=2,MSGCLASS=X,
//             NOTIFY=&SYSUID
//*------------------------------------------*
//DEL      EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSIN    DD *
  DELETE <UID>.TEST.UNLDTBL.LOG
  DELETE <UID>.TEST.UNLDTBL.DATA
  DELETE <UID>.TEST.UNLDTBL.SYSPUNCH
  IF MAXCC = 8 THEN SET MAXCC=0
//*
//*------------------------------------------*
//* CREATE FILES
//*------------------------------------------*
//CRFILES  EXEC PGM=IEFBR14
//SYSREC   DD DSN=<UID>.TEST.UNLDTBL.DATA,
//            SPACE=(CYL,(1,1),RLSE),
//            DISP=(MOD,DELETE,DELETE),
//            UNIT=SYSDA
//SYSPUNCH DD DSN=<UID>.TEST.UNLDTBL.SYSPUNCH,
//            SPACE=(CYL,(1,1),RLSE),
//            DISP=(MOD,DELETE,DELETE),
//            UNIT=SYSDA
//SYSPRINT DD SYSOUT=*
//*------------------------------------------*
//* LAUNCH UNLOAD UTILITY
//*------------------------------------------*
//UNLOAD   EXEC PGM=DSNUTILB,PARM=('<DSN>,'),REGION=0K
//STEPLIB  DD  DSN=DSN910.SDSNEXIT,DISP=SHR
//         DD  DSN=DSN910.SDSNLOAD,DISP=SHR
//         DD  DSN=DSN910.RUNLIB.LOAD,DISP=SHR
//SYSPRINT DD DSN=<UID>.TEST.UNLDTBL.LOG,
//            SPACE=(CYL,(1,1),RLSE),
//            DISP=(,CATLG),
//            UNIT=SYSDA
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
  TEMPLATE TSYSPUN
    DSN('<UID>.TEST.UNLDTBL.SYSPUNCH')
  TEMPLATE TSYSREC
    DSN('<UID>.TEST.UNLDTBL.DATA')

  UNLOAD DATA FROM TABLE TAB
  (
     ID
    ,NAME
    ,ADDRESS
    ,EMAIL
    ,PHONE
  )
  UNLDDN(TSYSREC) PUNCHDDN(TSYSPUN)
/*

After submit JCL job:

1. <UID>.TEST.UNLDTBL.SYSPUNCH: define the generated dataset data structure

TEMPLATE <TEMPLATE> DSN('<UID>.TEST.UNLDTBL.DATA') DISP(OLD,KEEP,KEEP)
LOAD DATA INDDN <INDD> LOG NO  RESUME YES
 EBCDIC  CCSID(<CCSID>)
 INTO TABLE
 "<SCHEMA>".
 "TESTTAB"
 WHEN(00001:00002) = X'0003'
 NUMRECS  4                
 ( "ID"      POSITION(00003:00005) DECIMAL PACKED,
   "NAME"    POSITION(00006:00011) CHAR(00006) ,
   "ADDRESS" POSITION(00012:00023) VARCHAR ,
   "EMAIL"   POSITION(00025:00032) VARCHAR NULLIF(00024)=X'FF' ,
   "PHONE"   POSITION(00034:00037) CHAR(00004) NULLIF(00033)=X'FF'
 )

2. <UID>.TEST.UNLDTBL.DATA is the data

          [pad] [  ID  ] [      NAME     ] [LEN] [          ADDRESS          ][I] [LEN] [     EMAIL     ][I] [  PHONE  ]

00000000: 00 03 00 00 1C C1 C1 C1 C1 C1 C1 00 0A 81 81 81 81 81 81 81 81 81 81 00 00 06 C1 C1 C1 81 81 81 00 F1 F1 F1 F1 : .....AAAAAA..aaaaaaaaaa...AAAaaa.1111

00000001: 00 03 00 00 2C C2 C2 C2 C2 C2 C2 00 0A 82 82 82 82 82 82 82 82 82 82 FF 00 00 40 40 40 40 40 40 00 F2 F2 F2 F2 : .....BBBBBB..bbbbbbbbbb...      .2222

00000002: 00 03 00 00 3C C3 C3 C3 C3 C3 C3 00 0A 83 83 83 83 83 83 83 83 83 83 00 00 06 C3 C3 C3 83 83 83 FF 40 40 40 40 : .....CCCCCC..cccccccccc...CCCccc.  

00000003: 00 03 00 00 4C C4 C4 C4 C4 C4 C4 00 0A 84 84 84 84 84 84 84 84 84 84 FF 00 00 40 40 40 40 40 40 FF 40 40 40 40 : ....<DDDDDD..dddddddddd...      .    

3. Difference between utility DSNUTILB and DSNTIAUL

   1. DSNTIAUL use traditional SELECT SQL statement to query data; and DSNUTILB use UNLOAD sub-command

   2. Output data format for DSNTIAUL is fixed-length dataset, and DSNUTILB is variable-length dataset.