Avatar of Enyinnaya
Enyinnaya
Flag for United States of America asked on

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

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.
DatabasesDB2Mainframe OSCOBOL

Avatar of undefined
Last Comment
Enyinnaya

8/22/2022 - Mon
giltjr

That I am aware of there are no IBM utilities or free programs that do this.
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.
SOLUTION
momi_sabag

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Enyinnaya

ASKER
giltjr, momi_sabag,

Thank you both for your suggestions. First let me say it is looking more and more that I may just have to write a cobol program to do what I need done. Unfortunately, we don't have any REXX expertise in house and  I was only hoping to discover any of the IBM common utilities on the mainframe that can do what I need done withouy having to write a program for this purpose.

Doeas anyone have ICETOOL (alias for SORT)? In the past few days I have read documentation about ICETOOL that shows it can use "JCL SET name=some_data" and the "JPn" function to assign PARM= with the ICETOOL program is invoked directly. What I have not been able to find is any example of how to code for it.
The example found shows only how to use the "JPn" function to "OMIT COND= something " during a ICETOOL COPY routine.
If it is possible to use ICETOOL to write PARM= data to a file, can anyone show how this can be done?

Take a look at the ICETOOL JCL that I have tried to use. It appears to run successfully but did not write anything to the OUTDD file.
I understand why ICETOOL did not write anything out...INDD is empty, so nothing to copy.
Is there anyway what I have can work? If so, what am I doing wrong?

1 //FG1TESTC JOB 05274,'FG1TESTC,BN-53',NOTIFY=&SYSUID,                     JOB00659
          //      CLASS=H,MSGCLASS=X,REGION=0M
          //* $ACFJ219 ACF2 ACTIVE ADPDNJE                                            ACF2
          //*
        2 //SETVAR SET SSNODE=DBO1,DBOI=P1,DBOO=D2,TRK=OG1,LOADIR=TEST,
          //           PARMDIR=OGDBA,PDBNAME='FG1DD%1',PNAME='OG1SC%'
        3 //       SET VAL1='WHERE DBNAME LIKE ',
          //           VAL2='FG1DD%1'
          //*
        4 //JOBLIB   DD  DISP=SHR,DSN=SYS2.PTRP.&LOADIR..LOADLIB
        5 //         DD  DISP=SHR,DSN=SYS2.DB2.&LOADIR..SDSNEXIT
        6 //         DD  DISP=SHR,DSN=SYS2.DB2.&LOADIR..SDSNLOAD
        7 //         DD  DISP=SHR,DSN=SYS2.DB2.&LOADIR..RUNLIB.LOAD
        8 //MYLIBS1  JCLLIB  ORDER=FG1.FG1DM3.PROCLIB
          //*
          //****************************************************
          //*  DELETE PREVIOUS WORK FILES FOR DATABASE & TRACK *
          //****************************************************
          //*
        9 //UTL100U EXEC PGM=IEFBR14                                              00058100
       10 //DD01    DD DSN=FG1.F&SSNODE..RUNDYNAM,                                00058200
          //           DISP=(MOD,DELETE,DELETE),                                  00058300
          //           UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE),
          //           DCB=(RECFM=FB,LRECL=80,BLKSIZE=0)
         //*
          //*------------------------------------------------------
          //* CREATE A SETUP FOR TABLESPACE UTILITY
          //*------------------------------------------------------
       11 //UTL1002U EXEC PGM=ICETOOL,
          //     PARM='MSGDDN=MYOUT,JP1"&VAL1",JP2"&VAL2",LIST'
        12 //MYOUT    DD SYSOUT=*
       13 //SYSPRINT DD SYSOUT=*
       14 //TOOLMSG  DD SYSOUT=*
       15 //DFSMSG   DD SYSOUT=*
       16 //INDD   DD DISP=SHR,DSN=FG1.F&SSNODE..DUMMY.DATA  <<<---- Empty File to try to fool ICETOOL to think I have data to copy
          //*          DISP=SHRW,CATLG,CATLG),
          //*          UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE),
          //*          DCB=(RECFM=FB,LRECL=80,BLKSIZE=0)
       17 //OUTDD   DD DSN=FG1.F&SSNODE..RUNDYNAM,           <<<---- Output File I want ICETOOL to write to...also empty when JCL ran Ok
          //           DISP=(NEW,CATLG,CATLG),
          //           UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE),
          //           DCB=(RECFM=FB,LRECL=80,BLKSIZE=0)
       18 //CTL1CNTL DD *
            INCLUDE COND=(1,44,CH,EQ,C'JP1',AND,1,44,CH,EQ,C'JP2')

       19 //TOOLIN   DD *
             COPY FROM(INDD) TO(OUTDD)  USING(CTL1)

---------------------------------------------------------------------

SYT000I  SYNCTOOL RELEASE 1.6.2 - COPYRIGHT 2007  SYNCSORT INC.
SYT001I  INITIAL PROCESSING MODE IS "STOP"
SYT002I  "TOOLIN" INTERFACE BEING USED

           COPY FROM(INDD) TO(OUTDD)  USING(CTL1)
SYT020I  SYNCSORT CALLED WITH IDENTIFIER "0001"
SYT030I  OPERATION COMPLETED WITH RETURN CODE 0

SYT004I  SYNCTOOL PROCESSING COMPLETED WITH RETURN CODE 0


 SYNCSORT FOR Z/OS  1.3.2.1RI   U.S. PATENTS: 4210961, 5117495   (C) 2007 SYNCSORT INC.   DATE=2013/005   TIME=20.31.25
                                          STATE OF NEW MEXICO   z/OS   1.9.0
 SYNCSORT LICENSED FOR CPU SERIAL NUMBER 01DD7, MODEL 2818 Q03             LICENSE/PRODUCT EXPIRATION DATE: 02 JUL 2015
 CTL1CNTL :
   INCLUDE COND=(1,44,CH,EQ,C'JP1',AND,1,44,CH,EQ,C'JP2')
 PARMLIST :
 OPTION RESINV=0,ARESINV=0,MSGDDN=DFSMSG,SORTIN=INDD,SORTOUT=OUTDD,SORTDD=CTL1,CO
 PY
 WER428I  CALLER-PROVIDED IDENTIFIER IS "0001"
 WER108I  INDD     : RECFM=FB   ; LRECL=    80; BLKSIZE= 27920
 WER110I  OUTDD    : RECFM=FB   ; LRECL=    80; BLKSIZE= 27920
 WER055I  INSERT          0, DELETE          0
 WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
 WER054I  RCD IN          0, OUT          0
 WER169I  RELEASE 1.3 BATCH 0506 TPF LEVEL 2.1
 WER052I  END SYNCSORT - FG1TESTC,UTL1002U,,DIAG=E000,7306,C806,0044,A8FA,4D8A,0A08,C4E2
-----------------------------------------------------------------------------

Thank you.
ASKER CERTIFIED SOLUTION
giltjr

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
giltjr

Oh, REXX is not all that difficult to learn and use.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Enyinnaya

ASKER
Thanks giltjr for the latest suggestions to try using ICETOOL. I am going to try it and report back if it worked or not. I checked out the link you provided and it looks promising.

Again thanks
Enyinnaya

ASKER
I've requested that this question be closed as follows:

Accepted answer: 350 points for giltjr's comment #a38748466
Assisted answer: 150 points for momi_sabag's comment #a38747550
Assisted answer: 0 points for Enyinnaya's comment #a38749910

for the following reason:

Thanks guys
Enyinnaya

ASKER
Thanks again!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.