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

Syncsort -display recs in file A,which are matched in File B


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

New User


Joined: 07 May 2006
Posts: 35

PostPosted: Thu Sep 16, 2010 12:19 pm
Reply with quote

Dear all,

I have two files.
File A as follows:
A1
A1
A2
A2
A3
FILE B as follows:
A1
A3
B1

i want to the results are: that is the recs in file a,which are all matched with the recz in fileb.
A1
A1
A3

I don't know whether my request is clear or not.
Thanks ahead.

Best Regards
Back to top
View user's profile Send private message
ankvardhan

New User


Joined: 05 Sep 2008
Posts: 14
Location: Mumbai

PostPosted: Thu Sep 16, 2010 12:38 pm
Reply with quote

Hi,

By using JoinKeys you can matched the records of both the files and display or write the same.

Syntax:
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=File A,DISP=SHR
//SORTJNF2 DD DSN=File B,DISP=SHR
//SORTOUT DD DSN=Output file ,DISP=SHR,
// DCB=(LRECL=18,RECFM=FB),UNIT=SYSDA,
// SPACE=(TRK,(10,1000))
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,2,A),SORTED
JOINKEYS FILES=F2,FIELDS=(1,2,A),SORTED
REFORMAT FIELDS=(F1:1,X,F2:1,Y)
OPTION COPY

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

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Sep 16, 2010 1:48 pm
Reply with quote

ankvardhan,

please learn to use the bbcode (link here)

Code:

//SYSIN DD *
 JOINKEYS FILES=F1,FIELDS=(1,2,A),SORTED
 JOINKEYS FILES=F2,FIELDS=(1,2,A),SORTED
 REFORMAT FIELDS=(F1:1,X,F2:1,Y)
 OPTION COPY
/*


without the use of the [ code ] [ /code ] tags, the TS would not know
to insure all the parameters contained in the SYSIN must start in col 2 (or greater)
with a space in col 1.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu Sep 16, 2010 3:53 pm
Reply with quote

Okay, for the given sample inputs try this:
Code:
//S1 EXEC PGM=SORT                         
//SYSOUT DD SYSOUT=*                       
//SORTJNF1 DD *                           
A1                                         
A1                                         
A2                                         
A2                                         
A3                                         
//SORTJNF2 DD *                           
A1                                         
A3                                         
B1                                         
//SORTOUT DD SYSOUT=*                     
//*                                       
//SYSIN DD *                               
  JOINKEYS FILES=F1,FIELDS=(1,2,A),SORTED 
  JOINKEYS FILES=F2,FIELDS=(1,2,A),SORTED 
  REFORMAT FIELDS=(F1:1,2)                 
  OPTION COPY                             
/*                                         
//*                                       
Ank -- Why F2 is mentioned in REFORMAT fields?
Back to top
View user's profile Send private message
xpower

New User


Joined: 07 May 2006
Posts: 35

PostPosted: Thu Sep 16, 2010 4:55 pm
Reply with quote

Thank you so much, Sir.

yes, i tried and it worked. by the way, how to display the recs not matched in different files. in other words, the recs not matched in file a shuold be displayed in file c, and the recs not matched in file b should be displayed in file d.

Best Regards
Back to top
View user's profile Send private message
ankvardhan

New User


Joined: 05 Sep 2008
Posts: 14
Location: Mumbai

PostPosted: Thu Sep 16, 2010 6:37 pm
Reply with quote

Anuj,
F2 is defined to merge, the matched records of both the files.
O/P is Like:
FileA FileB
A1 A1
A1 A1
A3 A3

From your JCL
O/P is like
A1
A1
A3

It means the same in both the cases, only difference is F2 includes the matched records of FileB also.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu Sep 16, 2010 7:07 pm
Reply with quote

xpower wrote:
Thank you so much, Sir.
I'm not Sir, I'm just another guy on the block. icon_smile.gif

Quote:
yes, i tried and it worked. by the way, how to display the recs not matched in different files. in other words, the recs not matched in file a shuold be displayed in file c, and the recs not matched in file b should be displayed in file d.
Try this:
Code:
//STEP1    EXEC PGM=SORT                     
//SORTOUT  DD SYSOUT=*                       
//SYSOUT   DD SYSOUT=*                       
//NOMATCH1 DD SYSOUT=*                       
//NOMATCH2 DD SYSOUT=*                       
//MATCH    DD SYSOUT=*                       
//SORTJNF1 DD *                               
A1                                           
A1                                           
A2                                           
A2                                           
A3                                           
//SORTJNF2 DD *                               
A1                                           
A3                                           
B1                                           
//SYSIN    DD *                               
  JOINKEYS FILE=F1,FIELDS=(1,2,A)             
  JOINKEYS FILE=F2,FIELDS=(1,2,A)             
  JOIN UNPAIRED                               
  REFORMAT FIELDS=(F1:1,10,F2:1,80),FILL=X'FF'   
  SORT FIELDS=COPY                               
  OUTFIL FNAMES=NOMATCH1,                       
         INCLUDE=(11,1,BI,EQ,X'FF'),             
         BUILD=(1,10)                           
  OUTFIL FNAMES=NOMATCH2,                       
         INCLUDE=(1,1,BI,EQ,X'FF'),             
         BUILD=(11,80)                           
  OUTFIL FNAMES=MATCH,SAVE,                     
         BUILD=(11,80)                           
/*                                               
//*                                             
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu Sep 16, 2010 7:11 pm
Reply with quote

I've just noticed, this thread is in DFSort part of the Forum, Please note there is a difference the way JOINKEYS syntax is allowed in SyncSort and DFsort and I can not check if this will work with DFSort or not as I'm not logged on to the LPAR uses DFSort. For example, DFSort would use F1=IN1 while what I post is SyncSort Syntax. But you said previous solution worked for you..so I leave it to you (and Frank, ofcourse).
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Thu Sep 16, 2010 8:11 pm
Reply with quote

xpower,
Try below job for DFSort. Its just another way to get the desired results. The solution Anuj provided earlier,gives the same result as well.

Below job uses DFSort's 1 byte join indicator('?') to determine which records match and which ones doesn't.

'B' - in the join indicator indicates that the key was found in F1 and F2.
'1' - in the join indicator indicates that the key was found in F1, but not in F2.
'2' - in the join indicator indicates that the key was found in F2, but not in F1.

Code:

//STEP01   EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTJNF1 DD *                                                         
A1                                                                     
A1                                                                     
A2                                                                     
A2                                                                     
A3                                                                     
//SORTJNF2 DD *                                                         
A1                                                                     
A3                                                                     
B1                                                                     
//NOMATCH1 DD SYSOUT=*                                                 
//NOMATCH2 DD SYSOUT=*                                                 
//MATCH    DD SYSOUT=*                                                 
//*                                                                     
//SYSIN    DD *                                                         
  JOINKEYS FILES=F1,FIELDS=(1,2,A)                             
  JOINKEYS FILES=F2,FIELDS=(1,2,A)
  JOIN UNPAIRED                                                         
  REFORMAT FIELDS=(F1:1,2,?,F2:1,2)                                     
  OPTION COPY                                                           
  OUTFIL FNAMES=NOMATCH1,INCLUDE=(3,1,CH,EQ,C'1'),BUILD=(1,2)           
  OUTFIL FNAMES=NOMATCH2,INCLUDE=(3,1,CH,EQ,C'2'),BUILD=(4,2)           
  OUTFIL FNAMES=MATCH,INCLUDE=(3,1,CH,EQ,C'B'),BUILD=(1,2)             
/*                                                                     
//*                                                                     



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

DFSORT Developer


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

PostPosted: Thu Sep 16, 2010 11:45 pm
Reply with quote

Quote:
For example, DFSort would use F1=IN1 while what I post is SyncSort Syntax.


DFSORT supports the syntax you used for JOINKEYS. It also has additional variations of the syntax for ease-of-use.

For complete details on DFSORT's JOINKEYS, see:

www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174
Back to top
View user's profile Send private message
xpower

New User


Joined: 07 May 2006
Posts: 35

PostPosted: Fri Sep 17, 2010 5:46 am
Reply with quote

Anuj Dhawan,

thank you for your idea. icon_smile.gif

sqlcode1,

Appreciate your help. It worked as well. icon_smile.gif

Frank,

Thank you for your link. very useful for me. icon_smile.gif

Best Regards
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Fri Sep 17, 2010 7:18 pm
Reply with quote

Thank you Frank, that's a good tip for the day. Hope I didn't bother you much...icon_smile.gif.

Have a good one,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Fri Sep 17, 2010 9:22 pm
Reply with quote

Anuj,

You didn't bother me at all.
Back to top
View user's profile Send private message
foolsbrain

New User


Joined: 15 Apr 2010
Posts: 11
Location: chennai

PostPosted: Fri Oct 15, 2010 9:34 pm
Reply with quote

hi, I tried the above code and it works fine for me.
In the above example, file1 contains A1 twice and file2 contains A1 only once. What should i do to get A1 only once (i.e same number of occurences as of file2) from file1?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Sat Oct 16, 2010 1:26 am
Reply with quote

It's not clear what you're asking for. You need to show a better example with the differrent variations you want to handle, and explain the complete rules for getting from input to output.
Back to top
View user's profile Send private message
foolsbrain

New User


Joined: 15 Apr 2010
Posts: 11
Location: chennai

PostPosted: Sat Oct 16, 2010 8:32 am
Reply with quote

File1 contains
A1
A1
B1
C1
A1
C1
C1
D1

File2 contains
A1
B1
C1
B1
C1

My requirement is to write the matched records from File1 and it should look like

A1 -> Should come only once, as A1 is present only once in file2
B1
C1
C1 -> should come only twice, because in file2 C1 is present only twice even though C1 is present thrice in file1.
Back to top
View user's profile Send private message
foolsbrain

New User


Joined: 15 Apr 2010
Posts: 11
Location: chennai

PostPosted: Tue Oct 19, 2010 7:30 pm
Reply with quote

so, the above request cannot be achieved?
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Tue Oct 19, 2010 8:30 pm
Reply with quote

Achived by you or someone else?

Have you tried?

I was waiting for you to try then help you with errors and issues.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Tue Oct 19, 2010 11:25 pm
Reply with quote

Quote:
so, the above request cannot be achieved?


I don't know if it can or not since I don't understand your explanation of what you want.

For example, you have B1 twice in File2, but only once in the output - why?

The rules may be clear to you, but they aren't clear to me.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Oct 19, 2010 11:33 pm
Reply with quote

Hi Frank,

If i understand, the output should contain only the records that are in both files.

If there is an unequal number of records with the same key, the "extras" should be discarded from either file.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Tue Oct 19, 2010 11:43 pm
Reply with quote

By those rules, shouldn't D1 be in the output? It isn't. I think file2 is the controlling file, but I don't understand exactly how. The example shown doesn't even indicate which input file the output records come from so it's very confusing. We shouldn't have to guess what the OP wants.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Oct 19, 2010 11:56 pm
Reply with quote

Hi Frank,

I believe D1 is excluded because it has no match.

Quote:
We shouldn't have to guess what the OP wants.
For sure . . .

I'm not sure if only the "keys" are to be written or if there even is addtional data in a record. . . icon_confused.gif

d
Back to top
View user's profile Send private message
foolsbrain

New User


Joined: 15 Apr 2010
Posts: 11
Location: chennai

PostPosted: Fri Oct 22, 2010 7:12 pm
Reply with quote

Hi Dick,
You are right, the output should contain only the records that are in both files. D1 is rejected because it is not present in File2.

Need to write the O/P using File1 records.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Oct 22, 2010 8:17 pm
Reply with quote

Hello,

Quote:
Need to write the O/P using File1 records.
How is the determination made as to which File1 records to keep and which to discard when there is an unequal number in File2?
Back to top
View user's profile Send private message
foolsbrain

New User


Joined: 15 Apr 2010
Posts: 11
Location: chennai

PostPosted: Fri Oct 22, 2010 8:28 pm
Reply with quote

Since we are only matching the key fields of File1 with File2, we can keep any of the matching records.

For example, if A1 is present 5 times in File1 and 3 times in File2, in the output we only need A1 thrice, we can discard any two A1 records.
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 Goto page 1, 2  Next

 


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 Access to non cataloged VSAM file JCL & VSAM 18
No new posts Compare only first records of the fil... SYNCSORT 7
Search our Forums:

Back to Top