DSNTIAUL sample program

Examples

Example: using DSNTIAUL to unload a subset of rows in a table

Suppose that you want to unload the rows for department D01 from the project table. Because you can fit the table specification on one line, and you do not want to execute any non-SELECT statements, you do not need the SQL parameter. Your invocation looks like the one that is shown in the following figure:

//UNLOAD  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD  SYSOUT=*
//SYSTSIN  DD  *
 DSN SYSTEM(DSN)
 RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIBD1) -
       LIB('DSN1310.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
//            UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
//            VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
//            UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
//            VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN    DD *
DSN8D10.PROJ WHERE DEPTNO='D01'

Example: using DSNTIAUL to unload rows in more than one table

Suppose that you also want to use DSNTIAUL to perform the following actions:

• Unload all rows from the project table
• Unload only rows from the employee table for employees in departments with department numbers that begin with D, and order the unloaded rows by employee number
• Lock both tables in share mode before you unload them
• Retrieve 250 rows per fetch

For these activities, you must specify the SQL parameter and the number-of-rows-per-fetch parameter when you run DSNTIAUL. Your DSNTIAUL invocation is shown in the following figure:

//UNLOAD  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD  SYSOUT=*
//SYSTSIN  DD  *
 DSN SYSTEM(DSN)
 RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIBD1) PARMS('SQL,250') -
       LIB('DSN1310.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
//            UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
//            VOL=SER=SCR03
//SYSREC01 DD DSN=DSN8UNLD.SYSREC01,
//            UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
//            VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
//            UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
//            VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN    DD *
LOCK TABLE DSN8D10.EMP IN SHARE MODE;
LOCK TABLE DSN8D10.PROJ IN SHARE MODE;
SELECT * FROM DSN8D10.PROJ;
SELECT * FROM DSN8D10.EMP
  WHERE WORKDEPT LIKE 'D%'
  ORDER BY EMPNO;

Example: using DSNTIAUL to obtain LOAD utility control statements

If you want to obtain the LOAD utility control statements for loading rows into a table, but you do not want to unload the rows, you can set the data set names for the SYSRECnn data sets to DUMMY. For example, to obtain the utility control statements for loading rows into the department table, you invoke DSNTIAUL as shown in the following figure:

//UNLOAD  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD  SYSOUT=*
//SYSTSIN  DD  *
 DSN SYSTEM(DSN)
 RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIBD1) -
       LIB('DSN1310.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DUMMY
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
//            UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
//            VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN    DD *
DSN8D10.DEPT

Example: using DSNTIAUL to unload LOB data

This example uses the sample LOB table with the following structure:

CREATE TABLE DSN8D10.EMP_PHOTO_RESUME
( EMPNO CHAR(06) NOT NULL,
EMP_ROWID ROWID NOT NULL GENERATED ALWAYS,
PSEG_PHOTO BLOB(500K),
BMP_PHOTO BLOB(100K),
RESUME CLOB(5K),
PRIMARY KEY (EMPNO))
IN DSN8D13L.DSN8S13B
CCSID EBCDIC;

The following call to DSNTIAUL unloads the sample LOB table. The parameters for DSNTIAUL indicate the following options:

• The SQL parameter specifies that the input data set (SYSIN) contains SQL.
• The number-of-rows-per-fetch parameter value of 1 specifies that DSNTIAUL is to retrieve one row for each FETCH operation. A value of 1 is necessary if the LOB columns that you unload might contain NULL values.
• The LOBFILE parameter value of LOBFILE(DSN8UNLD) specifies that DSNTIAUL places the LOB data in data sets with a high-level qualifier of DSN8UNLD.

//UNLOAD   EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT  DD SYSOUT=*
//SYSTSIN   DD *
  DSN SYSTEM(DSN)
  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB91) -
  PARMS('SQL,1,LOBFILE(DSN8UNLD)') -
  LIB('DSN1310.RUNLIB.LOAD')
//SYSPRINT  DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=*
//SYSREC00  DD DSN=DSN8UNLD.SYSREC00,
//             UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
//             VOL=SER=SCR03,RECFM=FB
//SYSPUNCH  DD DSN=DSN8UNLD.SYSPUNCH,
//             UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
//             VOL=SER=SCR03,RECFM=FB
//SYSIN     DD *
  SELECT * FROM DSN8D10.EMP_PHOTO_RESUME;

Given that the sample LOB table has 4 rows of data, DSNTIAUL produces the following output:

• Data for columns EMPNO and EMP_ROWID are placed in the data set that is allocated according to the SYSREC00 DD statement. The data set name is DSN8UNLD.SYSREC00
• A generated LOAD statement is placed in the data set that is allocated according to the SYSPUNCH DD statement. The data set name is DSN8UNLD.SYSPUNCH
• The following data sets are dynamically created to store LOB data:
  • DSN8UNLD.Q0000000.C0000002.R0000000
  • DSN8UNLD.Q0000000.C0000002.R0000001
  • DSN8UNLD.Q0000000.C0000002.R0000002
  • DSN8UNLD.Q0000000.C0000002.R0000003
  • DSN8UNLD.Q0000000.C0000003.R0000000
  • DSN8UNLD.Q0000000.C0000003.R0000001
  • DSN8UNLD.Q0000000.C0000003.R0000002
  • DSN8UNLD.Q0000000.C0000003.R0000003
  • DSN8UNLD.Q0000000.C0000004.R0000000
  • DSN8UNLD.Q0000000.C0000004.R0000001
  • DSN8UNLD.Q0000000.C0000004.R0000002
  • DSN8UNLD.Q0000000.C0000004.R0000003
  For example, DSN8UNLD.Q0000000.C0000004.R0000001 means that the data set contains data that is unloaded from the second row (R0000001) and the fifth column (C0000004) of the result set for the first query (Q0000000).