Category: IKJEFT01

Using IKJEFT01 for Binding a normal batch DB2 program

//BINDSTEP EXEC PGM=IKJEFT01
//DBRMLIB  DD  DSN=my.dbrm.library,DISP=SHR
//STEPLIB  DD DISP=SHR,
//            DSN='SSID.SDSNLOAD'
//SYSTERM  DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
DSN SYSTEM(SSID) RETRY(2) TEST(0)
  BIND PACKAGE   (PKGNAME)  -
       MEMBER    (PGMNAME) -
       OWNER     (USER1)  -
       QUALIFIER (USER1)  -
       ACTION    (REPLACE)  -
       VALIDATE  (BIND)     -
       ISOLATION (CS)       -
       EXPLAIN   (YES)      -
       RELEASE   (COMMIT)
  END
/*
Advertisement

Using IKJEFT01 for running a normal batch DB2 program

//STEP001  EXEC PGM=IKJEFT01
//*
//STEPLIB  DD DISP=SHR,DSN=SSID.SDSNLOAD
//*
//DISPLAY  DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSABOUT DD SYSOUT=*
//SYSDBOUT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN  DD *
    DSN SYSTEM(SSID)
    RUN PROGRAM(PGMNAME) PLAN(PLANNAME) PARM('ANY PARM TO THE PGM') -
    LIB('MYPGM.LOADLIB')
    END
/*

Running DSNTIAUL using IKJEFT01 for batch unload

DSNTIAUL (an assembler program used to process select statements dynamically) is used to unload the tables, or to get the results of an SQL select in the unload format.

No details about the query will be displayed, just the resultset will be in the SYSPRINT.

Unloading the whole table. 

//STEP0001 EXEC PGM=IKJEFT01
//STEPLIB  DD DISP=SHR,
//            DSN='SSID.SDSNLOAD'
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(250,100),RLSE),
//            UNIT=DISK,
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=0),
//            DSN=TEST.MYDSN
//SYSUDUMP DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//SYSTSOUT DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(SSID)
  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL)
  LIB ('SSID.SDSNLOAD')
/*
//SYSIN    DD *
SYSIBM.SYSDUMMY1
/*

Unloading a specific SQL select.

//STEP0001 EXEC PGM=IKJEFT01
//STEPLIB  DD DISP=SHR,
//            DSN='SSID.SDSNLOAD'
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(250,100),RLSE),
//            UNIT=DISK,
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=0),
//            DSN=TEST.MYDSN
//SYSUDUMP DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//SYSTSOUT DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(SSID)
  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL,1') –
  LIB ('SSID.SDSNLOAD')
/*
//SYSIN    DD *
SELECT * FROM SYSIBM.SYSDUMMY1
/*

The Parm ‘SQL,1’ says that I am giving an SQL statement and fetch only one record from the resultset into SYSPRINT.

Running DSNTEP2 / DSNTEP4 using IKJEFT01 for batch SQL(DB2) processing

The output of these utilities will be exactly like executing a Spufi. It will have the complete details of the query and formatted columns. If you just want the resultset (in the unload format) Please read about the DSNTIAUL utility.

DSNTEP2 / DSNTEP4: These programs are used to execute the SQL queries in batch.

The output (SYSPRINT) will have complete details of the query execution.
DSNTEP2 is a PL/I program used to process SELECT and NON SELECT statements dynamically.

//STEP0001 EXEC PGM=IKJEFT01 ,DYNAMNBR=20
//STEPLIB  DD DISP=SHR,
//            DSN='SSID.SDSNLOAD'
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(250,100),RLSE),
//            UNIT=DISK,
//            DCB=(RECFM=FB,LRECL=133,BLKSIZE=0),
//            DSN=TEST.MYDSN
//SYSUDUMP DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//SYSTSOUT DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(SSID)
  RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) PARM('ALIGN(LHS)')
  LIB ('SSID.SDSNLOAD')
/*
//SYSIN    DD *
 SELECT * FROM SYSIBM.SYSDUMMY1
/*

The Parm for this program is optional.

Valid values are,

ALIGN(MID) – center alignment

ALIGN(LHS) – left alignment

MAXSEL(n) – to limit the number of rows returned from a
SELECT statement to n (n = 0 -> 32768)
NOMIXED – ??

MIXED  – ??

SQLTERM(termchar) – termchar is the character that you use to end each SQL
| statement.