DSNTIAUL UNLOAD DB2 TABLE INTO DATASET

最新推荐文章于 2024-04-30 20:47:55 发布

假装80后

最新推荐文章于 2024-04-30 20:47:55 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： mainframe DB2

本文链接：https://blog.youkuaiyun.com/HiHui/article/details/52877592

DB2 同时被 2 个专栏收录

27 篇文章

订阅专栏

mainframe

14 篇文章

订阅专栏

本文介绍了如何利用DB2实用程序DSNTIAUL将数据库表的内容卸载到IBM Mainframe的数据集中。首先定义了表的结构，然后提供了DSNTIAUL的JCL（作业控制语言）示例。在提交该作业前，需要预先准备表和数据。执行JCL作业后，将创建两个数据集：一个是数据结构描述文件，另一个是包含实际数据的文件，记录长度为35。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.