IKJEFT01 - Unload Db2 Tables


IKJEFT01 used to unload the db2 table from the database and creates flat file with the data from the table.

IKJEFT01 utility is a TSO utility to unload the DB2 table.

IKJEFT01 utility uses the DSNTIAUL program to unload data from DB2 tables into sequential data sets.

The data is copied to the data sets and is not deleted from the table.

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)                        
//*

Example: -


Below example to unload the db2 table using IKJEFT01 utility.


Job: -

----+----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 ****************************

SYSPRINT Output: -


Explaining Solution: -

  • SYSPRINT DD - specifies a sysout class where all product usage reports produced by IBM are written.
  • SYSTSPRT DD - Used to control the output from the background job.
  • SYSUDUMP - specifies the output class where the dump is placed in the event of an ABEND. A data set name may be used instead of a sysout class.
  • SYSTSIN DD *- Used to specify the data to follow which consists of executable commands and/or subcommands.
  • SYSIN DD * - Specifies the input dataset. In this utility, it contains the Db2 query for data selection criteria from table.
  • SYSRECnn DD * - Specifies the output dataset which used to store the unloaded data from the table. The value nn ranges from 00 to 99. It can have maximum 100 datasets for the single execution of DSNTIAUL.
  • SYSPUNCH DD * - Specifies the output dataset which used to store the table structure. Used to write the control statements of the LOAD utility.

Note:

Change the STEPLIB libraries, SYSTEM, PLAN and LIB according to the system setup and do not change the PROGRAM name.

Once the details updated, the above JCL can directly submit to run the program.