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.
Now you could create a REXX that called DB2 you could pass parameters directly to them from the PARM statement using IKJEFT01. The limitation is the "PARM" statement is limited to a total of 101 characters IIRC.
Something like:
//CTMSEND EXEC PGM=IKJEFT01,
// PARM='DB2REXX ABCD"
Where DB2REXX is a REXX EXEC that calls DB2 and is the name of a REXX EXEC or TSO CLIST that you want IKJEFT01 to execute when is starts and &SQL is a SQL statement. Then if using REXX you would have something like:
/* DB2REXX: REXX exec to call db2 *?
arg wclause /* Get Where clause */
Then you would have the rest of the EXEC. You can search google for examples of how to call DB2 from REXX. Depending on how complex your where clause is you may have to play around with embedding it in quotes.