Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Dynamic JCL step generation

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Guest







PostPosted: Thu Dec 02, 2010 10:11 am    Post subject: Dynamic JCL step generation
Reply with quote

Please find my requirement:
I have a file containing n sql statements. I want the output of each query in a separate dataset(n). Is there a way to dynamically create jcl steps one for each query based on the number of queries in input file.
I want the same to be done in a JCL.
Please let me know if i am not clear
Back to top

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Dec 02, 2010 10:18 am    Post subject:
Reply with quote

Hello and welcome to the forum,

Why do you believe this is a sort question?

Quote:
I want the same to be done in a JCL.
This won't happen "in a JCL" no matter how you approach it. JCL only executes programs - the sort, a utility, developer written code, etc.

You could fairly easily do what you want with COBOL or Easytrieve. If you know rexx, you could do this in rexx.
Back to top
View user's profile Send private message
Guest







PostPosted: Thu Dec 02, 2010 10:48 am    Post subject:
Reply with quote

Thanks for the reply.
Actually Rexx, helped us in solving the same, but is there a way to incorporate the function of iteration in JCL?

Adding a feature to DFSORT or ICETOOL.

Though the suggestion may seem naive, at one or the other point everyone gets to this Road block.

Kindly advice!
Back to top
superk

Moderator Team Head


Joined: 26 Apr 2004
Posts: 4648
Location: Raleigh, NC, USA

PostPosted: Thu Dec 02, 2010 11:01 am    Post subject: Reply to: Dynamic JCL step generation
Reply with quote

Well, like anything else, you'd have to have DFSORT make a pass through the data and calculate how many DD statements and corresponding datasets will be required. Then, that information would be written out to either sysout or to a dataset. At that point, it would be up to you to determine how you'd use it. Would you build an entire job and submit it through the Internal Reader? Would you use it in a subsequent job as part of an INCLUDE JCL statement? Use it in a PROC? Some combination of all of the above?
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Thu Dec 02, 2010 1:00 pm    Post subject:
Reply with quote

ISPF File Tailoring services?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Dec 02, 2010 3:04 pm    Post subject: Re: Dynamic JCL step generation
Reply with quote

devil13 wrote:
I have a file containing n sql statements. I want the output of each query in a separate dataset(n).
Suggeste you show us some sample data you've in the file and tell us what do you expect as output. Possibly, a SORT application can, then, be written.
Back to top
View user's profile Send private message
Guest







PostPosted: Thu Dec 02, 2010 6:52 pm    Post subject:
Reply with quote

Thanks all for the reply.


My requirement:
file 1:
select * from table1;
select * from table2;

job:
step1:
// pgm=I***
//input first query
//output first query result

step2:
//pgm=i***
//input second query
//output second query result

suppose file has n query i have to dynamically generate n steps in the job.

This can be done in Rexx. But just want to find a way for the same in sort.


[/quote]
Back to top
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Dec 02, 2010 9:49 pm    Post subject:
Reply with quote

Hello,

You might consider creating a member (in a single-use pds defined for this process) for each query - (rexx?).

Also, create a little proc to run one query and use the member name as a symbolic parameter - (one-time edit).

Then generate a job that contains multiple executions of the query proc - one for each member (rexx or ?).
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Dec 02, 2010 10:12 pm    Post subject:
Reply with quote

You lack a lot of information in your post, viz:

1. Is there no WHERE clause in your query and query is just as is as you show them?
2. What is the LRECL/RECFM of input?
3. You say "n" queries, do you know the value of "n" in advance? Per your example, n=2 (if it's not, below JCL is a garbage but then what about the data you show us).

However, for the kind of data you show, this basic sort can be used:
Code:
//SPLIT   EXEC PGM=SORT                         
//SYSOUT  DD SYSOUT=*                           
//SORTIN  DD *                                 
SELECT * FROM TABLE1;                           
SELECT * FROM TABLE2;                           
//OUT1    DD DSN=HLQ.SPLIT1,DISP=(NEW,CATLG), 
//           SPACE=(CYL,(5,5)),UNIT=SYSDA       
//OUT2    DD DSN=HLQ.SPLIT2,DISP=(NEW,CATLG), 
//           SPACE=(CYL,(5,5)),UNIT=SYSDA       
//SYSIN   DD *                                 
  OPTION COPY                                   
  INCLUDE COND=(1,6,CH,EQ,C'SELECT')           
  OUTFIL FNAMES=(OUT1,OUT2),SPLIT         
/*                                             
//*                                             
In other steps of your JCL, where you need the query, refer the DSN of OUT1, OUT2 etc.

If n=2 i snot strictly 2, as it implies -
Code:
OUTFIL FNAMES=OUT1,INCLUDE=(1,80,CH,EQ,C'table1')
  OUTFIL FNAMES=OUT2,INCLUDE=(1,80,CH,EQ,C'table2') 
but this largely depends on, what are your answers to the other questions...
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Dec 02, 2010 11:10 pm    Post subject: Reply to: Dynamic JCL step generation
Reply with quote

Anuj,

I think you misunderstood the requirement. OP wants to generate the output from the sql statements into a different dataset. He needs to generate a dynamic JCL based on the number of select statements.

devil13,

As your input is a sql statement I am assuming that they are going to be inputs for a unload utility like DSNTIAUL or DSNTEP2. I am showing the example for DSNTIAUL.

I assumed that your each sql statement is just 1 line. Also A job can have a maximum of 255 job steps, so I am limiting the generation of JCL to just 255 steps.

The following DFSORT JCL will generate the dynamic JCL. Once you have verified the jcl created is good, change the statement
Code:
//SORTOUT   DD SYSOUT=*


to

Code:
//SORTOUT   DD SYSOUT=(*,INTRDR)



Code:

//STEP0100 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*                                       
//SORTIN   DD *                                               
SELECT * FROM TABLE1;                                         
SELECT * FROM TABLE2;                                         
//SORTOUT  DD SYSOUT=*                                       
//SYSIN    DD *                                               
  OPTION COPY,STOPAFT=255                                     
  OUTREC OVERLAY=(81:SEQNUM,3,ZD)                             
  OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                       
  HEADER1('//USERIDZ JOB ',X'7D','SPLIT JOBS',X'7D',         
          ',CLASS=A,',/,                                     
          '//',13X,'MSGCLASS=Y,MSGLEVEL=(1,1),',/,           
          '//',13X,'NOTIFY=&SYSUID',/,                       
          '//*'),                                             
  SECTIONS=(81,3,                                             
  HEADER3=('//STEP0',81,3,X,'EXEC PGM=IKJEFT01',/,           
           '//STEPLIB  DD DSN=DB2T.SDSNLOAD,DISP=SHR',/,     
           '//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121',/,       
           '//SYSPRINT DD SYSOUT=*',/,                       
           '//SYSTSIN  DD *',/,                               
           '  DSN SYSTEM(DB2T)',/,                           
           '  RUN PROGRAM(DSNTIAUL) - ',/,                   
           '      PLAN(DSNTIAUL)    - ',/,                   
           '      PARMS(''','SQL''',') - ',/,                 
           '      LIB(''','DB2T.RUNLIB.LOAD''',')',/,         
           '//*',/,                                           
           '//SYSREC00 DD DSN=YOUR.QUERY.OUT',81,3,',',/,     
           '//            DISP=(NEW,CATLG,DELETE),',/,       
           '//            UNIT=SYSDA,',/,                     
           '//            SPACE=(CYL,(X,Y),RLSE)',/,         
           '//SYSPUNCH DD SYSOUT=*',/,                       
           '//SYSIN    DD *'),                               
  TRAILER3=(1,80,/,'//*'))                                   
//*


This will create a JCL which looks like this

Code:

//USERIDZ JOB 'SPLIT JOBS',CLASS=A,         
//             MSGCLASS=Y,MSGLEVEL=(1,1),   
//             NOTIFY=&SYSUID               
//*                                         
//STEP0001 EXEC PGM=IKJEFT01               
//STEPLIB  DD DSN=DB2T.SDSNLOAD,DISP=SHR   
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121     
//SYSPRINT DD SYSOUT=*                     
//SYSTSIN  DD *                             
  DSN SYSTEM(DB2T)                         
  RUN PROGRAM(DSNTIAUL) -                   
      PLAN(DSNTIAUL)    -                   
      PARMS('SQL') -                       
      LIB('DB2T.RUNLIB.LOAD')               
//*                                         
//SYSREC00 DD DSN=YOUR.QUERY.OUT001,       
//            DISP=(NEW,CATLG,DELETE),     
//            UNIT=SYSDA,                   
//            SPACE=(CYL,(X,Y),RLSE)       
//SYSPUNCH DD SYSOUT=*                     
//SYSIN    DD *                             
SELECT * FROM TABLE1;                       
//*                                         
//STEP0002 EXEC PGM=IKJEFT01               
//STEPLIB  DD DSN=DB2T.SDSNLOAD,DISP=SHR   
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121     
//SYSPRINT DD SYSOUT=*                     
//SYSTSIN  DD *                             
  DSN SYSTEM(DB2T)                         
  RUN PROGRAM(DSNTIAUL) -                   
      PLAN(DSNTIAUL)    -                   
      PARMS('SQL') -                       
      LIB('DB2T.RUNLIB.LOAD')               
//*                                         
//SYSREC00 DD DSN=YOUR.QUERY.OUT002,           
//            DISP=(NEW,CATLG,DELETE),         
//            UNIT=SYSDA,                     
//            SPACE=(CYL,(X,Y),RLSE)           
//SYSPUNCH DD SYSOUT=*                         
//SYSIN    DD *                               
SELECT * FROM TABLE2;                         
//*
Back to top
View user's profile Send private message
Guest







PostPosted: Fri Dec 03, 2010 11:37 am    Post subject:
Reply with quote

Thanks a lot Kolusu, your JCL matched my requirement exactly.
This was a great learning for me.
DFSORT is amazing and Hats off to you!!!!

Anuj,
Please find the Rexx code which I used for my requirement:
Code:

 SAY 'ENTER DATASET NAME:'                                             
 PULL JOBFL                                                             
"ALLOC F(INPT) DA('"JOBFL"') SHR REUSE"                                 
"EXECIO * DISKR INPT (FINIS STEM INPT."                                 
"FREE F(INPT)"                                                         
ADDRESS TSO                                                             
QUEUE "//TT****  JOB 1,CLASS=I,MSGCLASS=0,NOTIFY=&SYSUID"
DO I=1 TO INPT.0                                                       
   REC = INPT.I                                                         
   CALL CNT                                                             
END                                                                     
QUEUE "££"                                                             
OUTPUT = 0                                                             
X = OUTTRAP("OUTPUT.",'*',"CONCAT")                                     
ADDRESS TSO "SUBMIT * END(££)"                                         
X = OUTTRAP('OFF')                                                     
EXIT         

CNT:                                                                                                       
REC  = STRIP(FIL)                                                       
QUEUE "//STEP001  EXEC PGM=IKJEFT01,DYNAMNBR=20   "
QUEUE "//STEPLIB  DD DSN=DB2T.SDSNLOAD,DISP=SHR "
QUEUE "//SYSTSIN  DD *                                                 "
QUEUE "  DSN SYSTEM(DB2T) RUN PROGRAM(DSNTIAUL) - "
QUEUE "    PLAN (DSNTIAUL) PARMS('SQL') -    "
QUEUE "    LIB('DB2T.RUNLIB.LOAD')                                "
QUEUE "//SYSTSPRT DD SYSOUT=*                                  "
QUEUE "//SYSREC00 DD DSN=OUTPUT.QURY.GDG(+"I"),   "     
QUEUE "//             DISP=(,CATLG),           "
QUEUE "//             LRECL=187,RECFM=FB                         "
QUEUE "//SYSPRINT DD SYSOUT=* "
QUEUE "//SYSPUNCH DD SYSOUT=* "     
QUEUE "//SYSIN DD *"
QUEUE " "REC                                             
 QUEUE "/*                                                              "
 RETURN                                                                 
                                                         
Back to top
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Dec 03, 2010 2:59 pm    Post subject: Re: Reply to: Dynamic JCL step generation
Reply with quote

Skolusu wrote:
I think you misunderstood the requirement. OP wants to generate the output from the sql statements into a different dataset. He needs to generate a dynamic JCL based on the number of select statements.
I see - then I got it wrong for sure.

On the other hand, if the SQL Query is just not contain only a SELECT, then? Something like
Code:
select * from table
where col1= something
      col2= something
.
.
I mean what if a single query spans to multiple lines?
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Sat Dec 04, 2010 12:25 am    Post subject: Re: Reply to: Dynamic JCL step generation
Reply with quote

devil13 wrote:
Thanks a lot Kolusu, your JCL matched my requirement exactly. This was a great learning for me.


Devil13,

Just for the record you really don't have to generate "n" steps. DSNTIAUL is capable of unloading up to 100 DB2 tables in a single step.

check this example

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/dsnapj12/APPENDIX1.3.1?DT=20050328013405#FIGTIAUSPM

Anuj Dhawan wrote:
I mean what if a single query spans to multiple lines?


Quite simple , you will use When=GROUP to tag the sql as a group which begin with SELECT and end with a Semicolon.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Sat Dec 04, 2010 4:52 pm    Post subject:
Reply with quote

Thanks Kolusu icon_smile.gif.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Random Password (in string format) ge... ezhavendhan COBOL Programming 10 Mon Aug 29, 2016 3:18 pm
No new posts Converting multiple VB files to FB fi... Viswanath Reddy JCL & VSAM 6 Mon Aug 08, 2016 11:49 pm
No new posts Creating a dynamic sort card in JCL u... ChitraChhabra DFSORT/ICETOOL 4 Wed Aug 03, 2016 6:15 pm
This topic is locked: you cannot edit posts or make replies. JCL MULTIPLE STEP EXECUTION QUERY Susanta JCL & VSAM 18 Sat Jul 30, 2016 1:17 pm
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us