RC/Query : Generate JCL to unload data in DSNTIAUL format

Products

• RC/Query for DB2 for z/OS

Issue/Introduction

RC/Query enables users to generate JCL to unload data in DSNTIAUL format which can then be transferred to other environments. 

Resolution

The JCL generated depends on the Utilities setting in the RC/Query Profile. If CA-UTILITIES is Y, the Fast Unload cards are generated. If CA-UTILITIES is N then cards to run the DSNTIAUL utility are generated.

RCQ Profile has:

CA-UTILITIES        ==> Y    (Y/N)   - To use CA-Utilities.

The DSNTIAUL line command on a table initiates the process.


RQTL   20.0   --------------- RC/Q Table List --------------- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> T                       Option  ===> L    Where => N
 Table Name ===> AGENT1                > Creator ===> AUTHID1                >
  Qualifier ===> *                     > N/A     ===> *                      >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 -          LINE 1 OF 1    >
CMD      TABLE NAME         CREATOR  DATABASE TBLSPACE COL_CNT  NUMBER OF ROWS
DSNTIAUL AGENT1             AUTHID1  AGENT1DB AGENT1TS       6             N/A
******************************* BOTTOM OF DATA ********************************

Where CA-UTILITIES is Y a Fastunload screen is displayed with the with the OUTPUT FORMAT  : D  for DSNTIAUL

UOPFUTBL               -   Fast Unload - Table Unload -        yyyy/mm/dd hh:mm
COMMAND  ==>

 Database  : AGENT1DB
 Table     : AGENT1              Partition(s): 0000

EXECUTION OPTIONS
 EXCP         : Y ONE-SYSREC : N  Display Status : 0010000    SQL-ACCESS : O
 Input Format : T SHRLEVEL   : C  Edit SELECT    : N   PART-INDEPENDENCE : N
 Input Dsname :

GLOBAL OPTIONS
 LIMIT    :           OUTPUT FORMAT  : D         NULL-FIELD       :
 COMMA    :           QUOTE          :           LOAD-CONTROL     : D
 DISCARDS : 0100      SAMPLE (x,y)   : (          ,          )

SORT OPTIONS                          PERFORMANCE OPTIONS
 Messages: ALL                          VSAM Buffers: 0096
 Sort Size: 4M        Sortnum: 0008     Data Buffers: 0050
 Sort Devt: DASD      Estimated-rows: 007500000

 EXITS      Open         :           Formatted    :
            Unformatted  :           Close        :

The generated cards look like this:

//SYSREC01    DD   DSN=HLQ.SYSREC01,
// DISP=(,CATLG),SPACE=(CYL,(1174,5)),UNIT=SYSDA
//SYSCTL01    DD   DSN=HLQ.SYSCTL01,
// DISP=(,CATLG),SPACE=(CYL,(1,1)),UNIT=SYSDA
//ST01MSG   DD   SYSOUT=*
//SYSIN   DD  *
FASTUNLOAD
DISCARDS 100
DISPLAY-STATUS 10000
EXCP YES
INPUT-FORMAT TABLE
IO-BUFFERS 50
LOAD-CONTROL DB2LOAD
OUTPUT-FORMAT DSNTIAUL     <----------------- DSNTIAUL format output
PART-INDEPENDENCE NO
SHRLEVEL CHANGE
SORTFLAG ALL
SORTSIZE 4M
SORTNUM 8
ESTIMATED-ROWS 7500000
SQL-ACCESS  ONLY
SELECT * FROM AUTHID1.AGENT1;

When the RCQ Profile has "N" in CA Utilities......

CA-UTILITIES        ==> N    (Y/N)   - To use CA-Utilities.

RQTL   20.0   --------------- RC/Q Table List --------------- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> T                       Option  ===> L    Where => N
 Table Name ===> AGENT1                > Creator ===> AUTHID1                >
  Qualifier ===> *                     > N/A     ===> *                      >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 -          LINE 1 OF 1    >
CMD      TABLE NAME         CREATOR  DATABASE TBLSPACE COL_CNT  NUMBER OF ROWS
DSNTIAUL AGENT1             AUTHID1  AGENT1DB AGENT1TS       6             N/A
******************************* BOTTOM OF DATA ********************************

The DSNTIAUL line command displays a DSNTIAUL screen:

RQLUTL 20.0   -------------- RC/Q Line Utilities ------------- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

UNLOAD DATA
    FROM TABLE    AUTHID1.AGENT1______________________________________________
                  ____________________________________________________________
                  ____________________________________________________________
                  ____________________________________________________________
                  ____________________________________________________________
     SELECT(*)    S YES         _ NO
    COLUMN-NAME   AGT_ID________________________
    COLUMN-NAME   AGT_NM________________________
    COLUMN-NAME   AGT_CORE_CREATE_TS____________
    COLUMN-NAME   AGT_CORE_UPDATE_TS____________
    COLUMN-NAME   AGT_CREATE_TS_________________
    COLUMN-NAME   AGT_UPDATE_TS_________________

     USE THESE OPTIONS FOR ALL SELECTED OBJECTS:  S NO     _ YES

******************************* BOTTOM OF DATA ********************************

The generated JCL looks like this:

//AUTHID1B JOB (106100000),'DSNTIAUL',CLASS=B,MSGCLASS=X,
//             NOTIFY=AUTHID1,REGION=0M,TIME=1440
/*JOBPARM SYSAFF=ssid
//*
//* UD       PTIPROD.RD200.PRD.CDBAMDL(MJUTLUNL) AUTHID1
//*****************************************************
//UTIL0001  EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB  DD DISP=SHR,DSN=ssid.PRIVATE.SDSNEXIT
//         DD DISP=SHR,DSN=hlq.SDSNLOAD
//*
//SYSOUT    DD   SYSOUT=*
//SYSPRINT  DD   SYSOUT=*
//SYSPUNCH  DD   DSN=AUTHID1.TABLE.UNLOAD.SYSPUNCH.I001,
//       UNIT=SYSDA,DISP=(,KEEP),
//       SPACE=(CYL,(1,1))
//SYSREC00  DD   DSN=AUTHID1.TABLE.UNLOAD.DATA.I001,
//       UNIT=SYSDA,DISP=(,KEEP),
//       SPACE=(CYL,(4,20))
//SYSTSPRT DD   SYSOUT=*
//SYSTSIN  DD   *
DSN SYSTEM(ssid)
RUN PROGRAM(DSNTIAUL)            -
    PLAN(DSNTIAUL)               -
    PARMS('SQL')
/*
//SYSIN    DD   *
SELECT *
    FROM AUTHID1.AGENT1;
/*
/*