Unload DB2 Tables ^{(for Experienced)}

• IKJEFT01 is used to unload the DB2 table from the database and creates a flat file with the data.
• IKJEFT01 utility uses the DSNTIAUL program to unload data from DB2 tables into sequential data sets.
• The table data are not affected by the unload operation.
• DSNTIAUL uses SQL to access DB2.

Syntax -

//UNLOAD   EXEC PGM=IKJEFT01,DYNAMNBR=50                                
//STEPLIB  DD  DSN=XXXXXX.DB2T.SDSNEXIT,DISP=SHR                        
//         DD  DSN=XXXXXX.DB2T.SDSNLOAD,DISP=SHR 
//SYSIN    DD  *                                                        
SELECT * FROM TABLE_NAME WITH UR;   >> Table query to unload the data
/*                                                                      
//SYSPRINT DD  SYSOUT=*                                                 
//SYSTSPRT DD  SYSOUT=*                                                 
//SYSUDUMP DD  SYSOUT=*                                                 
//SYSTSIN  DD  *                                                        
DSN SYSTEM(DB2T)                    >> DB2 System    
RUN PROGRAM(DSNTIAUL) -              >> unloading program  
    PLAN(DSNTIAUL) -                  >> Program bounded plan 
    LIB('XXXXXX.DB2T.RUNLIB.LOAD') -  >> Program existed library  
    PARMS('SQL')                      >> Program Parm    
END                                                                     
/*                                                                      
//SYSREC00 DD  DSN=MTHUSR.TABLE.UNLOAD,    >> Unloaded table data   
//             DISP=(NEW,CATLG,DELETE),                                 
//             UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)                        
//***TABLE STRUCTURE                                                    
//SYSPUNCH DD  DSN=MTHUSR.TABLE.SYSPUNCH,  >> Table structure  
//             DISP=(NEW,CATLG,DELETE),                                 
//             UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)                        
//*

Practical Example -

Scenario - JCL to unload the db2 table using IKJEFT01 utility.

JCL -

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
//MTHUSRU  JOB (123),'MTHUSR',CLASS=A,MSGCLASS=A,MSGLEVEL=(1,1),        
//             NOTIFY=&SYSUID                                           
//**********************************************************************
//*  DB2 TABLE UNLOAD USING IKJEFT01                                    
//**********************************************************************
//UNLOAD   EXEC PGM=IKJEFT01,DYNAMNBR=50                                
//STEPLIB  DD  DSN=DSNDB1.DB2T.SDSNEXIT,DISP=SHR                        
//         DD  DSN=DSNDB1.DB2T.SDSNLOAD,DISP=SHR                        
//SYSIN    DD  *                                                        
SELECT * FROM EMPLOYEE WITH UR;                                       
/*                                                                      
//SYSTSIN  DD  *                                                        
DSN SYSTEM(DB2T)                                                        
RUN PROGRAM(DSNTIAUL) -                                                 
    PLAN(DSNTIAUL) -                                                    
    LIB('DSNDB1.DB2T.RUNLIB.LOAD') -                                    
    PARMS('SQL')                                                        
END                                                                     
/*                                                                      
//SYSREC00 DD  DSN=MTHUSR.TABLE.UNLOAD,                                 
//             DISP=(NEW,CATLG,DELETE),                                 
//             UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)                        
//***TABLE STRUCTURE                                                    
//SYSPUNCH DD  DSN=MTHUSR.TABLE.SYSPUNCH,                               
//             DISP=(NEW,CATLG,DELETE),                                 
//             UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)                        
//SYSPRINT DD  SYSOUT=*                                                 
//SYSTSPRT DD  SYSOUT=*                                                 
//SYSUDUMP DD  SYSOUT=*                                                 
//*                                                                     
**************************** Bottom of Data ****************************

SYSTSPRT Output -

Explaining Example -

• SYSPRINT DD - Specifies an SYSOUT DSN where all product related messages are written.
• SYSTSPRT DD - Used to control the output from the background job.
• SYSUDUMP - Specifies the output dataset where the dump is placed in case of an ABEND.
• SYSTSIN – Used to specify the executable commands and/or subcommands.
• SYSIN DD * - Specifies the input dataset and contains the Db2 query for data selection criteria from the table.
• SYSRECnn DD * - Specifies the output dataset used to store the unloaded data from the table. The value nn ranges from 00 to 99. It can have a maximum of 100 datasets for the single execution of DSNTIAUL.
• SYSPUNCH DD * - Specifies the output dataset used to store the table structure and to write the LOAD utility's control statements.