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
 

 

Match to get first 50 records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
MSTP

New User


Joined: 21 Jun 2007
Posts: 29
Location: Baltimore

PostPosted: Tue Sep 23, 2008 12:54 am    Post subject: Match to get first 50 records
Reply with quote

Hi

FILEA has 500k records and FILEB has 100 records . For each record in FILEB there can a matching record in FILEA .FILEA is sorted as per FILEB records. I want to match FILEA and FILEB and want to extract only the first 50 of each matching into one FILEC from FILEA and all the other records into FILED from FILEA(FILEA – FILEC).

MSTP.
Back to top
View user's profile Send private message

MSTP

New User


Joined: 21 Jun 2007
Posts: 29
Location: Baltimore

PostPosted: Tue Sep 23, 2008 8:08 pm    Post subject: Syncsort/ICETOOL match to get first 50 records
Reply with quote

Hi

FILEA has 500k records and FILEB has 100 records . For each record in FILEB there can a matching record in FILEA .FILEA is sorted as per FILEB records. I want to match FILEA and FILEB and want to extract only the first 50 of each matching into one FILEC from FILEA and all the other records into FILED from FILEA(FILEA – FILEC).

MSTP
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2239
Location: @my desk

PostPosted: Tue Sep 23, 2008 9:29 pm    Post subject:
Reply with quote

MSTP,

I have assumed FB files of LRECL=80 and key fields of both the files starting at pos 1-3 and for extracting 2 matching records from file1 instead of 50. You can modify it as per your file attributes.
Code:
//STEP00   EXEC PGM=SORT           
//SYSOUT   DD SYSOUT=*             
//SORTJNF1 DD *                     
AAA 123                             
AAA 414                             
AAA 343                             
AAA 233                             
BBB 234                             
BBB 223                             
BBB 355                             
CCC 532                             
CCC 222                             
//SORTJNF2 DD *                     
AAA                                 
BBB                                 
CCC                                 
DDD                                 
//OUT1    DD SYSOUT=*               
//OUT2    DD SYSOUT=*               
//SYSIN   DD *                                         
  JOINKEYS FILE=F1,FIELDS=(1,3,A)                       
  JOINKEYS FILE=F2,FIELDS=(1,3,A)                       
  REFORMAT FIELDS=(F1:1,80)                             
  SORT FIELDS=COPY                                     
  INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))         
  OUTFIL FNAMES=OUT1,INCLUDE=(81,8,ZD,LE,2),BUILD=(1,80)
  OUTFIL FNAMES=OUT2,SAVE,BUILD=(1,80)                 
//* 

OUT1
Code:
AAA 123
AAA 414
BBB 234
BBB 223
CCC 532
CCC 222

OUT2
Code:
AAA 343
AAA 233
BBB 355

The above SYNCSORT works only if you have Syncsort for z/OS release 1.2 or later. Let me know if you come across any issues.
Back to top
View user's profile Send private message
MSTP

New User


Joined: 21 Jun 2007
Posts: 29
Location: Baltimore

PostPosted: Tue Sep 23, 2008 10:48 pm    Post subject:
Reply with quote

Arun

I tried with the above SORT parameters. It works to some extent but I am losing records from fileA like rcds ABC 111 which don't have a match to fileB records. Please let me know what modification is needed.

thanks
mstp
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Sep 24, 2008 12:44 am    Post subject:
Reply with quote

There seems to be some confusion about which SORT product you're using. Please run the following job and post the //SYSOUT messages:

Code:

//S1    EXEC  PGM=ICEMAN                                           
//SYSOUT    DD  SYSOUT=*                                           
//SORTIN DD *                                                     
RECORD 1                                                           
//SORTOUT DD SYSOUT=*   
//SYSIN    DD    *                                                 
  OPTION COPY     
/*                                               
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2239
Location: @my desk

PostPosted: Wed Sep 24, 2008 9:22 am    Post subject:
Reply with quote

Hello ,
Quote:
It works to some extent but I am losing records from fileA like rcds ABC 111 which don't have a match to fileB records.

From the above post, I assume the jcl ran successfully, but dint produce the results as you expected. Can you post the actual file attributes and the key positions on both the files and also some sample input/output records.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Sep 24, 2008 9:30 pm    Post subject:
Reply with quote

JOINKEYS implies the OP is using Syncsort, but he told another Moderator in an offline note that he's not using Syncsort. So we need to clear up which product he's using before we go any further with this.
Back to top
View user's profile Send private message
MSTP

New User


Joined: 21 Jun 2007
Posts: 29
Location: Baltimore

PostPosted: Sat Oct 11, 2008 1:01 am    Post subject:
Reply with quote

We are using Syncsort and below is the sort card I used to get the rqd data.Thank for all the help

OPTION EQUALS
JOINKEYS FILE=F1,FIELDS=(65,8,A)
JOINKEYS FILE=F2,FIELDS=(1,8,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,300,F2:1,8)
SORT FIELDS=COPY
INREC OVERLAY=(309:SEQNUM,8,ZD,RESTART=(65,8))
OUTFIL FNAMES=SORTOUT1,INCLUDE=((309,8,ZD,LE,50),
AND,(301,8,CH,NE,C' ')),
BUILD=(1,300)
OUTFIL FNAMES=SORTOUT2,INCLUDE=((309,8,ZD,GT,50),
OR,(301,8,CH,EQ,C' ')),
BUILD=(1,300)
************************** Bottom of Data ********************
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2239
Location: @my desk

PostPosted: Sun Oct 12, 2008 7:55 am    Post subject:
Reply with quote

MSTP,

You can further simplify this by using the SAVE parameter for SORTOUT2 like this.
Code:
OUTFIL FNAMES=SORTOUT2,SAVE,BUILD=(1,300)
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 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
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am


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