DSNTIAUL UNLOAD DB2 TABLE INTO DATASET

来源：互联网发布：王奕霏编程编辑：程序博客网时间：2024/06/05 17:08

DB2 Utility DSNTIAUL can be used to unload a table to a dataset.

Suppose table is defined as:

  CREATE TABLE TESTTAB(        ID                   DECIMAL(4, 0)   NOT NULL,        NAME           CHAR(06)             NOT NULL,        ADDRESS     VARCHAR(10)     NOT NULL,        EMAIL          VARCHAR(06),        PHONE          CHAR(04),        PRIMARY KEY(ID)  );

The DSNTIAUL JCL can be:

//UNLDTBL   JOB (DSNTEP4),'UNLDTBL',REGION=0M,CLASS=2,MSGCLASS=X,//             NOTIFY=&SYSUID//*------------------------------------------*//* UNLOAD THE RDBMS TABLE: TESTTAB//* INTO <UID>.TEST.UNLDTBL.DATA//*------------------------------------------*//* DELETE LOG, DATA AND SYSPUNCH FILES//*------------------------------------------*//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//*------------------------------------------*//* LAUNCH DSNTIAUL UTILITY//*------------------------------------------*//UNLOAD   EXEC PGM=IKJEFT01,DYNAMNBR=20//STEPLIB  DD DSN=DSN.SDSNEXIT,DISP=SHR//                  DD DSN=DSN910.SDSNLOAD,DISP=SHR//                  DD DSN=DSN910.RUNLIB.LOAD,DISP=SHR//SYSTSPRT DD SYSOUT=*//SYSTSIN  DD *  DSN SYS(<DSN>)  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') -    LIBRARY ('DSN910.RUNLIB.LOAD')  END//SYSPRINT DD SPACE=(CYL,(1,1),RLSE),//            DISP=(,CATLG),//            UNIT=SYSDA,//            DSN=<UID>.TEST.UNLDTBL.LOG//SYSUDUMP DD SYSOUT=*//SYSREC00 DD SPACE=(CYL,(5,5),RLSE),//            DISP=(,CATLG),//            UNIT=SYSDA,//            DSN=<UID>.TEST.UNLDTBL.DATA//SYSPUNCH DD SPACE=(TRK,(1,1),RLSE),//            DISP=(NEW,CATLG),DCB=(LRECL=80,RECFM=FB),//            UNIT=SYSDA,//            DSN=<UID>.TEST.UNLDTBL.SYSPUNCH//SYSIN    DD *  SELECT ID,NAME,ADDRESS,EMAIL,PHONE FROM TESTTAB;/*

Before you can submit above job, table and data must be prepared firstly

//PREPTBL  JOB (DSNTEP4),'PREPTBL',REGION=0M,CLASS=2,MSGCLASS=X,//             NOTIFY=&SYSUID//*------------------------------------------*//* CREATE TABLE//*------------------------------------------*//CRTABLE0 EXEC PGM=IKJEFT01,DYNAMNBR=20//SYSTSPRT DD  SYSOUT=*//SYSTSIN  DD  *  DSN SYSTEM(<DSN>)  RUN PROGRAM(DSNTEP4) PARMS('ALIGN(LHS)') -      LIB('DSN910.RUNLIB.LOAD')//STEPLIB  DD DSN=DSN910.SDSNEXIT,DISP=SHR//         DD DSN=DSN910.SDSNLOAD,DISP=SHR//         DD DSN=DSN910.RUNLIB.LOAD,DISP=SHR//         DD DSN=<DSN>.SDSNEXIT,DISP=SHR//SYSPRINT DD SYSOUT=*//SYSIN    DD *  CREATE TABLE TAB(        ID          DECIMAL(4, 0)   NOT NULL,        NAME        CHAR(06)        NOT NULL,        ADDRESS     VARCHAR(10)     NOT NULL,        EMAIL       VARCHAR(06),        PHONE       CHAR(04),        PRIMARY KEY(ID)  );/*//*------------------------------------------*//* INSERT TABLE DATA//*------------------------------------------*//INTABLE1 EXEC PGM=IKJEFT01,DYNAMNBR=20//SYSTSPRT DD  SYSOUT=*//SYSTSIN  DD  *  DSN SYSTEM(<DSN>)  RUN PROGRAM(DSNTEP4) PARMS('ALIGN(LHS)') -      LIB('DSN910.RUNLIB.LOAD')//STEPLIB  DD  DSN=DSN910.SDSNLOAD,DISP=SHR//SYSPRINT DD  SYSOUT=*//SYSIN    DD *  INSERT INTO TAB(ID, NAME, ADDRESS, EMAIL, PHONE)       VALUES (1, 'AAAAAA', 'aaaaaaaaaa', 'AAAaaa', '1111');  INSERT INTO TAB(ID, NAME, ADDRESS, PHONE)       VALUES (2, 'BBBBBB', 'bbbbbbbbbb', '2222');  INSERT INTO TAB(ID, NAME, ADDRESS, EMAIL)       VALUES (3, 'CCCCCC', 'cccccccccc', 'CCCccc');  INSERT INTO TAB(ID, NAME, ADDRESS)       VALUES (4, 'DDDDDD', 'dddddddddd');/*

Submit this JCL job, 2 dataset can be created:

1. <UID>.TEST.UNLDTBL.SYSPUNCH : data structure description file.

  LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE TESTTAB(   ID      POSITION(1:3) DECIMAL,   NAME    POSITION(4)   CHAR(6),   ADDRESS POSITION(10)  VARCHAR,   EMAIL   POSITION(22)  VARCHAR NULLIF(30)='?',   PHONE   POSITION(31)  CHAR(4) NULLIF(35)='?'  )

2. <UID>.TEST.UNLDTBL.DATA : the real data file, record length is 35:

[ ID   ] [  NAME         ] [LEN] [ ADDREE                    ] [LEN] [  EMAIL        ] [I][  PHONE  ] [I]00000000: 00 00 1C C1 C1 C1 C1 C1 C1 00 0A 81 81 81 81 81 81 81 81 81 81 00 06 C1 C1 C1 81 81 81 00 F1 F1 F1 F1 00 : ...AAAAAA..aaaaaaaaaa..AAAaaa.1111.00000001: 00 00 2C C2 C2 C2 C2 C2 C2 00 0A 82 82 82 82 82 82 82 82 82 82 00 00 00 00 00 00 00 00 6F F2 F2 F2 F2 00 : ...BBBBBB..bbbbbbbbbb........?2222.00000002: 00 00 3C C3 C3 C3 C3 C3 C3 00 0A 83 83 83 83 83 83 83 83 83 83 00 06 C3 C3 C3 83 83 83 00 00 00 00 00 6F : ...CCCCCC..cccccccccc..CCCccc.....?00000003: 00 00 4C C4 C4 C4 C4 C4 C4 00 0A 84 84 84 84 84 84 84 84 84 84 00 00 00 00 00 00 00 00 6F 00 00 00 00 6F : ..<DDDDDD..dddddddddd........?....?

[I] means indicator field 0x6F means previous field is null, 0x00 means not null.

0 0