|
View previous topic :: View next topic
|
| Author |
Message |
Guest
|
|
|
|
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
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Guest
|
|
|
|
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
Global Moderator
Joined: 26 Apr 2004
Posts: 4652
Location: Raleigh, NC, USA
|
|
|
|
| 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 |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
| ISPF File Tailoring services? |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
| 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 |
|
|
Guest
|
|
|
|
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
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
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 |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
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 |
|
|
Guest
|
|
|
|
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
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
| 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 |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
| 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
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
Thanks Kolusu  . |
|
| Back to top |
|
|
vnktrrd
New User
Joined: 12 Jan 2010
Posts: 34
Location: New York
|
|
|
|
Hi All,
I have similar requirement where I don't want to repeat the steps coded under HEADER3. Instead the job with one step has to be repeated for every value of the input and submitted through internal reader.
Please advise how to do it. When I move the "Sections=(81,3," above HEADER1, its giving me error.
Thanks. |
|
| Back to top |
|
|
vnktrrd
New User
Joined: 12 Jan 2010
Posts: 34
Location: New York
|
|
|
|
| Code: |
//GTMCREO1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=FILE1,
// DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY,STOPAFT=255
OUTREC OVERLAY=(81:SEQNUM,3,ZD)
OUTFIL REMOVECC,NODETAIL,BUILD=(80X),
HEADER1('//JOB1 JOB ',X'7D','SPLIT JOBS',X'7D',
',CLASS=A,',/,
'//',13X,'MSGCLASS=Y,MSGLEVEL=(1,1),',/,
'//',13X,'NOTIFY=&SYSUID',/,
'//*',/,
'/*JOBPARM S=P000',/,
'//PROCLIB JCLLIB ORDER=PROCLIB'),
SECTIONS=(81,3,
HEADER3=('//STEP0',81,3,X,'EXEC PROC1,',/,
'// FRMTS=2018-07-25-00.00.00.000000,',/,
'// TOTS=2018-07-25-00.00.00.000000,',/,
'// VALUE=P001,VERS=VERS1,INPFIELD=',1,4),
TRAILER3=('//*'))
//*
|
To explain clearly this is the code I have to create a job and add one step for each value of the INPFIELD from input file FILE1.
I want this code to be modified to generate a 'job and one step' for each value of the INPFIELD and submit it to internal reader. I dont multiple steps in the same job for each field. I want one job for each INPFIELD.
Please advise.
Thanks in advance. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
