Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need help on SORT to write each input record into diff file.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
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    Post subject: Need help on SORT to write each input record into diff file.
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    Post subject:
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    Post subject: Reply to: Need help on SORT to write each input record into
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Mon Dec 02, 2013 10:50 pm    Post subject: Reply to: Need help on SORT to write each input record into
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    Post subject: Reply to: Need help on SORT to write each input record into
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    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am
No new posts Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Execute JCL step based on the content... sprikitik JCL & VSAM 2 Tue Oct 03, 2017 10:03 am
This topic is locked: you cannot edit posts or make replies. PS file data should be passed as symb... d_sarlie JCL & VSAM 15 Tue Oct 03, 2017 5:18 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us