troubleshooting Question

Please read carefully: How can we pass parameters in SQL query while executing IKJEFT01? See example of problem statement.

Avatar of Enyinnaya
EnyinnayaFlag for United States of America asked on
DatabasesDB2COBOLMainframe OS
8 Comments2 Solutions4716 ViewsLast Modified:
Problem Statement:
//UNLOAD    EXEC PGM=IKJEFT01
//SYSTSPRT  DD SYSOUT=*
//SYSTSIN   DD *  DSN SYSTEM(DB2X) RETRY(3)
RUN PROGRAM(DSNTIAUL)
PLAN(DSNTIAUL) -
PARM('SQL')
END
//SYSPRINT  DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=D
//SYSREC00  DD DSN=OUTPUT.FILE.NAME,
//          DISP=(NEW,CATLG,DELETE),
//SYSPUNCH  DD SYSOUT=*                                          
//SYSIN     DD *
SELECT * FROM SYSIBM.SYSTABLESPACE WHERE NAME LIKE 'ABCD%'  
/*
End Problem Statement.

The answer that closely solves the problem that I have will look like this:

Potential Answer:

 I saw this in a post from someone with a similar problem...I need a separate step, prior to my catalg SELECT step, that writes the variable line(s) of SQL to a temporary file. Then concatenate that temporary file with the unchanging lines of SQL like shown below. I don't want to write any program for this. I want to know if there is any IBM utility that can serve the same purpose...take whatever is in the parm field and write it to the OUTPUT01 DD.

Does anyone know of s specific utility that I can use to achieve this objective?  And if so, the person please provide an example(s)? I have been thinking about IBM's ICETOOL or the SORT tool, but I do not know how to use the ICETOOL to accomplish this especially how to write the PARM variable to a file.

Any help will be much appreciated.


Freehand...
//SETVAR   EXEC PGM=PRM2FILE,PARM='WHERE XYZ=''&VAL'''
//SYSPRINT DD  SYSOUT=*
//OUTPUT01 DD  DISP=(NEW,PASS,DELETE),
//             LRECL=80,
//             AVGREC=U,
//             RECFM=FB,
//             SPACE=(80,(1,1),RLSE)
//*
//UNLOAD    EXEC PGM=IKJEFT01
//SYSTSPRT  DD SYSOUT=*
//SYSTSIN   DD *  DSN SYSTEM(DB2X) RETRY(120)
RUN PROGRAM(DSNTIAUL)
PLAN(DSNTIAUL) -
PARM('SQL')
END
//SYSPRINT  DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=D
//SYSREC00  DD DSN=FCSTN.AK.XXXXXX,
//          DISP=(NEW,CATLG,DELETE),
//SYSPUNCH  DD SYSOUT=*                                          
//SYSIN     DD *
SELECT * FROM ABC.DEF
//          DD  DISP=(OLD,PASS),DSN=*.SETVAR.OUTPUT01
//*

 ...where PRM2FILE is a program written to take whatever is in the parm field and write it to the OUTPUT01 DD.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros