Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Extract first 10 records which falls under a key

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
sthirumalai

New User


Joined: 14 Aug 2007
Posts: 14
Location: Chennai

PostPosted: Tue Jun 21, 2011 11:17 pm    Post subject: Extract first 10 records which falls under a key
Reply with quote

Hi

I have a FB byte file of length 80 Bytes.

first 10 bytes have account number and starting from 11 to 18 we got the transaction amount.

The file is sorted on account number and transaction amount ascending.

I have to fetch first 10 transactions under each account number and I have to skip the rest of the records under the account number till the next account number is obtained.

Please note that an account number may also have less than 10 transactions. In that case I need all those records under the account number to be extracted.

Is there any way to do this using DFsort?

Thanks & Regards
Santhosh
Back to top
View user's profile Send private message

Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Jun 21, 2011 11:43 pm    Post subject: Reply to: Extract first 10 records which falls under a key
Reply with quote

sthirumalai,

Use this DFSORT/ICETOOL JCL

Code:

//STEP0100 EXEC PGM=ICETOOL                                 
//TOOLMSG  DD SYSOUT=*                                     
//DFSMSG   DD SYSOUT=*                                     
//IN       DD *                                             
----+----1----+----2----+----3----+----4----+----5----+----6
KEY1            01                                         
KEY1            02                                         
KEY1            03                                         
KEY1            04                                         
KEY1            05                                         
KEY1            06                                         
KEY1            07                                         
KEY1            08                                         
KEY1            09                                         
KEY1            10                                         
KEY1            11                                         
KEY1            12                                         
KEY1            13                                         
KEY1            00                                         
KEY2            12                                         
KEY2            10                                         
KEY2            00                                         
//OUT      DD SYSOUT=*                                     
//TOOLIN   DD *                                             
  SELECT FROM(IN) TO(OUT) ON(1,10,CH) FIRST(10) USING(CTL1)
//CTL1CNTL DD *                                             
  SORT FIELDS=(01,10,CH,A,                                 
               11,08,ZD,A)                                 
//*
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Tue Jun 21, 2011 11:57 pm    Post subject:
Reply with quote

Kolusu,
Quote:
The file is sorted on account number and transaction amount ascending.

Do we need to resort the file? Wouldn't below suffice the requirement?
Code:
//SYSIN    DD *                                             
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,10),PUSH=(81:SEQ=8))
  SORT FIELDS=COPY                                         
  OUTFIL INCLUDE=(81,8,ZD,LE,10),BUILD=(1,80)               
//*                                                         

OR
Code:
//SYSIN    DD *                                                   
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,10)))
  SORT FIELDS=COPY                                                 
  OUTFIL INCLUDE=(81,8,ZD,LE,10),BUILD=(1,80)                     
//*                                                               

Thanks,
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Jun 22, 2011 12:05 am    Post subject: Reply to: Extract first 10 records which falls under a key
Reply with quote

Sqlcode1 wrote:
Do we need to resort the file? Wouldn't below suffice the requirement?


Hmm I assumed that the sort need to be done and then pick the first 10. Well if the data is already sorted then he can simply replace CTL1CNTL with this.
Code:

//CTL1CNTL DD *                                             
  SORT FIELDS=COPY
//*


Now the question arises as to why the data is sorted in a different step when everything can be done in 1 pass.

I usually prefer SELECT for such operations instead of appending the key at the end and then having to change if there is change in the LRECL or RECFM. With SELECT operator there is no change at all.
Back to top
View user's profile Send private message
sthirumalai

New User


Joined: 14 Aug 2007
Posts: 14
Location: Chennai

PostPosted: Wed Jun 22, 2011 12:06 am    Post subject: Reply to: Extract first 10 records which falls under a key
Reply with quote

Hi Kolusu/Sqlcode,

Thanks for your help

Both the solution works 100%.

Regards
Santhosh
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts What is the Extract Function in JCL dharmaraok JCL & VSAM 3 Wed Jun 14, 2017 9:12 am
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us