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.

2 thoughts on “Running DSNTEP2 / DSNTEP4 using IKJEFT01 for batch SQL(DB2) processing”

  1. i want to extract all the 122 columns from the table which might take close to 2000 LRECL for the file to have…

    its not working me for that…can anybody help me?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.