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
 

 

Syncsort -display recs in file A,which are matched in File B
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
xpower

New User


Joined: 07 May 2006
Posts: 35

PostPosted: Thu Sep 16, 2010 12:19 pm    Post subject: Syncsort -display recs in file A,which are matched in File B
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    Post subject: i want to the results are: that is the recs in file a,which
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    Post subject:
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

Senior Member


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

PostPosted: Thu Sep 16, 2010 3:53 pm    Post subject:
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    Post subject: Reply to: how to display recs in file A,which are matched in
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    Post subject: Ank -- Why F2 is mentioned in REFORMAT fields?
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

Senior Member


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

PostPosted: Thu Sep 16, 2010 7:07 pm    Post subject: Re: Reply to: how to display recs in file A,which are matche
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

Senior Member


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

PostPosted: Thu Sep 16, 2010 7:11 pm    Post subject:
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: 578
Location: USA

PostPosted: Thu Sep 16, 2010 8:11 pm    Post subject:
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 Moderator


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

PostPosted: Thu Sep 16, 2010 11:45 pm    Post subject:
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:

http://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    Post subject:
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

Senior Member


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

PostPosted: Fri Sep 17, 2010 7:18 pm    Post subject:
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 Moderator


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

PostPosted: Fri Sep 17, 2010 9:22 pm    Post subject:
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    Post subject:
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 Moderator


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

PostPosted: Sat Oct 16, 2010 1:26 am    Post subject:
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    Post subject:
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    Post subject:
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: 645
Location: Pennsylvania

PostPosted: Tue Oct 19, 2010 8:30 pm    Post subject:
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 Moderator


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

PostPosted: Tue Oct 19, 2010 11:25 pm    Post subject:
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

Site Director


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

PostPosted: Tue Oct 19, 2010 11:33 pm    Post subject:
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
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 1 Fri Dec 02, 2016 9:47 am
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm


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