View previous topic :: View next topic
|
Author |
Message |
rahul sijoriya
New User
Joined: 08 Jan 2007 Posts: 11 Location: hyderabad
|
|
|
|
Hello All,
I am new user here so please forgive me if i do any mistake here.
I have one input file where one or multiple records can be present.
Example:
Code: |
SELECT * FROM TABLE1
WHERE ID = '001'
AND NAME = 'DUMMY';
SELECT * FROM TABLE2
WHERE ID = '002'
AND NAME = 'DUMMY'
AND SURNAME = 'TEST'; |
Now i want to seprate these input SQL records into different output file and execute them to unload the data from DB2 table.
I found one the post where they are seprating the input recods and Build the JCL to unload the data. But in that post, they have only one line as a one record and second record in second line , so on.
I don't know how to use WHEN = GROUP option available in SORT.
Please suggest.
Code'd |
|
Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1249 Location: Richfield, MN, USA
|
|
|
|
One thing that would help readability would be to use the Code tags. Click Code before your code then Code again at the end. This preserves actual spacing between characters. See FAQ and read about Formatting and Topic Types. |
|
Back to top |
|
|
rahul sijoriya
New User
Joined: 08 Jan 2007 Posts: 11 Location: hyderabad
|
|
|
|
Thanks for suggestion Terry. I will use the same for going forward. |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
Code: |
OPTION COPY
INREC IFTHEN=(WHEN=GROUP,
BEGIN=(1,8,CH,EQ,C'SELECT '),PUSH=(81:ID=1)) |
This wil put an incremented number, starting from 1, on both your groups of records, in column 81 (I assumed F 80 records).
Then:
Code: |
OUTFIL FNAMES=DBSEL1,
INCLUDE=(81,1,CH,EQ,C'1'),
BUILD=(1,80)
OUTFIL FNAMES=DBSEL2,
SAVE,
BUILD=(1,80) |
This will put the first group of records onto DD DBSEL1 where the extended indicator is "1" and the second group (anything not accepted by the first OUTFIL) to DBSEL2.
The BUILDs are to reduce the records again to their original size. |
|
Back to top |
|
|
rahul sijoriya
New User
Joined: 08 Jan 2007 Posts: 11 Location: hyderabad
|
|
|
|
Quote: |
Thanks Bill for your reply.
but in my scenario, i am not sure how many SQL going to come as a input. it could be 1,2 or could be many more.
So i want Dynamic JCL which will separate the input SQL depending upon the number of SQL present in input file and then generate the Dynamic JCL to unload the data from DB2 table based upon the input SQL. |
Final Goal --> 1. Main JCL will read input file, extract first SQL, prepare Dynamic JCL to unload the data from DB2 table based upon first SQL.
2. then read second SQL from input file, prepare Dynamic SQL to unload th data from DB2 table based upon Second SQL.
3. This process should repeat until the EOF.
NOTE: Each SQL starts with "SELECT" and ends with ";"[/quote] |
|
Back to top |
|
|
|