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

Extract matching and non matching recs using SORT


IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
maki_psg

New User


Joined: 28 Jan 2010
Posts: 47
Location: India

PostPosted: Mon Jun 10, 2013 9:00 am
Reply with quote

Hi,

I have a requirement to extract the data from 2 files with matching keys from one file (File 2) and non matching keys from both the files (File 1 and File 2).

Data from file 1:

Code:

A 101
C <spaces>
B 102
E 105
D 104


Data from File 2:

Code:

P 301
A 200
Q 302
B 101
D 109
Z 901


I would like to extract the rec from File 2 if there is a match on Col1 and also extract non matching recs from both the files.

Sample output file:
Code:

A 200
B 101
C <spaces>
D 109
E 105
P 301
Q 302
Z 901


Please help to extract the above output using SORT.
Thanks.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Mon Jun 10, 2013 10:16 am
Reply with quote

maki_psg,

I would suggest "Search"ing the DFSORT forum , I am sure you will find a lot of working examples for your requirement.

Try something out and get back if you face any issues with that. Someone will be around to help you.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Mon Jun 10, 2013 11:05 am
Reply with quote

Are you using SYNCSORT OR DFSORT?
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Mon Jun 10, 2013 11:20 am
Reply with quote

Assuming both your files are FB and LRECL=80..and matching key is the first column in both the files F1 and F2..
you can try this DFSORT job.
Code:

//SRT01    EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTJNF1 DD *                                                     
A 101                                                               
C <SPACES>                                                           
B 102                                                               
E 105                                                               
D 104                                                               
//SORTJNF2 DD *                                                     
P 301                                                               
A 200                                                               
Q 302                                                               
B 101                                                               
D 109                                                               
Z 901                                                               
//*                                                                 
//SORTOUT  DD SYSOUT=*                                               
//*                                                                 
//SYSIN    DD *                                           
  JOINKEYS FILE=F1,FIELDS=(1,1,A)                         
  JOINKEYS FILE=F2,FIELDS=(1,1,A)                         
  JOIN     UNPAIRED,F1,F2                                 
  OPTION   COPY                                           
  REFORMAT FIELDS=(F1:1,80,F2:1,80,?)                     
  INREC    IFTHEN=(WHEN=(161,1,CH,EQ,C'1'),BUILD=(1,80)), 
           IFTHEN=(WHEN=(161,1,SS,EQ,C'B,2'),BUILD=(81,80))


Output:
Code:

A 200     
B 101     
C <SPACES>
D 109     
E 105     
P 301     
Q 302     
Z 901     
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Mon Jun 10, 2013 11:34 am
Reply with quote

mistah kurtz,

The matching marker can only have one of three values. IFTHEN=(WHEN=NONE will give you an "ELSE", which will make it clearer, rather than have the next person along wondering what values are not being catered for.

maki_psg,

Are your input files in sequence on the first byte? Are duplicates possible on either file?

Your volumes must be small, so you'd probably not notice, but you might want to look at using MERGE.

Specify your second file as the first input to the MERGE and use OPTION EQUAL and SUM FIELDS=NONE.

EDIT: I can see from your sample data that your files are not in sequence. Forget MERGE.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Mon Jun 10, 2013 1:06 pm
Reply with quote

Thanks Bill.. I have modified it as below:

Code:
//SYSIN    DD *                                         
  JOINKEYS FILE=F1,FIELDS=(1,1,A)                       
  JOINKEYS FILE=F2,FIELDS=(1,1,A)                       
  JOIN     UNPAIRED,F1,F2                               
  OPTION   COPY                                         
  REFORMAT FIELDS=(F1:1,80,F2:1,80,?)                   
  INREC    IFTHEN=(WHEN=(161,1,CH,EQ,C'1'),BUILD=(1,80)),
           IFTHEN=(WHEN=NONE,BUILD=(81,80))             
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Mon Jun 10, 2013 10:13 pm
Reply with quote

mistah kurtz,

Good Job. I would like to tidy up a little bit more.

You don't have to specify F1 and F2 on the JOIN statement. A simple JOIN UNPAIRED will give you matched as well unmatched records from both F1 and F2.

You also don't need to build the record once again when the indicator is '1' as the data is already present in positions 1 thru 80. You can use IFOUTLEN=80, to make sure we got the right LRECL for output.

so use the following control cards
Code:

//SYSIN    DD *                                                     
  JOINKEYS FILE=F1,FIELDS=(1,1,A)                                   
  JOINKEYS FILE=F2,FIELDS=(1,1,A)                                   
  JOIN UNPAIRED                                                     
  OPTION COPY                                                       
  REFORMAT FIELDS=(F1:1,80,F2:1,80,?)                               
  INREC IFOUTLEN=80,IFTHEN=(WHEN=(161,1,SS,EQ,C'B,2'),BUILD=(81,80))
//*
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jun 11, 2013 2:21 am
Reply with quote

If the volumes were large, there might be advantages to rearranging things.

If most of the records were "matches" with only a few "mismatches" then F1 and F2 could be "swapped", so that most of the input data required for output are in the first part of the REFORMAT record, and processed simply by the IFOUTLEN. This goes for whatever combination gives the preponderance of records. If should be the first part of the REFORMAT record.

I know there is a thing about "negation", but with only three possibilities, NE,C'1' might do :-)

Particularly with symbols...
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Tue Jun 11, 2013 9:58 am
Reply with quote

Thanks Skolusu and Bill..
I guess OP has forgotten that he/she posted something here :-)
Back to top
View user's profile Send private message
maki_psg

New User


Joined: 28 Jan 2010
Posts: 47
Location: India

PostPosted: Tue Jun 11, 2013 10:40 am
Reply with quote

Skolusu wrote:
mistah kurtz,

Good Job. I would like to tidy up a little bit more.

You don't have to specify F1 and F2 on the JOIN statement. A simple JOIN UNPAIRED will give you matched as well unmatched records from both F1 and F2.

You also don't need to build the record once again when the indicator is '1' as the data is already present in positions 1 thru 80. You can use IFOUTLEN=80, to make sure we got the right LRECL for output.

so use the following control cards
Code:

//SYSIN    DD *                                                     
  JOINKEYS FILE=F1,FIELDS=(1,1,A)                                   
  JOINKEYS FILE=F2,FIELDS=(1,1,A)                                   
  JOIN UNPAIRED                                                     
  OPTION COPY                                                       
  REFORMAT FIELDS=(F1:1,80,F2:1,80,?)                               
  INREC IFOUTLEN=80,IFTHEN=(WHEN=(161,1,SS,EQ,C'B,2'),BUILD=(81,80))
//*


@ Pandora, We are using SYNCSORT.

@ Skolusu, the input file volume is huge. I had posted a sample data, which was similar to the prod scenario.

Thank you mistah kurtz, Skolusu and Bill.

I have used the below sort card and it works perfectly.
Code:

//SYSIN    DD *                                                     
  JOINKEYS FILE=F1,FIELDS=(1,1,A)                                   
  JOINKEYS FILE=F2,FIELDS=(1,1,A)                                   
  JOIN UNPAIRED                                                     
  OPTION COPY                                                       
  REFORMAT FIELDS=(F1:1,80,F2:1,80,?)                               
  INREC IFOUTLEN=80,IFTHEN=(WHEN=(161,1,SS,EQ,C'B,2'),BUILD=(81,80))
//*                                                                 
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Tue Jun 11, 2013 10:53 am
Reply with quote

maki_psg wrote:
@ Pandora, We are using SYNCSORT.
maki_psg

Please post queries related to Syncsort in the JCL part of this forum in future. All the DFSORT keywords may not work well in Syncsort and vice versa, though for the most part, both will work the same way.

btw, Could you tell your Syncsort version, my manual does not say anything about the '?' support. icon_confused.gif
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Tue Jun 11, 2013 11:23 am
Reply with quote

Code:
//S1 EXEC PGM=ICEMAN
//SYSOUT   DD SYSOUT=*
//SORTIN DD *
RECORD
/*
//SORTOUT DD DUMMY
//SYSIN   DD   *
  OPTION COPY
/*



Please paste the complete sysout of the above step
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jun 11, 2013 11:53 am
Reply with quote

1.4.1.0 has undocumented support for JNFnCNTL files. Perhaps it also has for "?"? Either that or, unusually, TS/OP thinks they have SyncSort but don't....

maki_psg,

If your data is huge, pay attention to the points already mentioned. Mock-up your huge amounts, see if you get a reasonable difference in resources used, and choose the option which best fits your data.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jun 11, 2013 12:21 pm
Reply with quote

maki_psg,

You have a previous topic, from early April, which you also posted in DFSORT.

Please do not do that again. SyncSort questions go in the JCL part of the forum.

From that topic you say "I am currently using SYNCSORT FOR Z/OS 1.4.1.0R." Can you confirm that the "?" works in the Control Cards that you are using, please?
Back to top
View user's profile Send private message
maki_psg

New User


Joined: 28 Jan 2010
Posts: 47
Location: India

PostPosted: Tue Jun 11, 2013 1:01 pm
Reply with quote

Bill Woodger wrote:
maki_psg,

You have a previous topic, from early April, which you also posted in DFSORT.

Please do not do that again. SyncSort questions go in the JCL part of the forum.

From that topic you say "I am currently using SYNCSORT FOR Z/OS 1.4.1.0R." Can you confirm that the "?" works in the Control Cards that you are using, please?


Bill, apologies for posting the topic incorrectly icon_sad.gif.

Yes, "?" works in the control cards which were posted at the top.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jun 11, 2013 1:05 pm
Reply with quote

OK. Thanks.

So, with at least 1.4.1.0R, SyncSorters can use the ? in the REFORMAT statement :-)
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Thu Jun 13, 2013 11:09 am
Reply with quote

Bill Woodger wrote:
OK. Thanks.

So, with at least 1.4.1.0R, SyncSorters can use the ? in the REFORMAT statement :-)
Thats good news, Bill icon_smile.gif , Not sure why the 1.4 documentation does not mention about this enhancement. icon_eek.gif
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Thu Jun 13, 2013 12:08 pm
Reply with quote

Is there no SYNCSORT Developer in forum thats sad :'(

A person to share us new logic and commands of the latest version will come in handy for all users
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Jun 13, 2013 12:42 pm
Reply with quote

SyncSort drop in from time-to-time.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Thu Jun 13, 2013 7:05 pm
Reply with quote

Hello,

If your organization is registered with Syncsort, they send informational e-mails with some regularity . . . Make sure that whoever recieves them sends them to those who use Syncsort.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


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

PostPosted: Thu Jun 27, 2013 7:18 pm
Reply with quote

Pandora-Box wrote:
Is there no SYNCSORT Developer in forum thats sad :'(
Alissa is there but as Bill said, she logs-in from time-to-time.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Thu Jun 27, 2013 8:25 pm
Reply with quote

Hello,

Quote:
Not sure why the 1.4 documentation does not mention about this enhancement


It is in my 1.4 doc ~

Quote:
The join facility, controlled by the JOINKEYS, JOIN, and REFORMAT control statements, joins records from two source files
.
.
.
Specification of the placement of the data fields within the record created by the join operation is provided through the REFORMAT control statement
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Thu Jun 27, 2013 8:36 pm
Reply with quote

Would be pretty happy if get a chair to sit near those experts and influence my knowledge icon_smile.gif so that I could test & answer SYNCSORT queries like Arul does

But attimes I wonder how person could know so much things so that he writes a big manual icon_eek.gif

Yes Alissa is helpful once I mailed her when I was using SYNCSORT in my shop she very immediately helped with manuals

Ofcourse all should be providing those info which she asks for
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Thu Jun 27, 2013 9:22 pm
Reply with quote

Quote:
It is in my 1.4 doc ~
Hi dick,

I am afraid my 1.4 manual does not have the "?" as well as "JNFnCNTL" support details. icon_confused.gif I had sent an email to Syncsort support asking for any documentation and they responded saying that a ZAP is available to add these enhancements to even 1.3.2, but nothing on the documentation part.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Jun 27, 2013 10:33 pm
Reply with quote

Unfortunately, the thing about "undocumented features" is that they don't appear in the manual. I'd seen a couple of references elsewhere so asked Alissa about the JNFnCNTLs. I didn't know about the ?, and don't know if it is in the "patch", or only available above 1.4.0.
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 -> SYNCSORT Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts JCL sort to compare dates in two file... DFSORT/ICETOOL 2
No new posts Is this possible via sort (in one pass)? SYNCSORT 4
No new posts GDG generation name to GDG Base name ... DFSORT/ICETOOL 3
No new posts SORT on detail record, then repeat he... DFSORT/ICETOOL 3
No new posts FB to .CSV conversion using sort DFSORT/ICETOOL 7
Search our Forums:

Back to Top