Unload DB2 Tables (for Experienced)


Summary

For basic information, go through the IKJEFT01 Utility (for Beginners)

  • 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 -

Unload Db2 Tables

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.
Note! Change the STEPLIB libraries, SYSTEM, PLAN, and LIB according to the system setup, and do not change the PROGRAM name. Once the details are updated, the above JCL can directly submit to run the program.