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.