DB2 Unload DSNTIAUL

 

Data from a DB2 table can be unloaded mainly using two DB2 utilities, the latest high performance unload utility (HPU) which is supported by DB2 version 10 and above or the good old DSNTIAUL.

Let’s take a look at DSNTIAUL parameters and a sample JCL to execute the an unload

//STP001 EXEC PGM=IKJEFT01                                

//STEPLIB DD DISP=SHR,DSN=DSNXXX.RUNLIB.LOAD            

//SYSIN   DD *                                          

   SELECT * FROM DB2T.TABLE1                          

   WITH UR;                                                

/*                                                        

//SYSTSIN DD *                                            

   DSN SYSTEM(DSN1)                                        

   RUN PROGRAM(DSNTIAUL) –                                

   PLAN(DSNTIB81) –                                        

   PARMS(‘SQL’)                                            

//SYSTSPRT DD SYSOUT=*                                    

//SYSPRINT DD SYSOUT=*                                    

//SYSUDUMP DD SYSOUT=*                                    

//SYSPUNCH DD DUMMY                                        

//SYSREC00 DD DSNAME=UNLOAD.FILE1(+1),                

//            DISP=(NEW,CATLG,DELETE),                    

//             UNIT=SYSDA,SPACE=(TRK,(10,10),RLSE)   

SYSIN – Here you can provide the query to be executed instream as given in the sample JCL or in a data set of LRECL 72, as DSNTIAUL reads only the first 72 bytes of each record

If no parameter value is specified on invocation of DSNTIAUL, a table to be unloaded is specified by entering one 72-byte line

//SYSIN   DD *    

   DB2T.EMPLOYEE

/*

If the ‘SQL’ parameter value (see below under SYSTIN – PARMS) is specified on invocation of DSNTIAUL, input is in the form of complete SQL statements

//SYSIN   DD *                                          

   SELECT * FROM DB2T.TABLE1                          

   WITH UR;                                                

/*        

SYSPRINT – Output data set. DSNTIAUL writes informational and error messages in this data set, if record length is specified for the SYSPRINT data set it must be 121 bytes

SYSPUNCH – DSNTIAUL writes the LOAD utility control statements in this data set, if you want only to  obtain the LOAD utility control statements and not unload any data you can set the SYSRECnn data sets to DUMMY

SYSRECnn – The value nn ranges from 00 to 99. You can have a maximum of 100 output data sets for a single execution of DSNTIAUL. Each data set contains the data that is unloaded when DSNTIAUL processes a SELECT statement from the input data set. Therefore, the number of output data sets must match the number of SELECT statements. The output format is a sequential file without row or column delimiters. This file consists of an unbroken sequence of fixed-length records. Record length is limited to 32760 bytes.

SYSTSIN –

DSN SYSTEM – DB2 subsystem id

RUN PROGRAM – Program to be executed for unload, here its DSNTIAUL

PLAN – DB2 Plan to execute the program DSNTIAUL

PARMS

• SQL – Indicates that your input data set contains one or more complete SQL    Statements,   each of which ends with a semicolon
• Number of rows per fetch – Specify a number from 1 to 32767 to specify the number of rows per fetch that DSNTIAUL retrieves. If you do not specify this number, DSNTIAUL retrieves 100 rows per fetch. This parameter can be specified with the SQL parameter.
• TOLWARN Specify NO (the default) or YES to indicate whether DSNTIAUL continues to retrieve rows after receiving an SQL warning

 

Limitations of DSNTIAUL

• A maximum of 100 tables can be unloaded
• Data records are limited to 32760 bytes
• Input SQL statement size of 2 MB