Unload DB2 Tables Example


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

JCL -

----+----1----+----2----+----3----+----4----+----5----+
//MATEPKU  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=DSNA10.DBAG.SDSNEXIT,DISP=SHR 
//         DD  DSN=DSNA10.SDSNLOAD,DISP=SHR
//SYSIN    DD  *
  SELECT * FROM EMPLOYEE WITH UR;
/*
//SYSTSIN  DD  *
DSN SYSTEM(DBAG)
RUN PROGRAM(DSNTIAUL) -
    PLAN(DSNTIB10) -
    LIB('DSNA10.DBAG.RUNLIB.LOAD') -
    PARMS('SQL')
END
/*
//SYSREC00 DD  DSN=MATEPK.EMPL.UNLOAD,
//             DISP=(NEW,CATLG,DELETE),
//             UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)
//SYSPUNCH DD  DSN=MATEPK.EMPL.SYSPUNCH,
//             DISP=(NEW,CATLG,DELETE),
//             UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)
//SYSPRINT DD  SYSOUT=*
//SYSTSPRT DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*

SYSTSPRT Output -

Unload Db2 Tables

Explaining Example -

  • DSNA10.DBAG.SDSNEXIT - Specifies system DBAG SDSNEXIT library.
  • DSNA10.SDSNLOAD - Specifies system SDSNLOAD library.
  • SYSTEM(DBAG) - Specifies DB2 system name (DBAG).
  • PROGRAM(DSNTIAUL) – Utility used to unload the DB2 table.
  • PLAN(DSNTIB10) - Specifies the plan (DSNTIB*) which is authorized to unload Db2 table from DBAG system.
  • LIB('DSNA10.DBAG.RUNLIB.LOAD') - Specifies the library where the DSNTIAUL utility is already existed.
  • PARMS('SQL') - Should be always SQL for DB2 queries.
  • SYSREC00 DD ... - Specifies the PS file where the table data will be saved.
  • SYSPUNCH DD ... - Specifies the PS file where the table structure will be saved.
Note! Change the STEPLIB libraries, SYSIN query, SYSTEM, PLAN, LIB, SYSREC00 and SYSPUNCH according to your system setup, and do not change the PROGRAM name. Once the details are updated, the above JCL can directly submit to run the program.