Summary -
In this topic, we described about the Unload Db2 Tables with detailed example.
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.
Once the details updated, the above JCL can directly submit to run the program.