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
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?
Joined: 22 Apr 2006 Posts: 6250 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:
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
Joined: 22 Apr 2006 Posts: 6250 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?
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.
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.
.