At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!
COURSE of the MONTH
12 days, 16 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Please read carefully: How can we pass parameters in SQL query while executing IKJEFT01? See example of problem statement.
Posted on 2013-01-04
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=*.SETV
//*
...where PRM2FILE is a program written to take whatever is in the parm field and write it to the OUTPUT01 DD.
//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=*.SETV
//*
...where PRM2FILE is a program written to take whatever is in the parm field and write it to the OUTPUT01 DD.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
3
8 Comments
Active today
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.
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.
or you can just have a 5 lines REXX script which writes the content of the parameters to a file and then use that file
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',NOT
// CLASS=H,MSGCLASS=X,REGION=
//* $ACFJ219 ACF2 ACTIVE ADPDNJE ACF2
//*
2 //SETVAR SET SSNODE=DBO1,DBOI=P1,DBOO=D
// PARMDIR=OGDBA,PDBNAME='FG1
3 // SET VAL1='WHERE DBNAME LIKE ',
// VAL2='FG1DD%1'
//*
4 //JOBLIB DD DISP=SHR,DSN=SYS2.PTRP.&LO
5 // DD DISP=SHR,DSN=SYS2.DB2.&LOA
6 // DD DISP=SHR,DSN=SYS2.DB2.&LOA
7 // DD DISP=SHR,DSN=SYS2.DB2.&LOA
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
// DISP=(MOD,DELETE,DELETE), 00058300
// UNIT=SYSDA,SPACE=(TRK,(1,1
// DCB=(RECFM=FB,LRECL=80,BLK
//*
//*-----------------------
//* CREATE A SETUP FOR TABLESPACE UTILITY
//*-----------------------
11 //UTL1002U EXEC PGM=ICETOOL,
// PARM='MSGDDN=MYOUT,JP1"&VA
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.
//* DISP=SHRW,CATLG,CATLG),
//* UNIT=SYSDA,SPACE=(TRK,(1,1
//* DCB=(RECFM=FB,LRECL=80,BLK
17 //OUTDD DD DSN=FG1.F&SSNODE..RUNDYNAM
// DISP=(NEW,CATLG,CATLG),
// UNIT=SYSDA,SPACE=(TRK,(1,1
// DCB=(RECFM=FB,LRECL=80,BLK
18 //CTL1CNTL DD *
INCLUDE COND=(1,44,CH,EQ,C'JP1',AN
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',AN
PARMLIST :
OPTION RESINV=0,ARESINV=0,MSGDDN=
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=E0
--------------------------
Thank you.
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',NOT
// CLASS=H,MSGCLASS=X,REGION=
//* $ACFJ219 ACF2 ACTIVE ADPDNJE ACF2
//*
2 //SETVAR SET SSNODE=DBO1,DBOI=P1,DBOO=D
// PARMDIR=OGDBA,PDBNAME='FG1
3 // SET VAL1='WHERE DBNAME LIKE ',
// VAL2='FG1DD%1'
//*
4 //JOBLIB DD DISP=SHR,DSN=SYS2.PTRP.&LO
5 // DD DISP=SHR,DSN=SYS2.DB2.&LOA
6 // DD DISP=SHR,DSN=SYS2.DB2.&LOA
7 // DD DISP=SHR,DSN=SYS2.DB2.&LOA
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
// DISP=(MOD,DELETE,DELETE), 00058300
// UNIT=SYSDA,SPACE=(TRK,(1,1
// DCB=(RECFM=FB,LRECL=80,BLK
//*
//*-----------------------
//* CREATE A SETUP FOR TABLESPACE UTILITY
//*-----------------------
11 //UTL1002U EXEC PGM=ICETOOL,
// PARM='MSGDDN=MYOUT,JP1"&VA
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.
//* DISP=SHRW,CATLG,CATLG),
//* UNIT=SYSDA,SPACE=(TRK,(1,1
//* DCB=(RECFM=FB,LRECL=80,BLK
17 //OUTDD DD DSN=FG1.F&SSNODE..RUNDYNAM
// DISP=(NEW,CATLG,CATLG),
// UNIT=SYSDA,SPACE=(TRK,(1,1
// DCB=(RECFM=FB,LRECL=80,BLK
18 //CTL1CNTL DD *
INCLUDE COND=(1,44,CH,EQ,C'JP1',AN
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',AN
PARMLIST :
OPTION RESINV=0,ARESINV=0,MSGDDN=
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=E0
--------------------------
Thank you.
Active today
I found an example in an IBM manual you may want to try:
INREC OVERLAY=(1,44,CH,EQ,C'JP1'
instead of include. Ref:
http://publib.boulder.ibm.com/infocenter/zos/v1r13/index.jsp?topic=%2Fcom.ibm.zos.r13.icea100%2Fice1ca61230.htm
ICETOOL is not an alias for sort, but is a program that is part of IBM's DFSort product. It can be used for a multitude of things, however I have never had a need to use it directly.
INREC OVERLAY=(1,44,CH,EQ,C'JP1'
instead of include. Ref:
http://publib.boulder.ibm.com/infocenter/zos/v1r13/index.jsp?topic=%2Fcom.ibm.zos.r13.icea100%2Fice1ca61230.htm
ICETOOL is not an alias for sort, but is a program that is part of IBM's DFSort product. It can be used for a multitude of things, however I have never had a need to use it directly.
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
Again thanks
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
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
Featured Post
TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have.
In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB.
How Percona can help
Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month12 days, 16 hours left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.