IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need help on SORT to write each input record into diff file.


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
rahul sijoriya

New User


Joined: 08 Jan 2007
Posts: 11
Location: hyderabad

PostPosted: Mon Dec 02, 2013 9:09 pm
Reply with quote

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
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Mon Dec 02, 2013 9:50 pm
Reply with quote

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
View user's profile Send private message
rahul sijoriya

New User


Joined: 08 Jan 2007
Posts: 11
Location: hyderabad

PostPosted: Mon Dec 02, 2013 9:53 pm
Reply with quote

Thanks for suggestion Terry. I will use the same for going forward.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Mon Dec 02, 2013 10:50 pm
Reply with quote

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
View user's profile Send private message
rahul sijoriya

New User


Joined: 08 Jan 2007
Posts: 11
Location: hyderabad

PostPosted: Mon Dec 02, 2013 11:10 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Access to non cataloged VSAM file JCL & VSAM 18
Search our Forums:

Back to Top