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
 

 

using SYNCSORT to match records from 2 infiles
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
Beth Parker

New User


Joined: 08 Oct 2013
Posts: 10
Location: US

PostPosted: Tue Oct 08, 2013 9:38 pm    Post subject: using SYNCSORT to match records from 2 infiles
Reply with quote

I am trying to figure out how to read in 2 input files and write out only the records that match. I took a look at the posting about the overlay command but that write all records out.

can this be used to find matches?
IFTHEN=(WHEN=NONE)

any ideas? is there a idcams utility?
Back to top
View user's profile Send private message

superk

Moderator Team Head


Joined: 26 Apr 2004
Posts: 4648
Location: Raleigh, NC, USA

PostPosted: Tue Oct 08, 2013 9:44 pm    Post subject: Reply to: using SYNCSORT to match records from 2 infiles
Reply with quote

Don't you want to use a JOINKEYS operation?
Back to top
View user's profile Send private message
Beth Parker

New User


Joined: 08 Oct 2013
Posts: 10
Location: US

PostPosted: Tue Oct 08, 2013 9:53 pm    Post subject: Reply to: using SYNCSORT to match records from 2 infiles
Reply with quote

if I need to JOINKEYS to get it to work then Yes.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Oct 08, 2013 9:54 pm    Post subject:
Reply with quote

Do you have DFSORT (ICE messages) or Syncsort (WER messages)?
Back to top
View user's profile Send private message
Beth Parker

New User


Joined: 08 Oct 2013
Posts: 10
Location: US

PostPosted: Tue Oct 08, 2013 10:09 pm    Post subject: Reply to: using SYNCSORT to match records from 2 infiles
Reply with quote

I believe we do but I have never used them.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Oct 08, 2013 10:31 pm    Post subject:
Reply with quote

Actually, that was "which one do you have?"; the syntax is slightly different.

However, here is an example for a simple JOINKEYS is Syncsort:
Code:
//SHGBSORT JOB  ,SYNCSORT,CLASS=S,MSGCLASS=1,                           
//         REGION=0M,SCHENV=JOB@ANY                                     
//*                                                                     
//******************************************************************** 
//STEP1    EXEC PGM=SORT                                               
//SYSOUT   DD   SYSOUT=*                                               
//SORTJNF1 DD *                                                         
0001 THIS IS A TEST                                                     
0002 THIS IS ANOTHER TEST                                               
0003 KATIE IS A PEST                                                   
0004  LOREM IPSUM DOLOR SIT AMET, CONSECTETUR ADIPISICI ELIT, SED DO   
0005 EIUSMOD TEMPOR INCIDIDUNT UT LABORE ET DOLORE MAGNA ALIQUA. UT ENIM
0006 MINIM VENIAM, QUIS NOSTRUD EXERCITATION ULLAMCO LABORIS NISI UT ALI
0007 EX EA COMMODO CONSEQUAT. DUIS AUTE IRURE DOLOR IN REPREHENDERIT IN
0008 VOLUPTATE VELIT ESSE CILLUM DOLORE EU FUGIAT NULLA PARIATUR. EXCEPT
0009 SINT OCCAECAT CUPIDATAT NON PROIDENT, SUNT IN CULPA QUI OFFICIA DES
//SORTJNF2 DD *                                                         
        0001                                                           
        0004                                                           
        0009                                                           
        0016                                                           
        0025                                                           
        0036                                                           
        0049                                                           
        0064                                                           
        0081                                                           
        0100                                                           
        0121                                                           
//SORTOUT  DD   SYSOUT=*                                               
//SYSIN    DD   *                                                       
  JOINKEYS FILE=F1,FIELDS=(1,4,A),SORTED                               
  JOINKEYS FILE=F2,FIELDS=(9,4,A),SORTED                               
  REFORMAT FIELDS=(F1:6,67)                                             
  SORT FIELDS=COPY                                                     

It matches on a four-byte key that is position 1 in file 1, and position 9 in file 2; both are assumed already sorted. It them copies file 1 from position 6 to 72 to the output:
Code:
THIS IS A TEST                                                     
 LOREM IPSUM DOLOR SIT AMET, CONSECTETUR ADIPISICI ELIT, SED DO   
SINT OCCAECAT CUPIDATAT NON PROIDENT, SUNT IN CULPA QUI OFFICIA DES
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Tue Oct 08, 2013 11:06 pm    Post subject: Reply to: using SYNCSORT to match records from 2 infiles
Reply with quote

Definitely JOINKEYS.

If you are stuck, describe your requirement exactly, show some sample input, expected output, what you have tried, and output received from the sample input.
Back to top
View user's profile Send private message
Beth Parker

New User


Joined: 08 Oct 2013
Posts: 10
Location: US

PostPosted: Tue Oct 08, 2013 11:33 pm    Post subject: Reply to: using SYNCSORT to match records from 2 infiles
Reply with quote

IT WORKED!!

//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,1400,A)
JOINKEYS FILE=F2,FIELDS=(1,1400,A)
REFORMAT FIELDS=(F1:1,1400)
SORT FIELDS=COPY
//*

the JOINKEYS and REFORMAT created an output file with just the matches.
THANKS SOO MUCH! icon_biggrin.gif
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Oct 08, 2013 11:35 pm    Post subject:
Reply with quote

You're welcome icon_smile.gif
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Tue Oct 08, 2013 11:49 pm    Post subject: Reply to: using SYNCSORT to match records from 2 infiles
Reply with quote

Is your data in sequence? Should you be matching 1-to-1 on position, or by a key. You might have some good-looking output, but ensure that you it fits whatever your actual requirement is.
Back to top
View user's profile Send private message
Beth Parker

New User


Joined: 08 Oct 2013
Posts: 10
Location: US

PostPosted: Tue Oct 08, 2013 11:51 pm    Post subject: Reply to: using SYNCSORT to match records from 2 infiles
Reply with quote

I sorted the 2 files in the same order before joining
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Wed Oct 09, 2013 1:06 am    Post subject: Reply to: using SYNCSORT to match records from 2 infiles
Reply with quote

OK, so you should specify SORTED on each of the JOINKEYS statements, othewise they'll be sorted again. Or don't SORT them separately.

By default JOINKEYS sorts its input, with EQUALS. If it is OK for the last record to match against the first once SORTed, then it is simpler to let the JOINKEYS do the whole thing.

When first using JOINKEYS people are often not aware that the data gets SORTed by the JOINKEYS, so they end up sorting everything twice...
Back to top
View user's profile Send private message
Beth Parker

New User


Joined: 08 Oct 2013
Posts: 10
Location: US

PostPosted: Wed Oct 09, 2013 1:14 am    Post subject: Reply to: using SYNCSORT to match records from 2 infiles
Reply with quote

after running some compares on the output from the join keys to the original files and found 3 out of 5 joinkeys worked perfectly. However the 2 out of 5 did not. There may be an issue with the second file being joined. Thanks for the comments about the SORT. I was sorting them twice but that should not impact the results.

SYSOUT results on one of the sets of fiels that did not work
SORTJNF1 : RCD IN= 163849,OMITTED= 0,PAIRED= 162679,UNPAIRED= 1170
SORTJNF1 : EXCP'S=62,UNIT=3390,DEV=9966,CHP=(101114181C202425,1),VOL=L
all of F1 should have paired to F2...
concluding bad data in F2
Back to top
View user's profile Send private message
Beth Parker

New User


Joined: 08 Oct 2013
Posts: 10
Location: US

PostPosted: Wed Oct 09, 2013 1:20 am    Post subject: Reply to: using SYNCSORT to match records from 2 infiles
Reply with quote

Do you kno whow to capture the UNPAIRED records in a separate file?
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Wed Oct 09, 2013 1:27 am    Post subject:
Reply with quote

You still haven't said whether you're using DFSORT or Syncsort. Please do so, as the syntax differs in this case.
Back to top
View user's profile Send private message
Beth Parker

New User


Joined: 08 Oct 2013
Posts: 10
Location: US

PostPosted: Wed Oct 09, 2013 1:41 am    Post subject: Reply to: using SYNCSORT to match records from 2 infiles
Reply with quote

I am using SYNCSORT
what does this do to the output

JOIN UNPAIRED,F2

does it move the UNPAIRED records to F2? becuase I do not want that.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Wed Oct 09, 2013 2:08 am    Post subject:
Reply with quote

JOIN UNPAIRED,F2 creates a "right outer join" where unpaired records from F2 as well as paired (matched) records are written to the output. This does not seem to be what you want.

I have Syncsort 1.4.0.1; if you have a significantly back-leveled version, this may not work. However, try these control cards:
Code:
  JOINKEYS FILE=F1,FIELDS=(1,1400,A),SORTED                         
  JOINKEYS FILE=F2,FIELDS=(1,1400,A),SORTED                         
  JOIN UNPAIRED,F1,F2                                             
  REFORMAT FIELDS=(F1:1,1400,F2:1,1400),FILL=C'$'                     
  OPTION COPY                                                     
  OUTFIL FNAMES=F1ONLY,INCLUDE=(1401,1,CH,EQ,C'$'),                 
    BUILD=(1,1400)                                                 
  OUTFIL FNAMES=F2ONLY,INCLUDE=(1,1,CH,EQ,C'$'),                 
    BUILD=(1401,1400)                                                 
  OUTFIL FNAMES=BOTH,INCLUDE=(1401,1,CH,NE,C'$',AND,1,1,CH,NE,C'$'),
    BUILD=(1,1400)                                                 

I assume:
  1. The LRECL of both data sets is 1400 (if not, adjust positions and lengths accordingly)
  2. The entire record is being matched (if not, again adjust appropriately)
  3. The character "$" does not appear in the first position in either data set (if it does, choose a fill character that does not).
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Wed Oct 09, 2013 4:25 am    Post subject: Reply to: using SYNCSORT to match records from 2 infiles
Reply with quote

Code:
  OUTFIL FNAMES=BOTH,SAVE,
    BUILD=(1,1400)


The SAVE will cause all records which do not appear on the other two OUTFIL datasets to appear here. Clearer to read, reduces mistakes due to typos.
Back to top
View user's profile Send private message
Beth Parker

New User


Joined: 08 Oct 2013
Posts: 10
Location: US

PostPosted: Wed Oct 09, 2013 11:43 pm    Post subject: Reply to: using SYNCSORT to match records from 2 infiles
Reply with quote

OUTFIL FNAMES=BOTH,SAVE,
BUILD=(1,1400)


Does "BOTH" become the name of the outfile?
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Wed Oct 09, 2013 11:49 pm    Post subject:
Reply with quote

Yes; remember the difference between a file name and a data set name in z/OS. Of course, you can use a different file name (so long as there is a DD statement for it in the JCL).
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts syncsort: copy lines after the keyword shreya19 SYNCSORT 6 Fri Dec 02, 2016 9:47 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Syncsort - Hard coded Value in output PORYES SYNCSORT 1 Thu Nov 03, 2016 9:23 am
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


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