JPn, EXEC PARM for ICETOOL but failing on Syntax...Pls help

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

Post Reply
okonita1
Member
Posts: 12
Joined: Thu Jan 03, 2013 12:18 pm

JPn, EXEC PARM for ICETOOL but failing on Syntax...Pls help

Post by okonita1 » Thu Jan 03, 2013 12:58 pm

My goal is twofold - build a dynamic DB2 catalog Select statement within a JCL procstep using ICETOOL such as this:
SELECT '-START DB('||DBNAME||') SPACENAM('||NAME||') ACCESS(UT)'
FROM SYSIBM.SYSTABLESPACE WHERE DBNAME LIKE '&PDBNAME'
AND NAME LIKE '&TSNAME';

I have the most difficulty writing the correct syntax to enclose the entire SQL including punctuation, apostrophy, open/close perenthesis and special characters in a SET variable to pass as parameter to ICETOOL which in turn writes this string to two files. One file will be for ACCESS(UT) and the second will be for ACCESS(RW). Each file will be fed to DSNTIAUL to select tablespaces based on the database_name & tablespace_name variables and a subsequent step will call PGM=IKJEFT01 to execute the start utility statement or when done start in RW mode.

My problem is I can seem to get the variables properly enclosed in the set variable section of my JCL and I don't know of a better way to get valid values into my select SQL construct...

I'll be most grateful for any assistance anyone can provide especially if I could see some examples of how to wrap the this type of character strings for a finicky product such as ICETOOL. Perhaps it might be helpful to know this is my very first attempt at using ICETOOL...

HAPPY NEW YEAR everyone

Thanks
okonita1

NicC
Active Member
Posts: 650
Joined: Sun Jul 24, 2011 5:27 pm
Location: Down on the pig farm

Post by NicC » Thu Jan 03, 2013 2:37 pm

So what sort of errors are you getting? Give us an example of how you coded the sET statement.
Regards
Nic

William Collins
Active Member
Posts: 732
Joined: Thu May 24, 2012 4:07 am

Post by William Collins » Thu Jan 03, 2013 5:43 pm

If this is your first attempt at ICETOOL, don't you think you're being a little previous in describing it as "finicky"?

Which particular ICETOOL function were you considering using?

Why don't you just use SORT, put your basic cards on SORTIN, and then the JPn for what you want to be able to change?

okonita1
Member
Posts: 12
Joined: Thu Jan 03, 2013 12:18 pm

Post by okonita1 » Sat Jan 05, 2013 12:31 am

Thank you NicC and thank you Mr. Collins. I think I did not state my problem accurately. If you indulge me for a minute, I'll like to re-state my problem and what I am looking to accomplish. The question I really wanted/should have asked is that Is there a IBM utility that can take whatever is in the parm field and write it to output file for subsequest use by another step?

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.

What I started to do was to try ICETOOL like the one below but I did not know how to write the PARM string to a file:
/SETVAR SET VAL1='WHERE DBNAME LIKE ',
// VAL2='OG1DD%1'
//*
//PSTEP001 EXEC PGM=ICETOOL,PARM=('JP1"&VAL1"','JP2"&VAL2"')
//SYSPRINT DD SYSOUT=*
//SYMNOUT DD SYSOUT=*
//OUTPUT01 DD DISP=(NEW,CATLG,DELETE),
// DSN=OG1.OGADM3.TESTQB1,
// UNIT=SYSDA,SPACE=(1,(2,2),,,ROUND),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=2400)
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//*TOOLIN DD *
//OUTFCNTL DD *

The result of the JCL above was that nothing was ever writtent to the OUTDD file...because I did not know how to do that.

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.

//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.

Is there a IBM utility that can take whatever is in the parm field and write it to the OUTPUT01 DD?
okonita1

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Sat Jan 05, 2013 1:22 am

I suspect you could write a bit of code that would do this in just a few minutes.

If you used COBOL:

Code: Select all

SELECT/ASSIGN the output file
An FD for the file
the-parm
  parm-length
  parm-data
PROCEDURE DIVISION using the-parm
OPEN the output
MOVE parm-data to the output record
WRITE the output
CLOSE the output
GOBACK
Have a good one

okonita1
Member
Posts: 12
Joined: Thu Jan 03, 2013 12:18 pm

Post by okonita1 » Sat Jan 05, 2013 6:45 pm

Yes, one could write such a program but I refuse to believe that with all the tech advances of the 21st century, there is no such IBM utility that can take a PARM data and write it out to a file. ICETOOL looked promising but I just son't the syntax to extract and write parm data to a file...

Thanks DikDude for the suggestion...
okonita1

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Sun Jan 06, 2013 2:50 am

I suspect this has not been a priority for most software vendors because of the ability of programs to input the parm directly. If you are working with something that can read the parm from a file, why can this process not just use the parm and save the extra work of processing a new file?

Over the many organizations i've supported, several do have their own utilities to do what you want (and another one to take a value from a parm and set this as a return-code for checking in the downstream jcl).
Have a good one

okonita1
Member
Posts: 12
Joined: Thu Jan 03, 2013 12:18 pm

Post by okonita1 » Sun Jan 06, 2013 6:33 pm

DikDude,

Again thank you for your continued comment for this thread. Much appreciated. 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. 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.

If I may ask, can you tell me categorically if the ICETOOL is not really capable of accepting and writing PARM= data to a file?
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 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.
okonita1

William Collins
Active Member
Posts: 732
Joined: Thu May 24, 2012 4:07 am

Post by William Collins » Mon Jan 07, 2013 3:20 am

Well, ICETOOL uses JPn.

However, you don't have ICETOOL. You have SyncSort's SYNCTOOL, ALIASed TO ICETOOL.

Your SYNCTOOL is from 2007, so definitely does not support JPn. It may be supported in the latest release, but you'd have to check on docs for that/talk to SyncSort.

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Mon Jan 07, 2013 6:21 am

The first thing i'd suggest is to have your release of Syncsort upgraded - many, many of the new features are not available to you.

Is some manager in your organization willing to allow something like this to be promoted to Production? Nowhere i've supported would even consider this . . .

I do not have the Syncsort documentation this weekend, but i will look for sometime on Monday to see if i can find anything. If te product does do this, i have not yet seen it used. As i mentioned before, there has been no requirement to do this.

Just curious, but why to you want to add additonal processing to get a parm into a program when this is already supported?

Yup, i remain confused :?
Have a good one

okonita1
Member
Posts: 12
Joined: Thu Jan 03, 2013 12:18 pm

Post by okonita1 » Tue Jan 08, 2013 4:52 am

DikDude said:
"Just curious, but why to you want to add additonal processing to get a parm into a program when this is already supported?"

I am not trying to get a parm into any program. I am trying to get the parm into a flat file or a PDS member so that I can feed this file to IKJEFT01 simulating dynamic SQL processing. IKJEFT01 cannot resolve symbolic parm substitution while executing DSNTIAUL...for example:
//UTL1010U EXEC PGM=IKJEFT01,DYNAMNBR=20,
//STEPLIB DD DSN=SYS2.DB2.&LOADIR..SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSREC00 DD DSN=&&OUTSPACE,UNIT=TEMP,DISP=(NEW,PASS),
// SPACE=(CYL,(1,1),RLSE),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=0)
//SYSPRINT DD SYSOUT=*
//SYSPUNCH DD DUMMY
//SYSTSIN DD *
DSN SYSTEM(DBO1) RETRY(3)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) -
LIB('SYS2.DB2.TEST.RUNLIB.LOAD') PARMS('SQL')
END
/*
//SYSIN DD *
SELECT '-START DB('||DBNAME||') SPACENAM('||NAME||') ACCESS(UT)'
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME LIKE '&PDBNAME' <<<-------- This will not work
WHERE DBNAME LIKE 'OG1DD%1' <<<-------- This will work

It may not be elegant but it will do what I need done efficiently. Here's my simple reason:

I have several database in 5 LPARs, along with object changes, it is constantly being refreshed from production sometimes selectively and other times the entire subsystem is refreshed.. To unload/load or copy tablespeces, I either have to develop Jobs/procs/parmlib control members specific to each subsystem and databases (177 jobs and procs) or I can try to develop genereric Job/procs/ with strategically placed substitution parameters that I can use across the LPARs. Since I am very much challenged with regards to DB2 Admin toolset...

With 1 Procedure, I can now use 1 proc vs 22 procs to run/control which database to execute copy/reorg etc, etc on.

Thanks
okonita1

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Wed Jan 09, 2013 2:46 am

Hello,

Much more clear - thanks :)

One way to do what you want is to have 22 members with a unique value for the 22 control values.

For the SYSIN, you might use:

//SYSIN DD *
SELECT '-START DB('||DBNAME||') SPACENAM('||NAME||') ACCESS(UT)'
FROM SYSIBM.SYSTABLESPACE
// DD DSN=(the.pds.with.the.mbrs(&thewhere),DISP=SHR

If this process is already part of a cataloged PROCedure (or can be put in one), you could supply the member name on the proc execute statement?
Have a good one

William Collins
Active Member
Posts: 732
Joined: Thu May 24, 2012 4:07 am

Post by William Collins » Wed Jan 09, 2013 6:22 am

I just looked at your Sort cards. Just for information:

Code: Select all

INCLUDE COND=&#40;1,44,CH,EQ,C'JP1',AND,1,44,CH,EQ,C'JP2'&#41;
You've used here two literals with a value of "JP1" and "JP2", you are not using the value contained in JP1 and JP2 (which you can't with your SyncTool). I suspect you are showing us a version after you got rid of some syntax errors while attempting to get it to work.

academyindia4

Topic deleted by Admin

Post by academyindia4 » Mon Jan 25, 2016 9:47 pm

<< Content deleted By Admin >>

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others