|
View previous topic :: View next topic
|
| Author |
Message |
maki_psg
New User
Joined: 28 Jan 2010
Posts: 47
Location: India
|
|
|
|
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 |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Are you using SYNCSORT OR DFSORT? |
|
| Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
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 |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
Thanks Skolusu and Bill..
I guess OP has forgotten that he/she posted something here :-) |
|
| Back to top |
|
|
maki_psg
New User
Joined: 28 Jan 2010
Posts: 47
Location: India
|
|
|
|
| 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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
| 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.  |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| 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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
maki_psg
New User
Joined: 28 Jan 2010
Posts: 47
Location: India
|
|
|
|
| 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  .
Yes, "?" works in the control cards which were posted at the top. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
OK. Thanks.
So, with at least 1.4.1.0R, SyncSorters can use the ? in the REFORMAT statement :-) |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
| 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  , Not sure why the 1.4 documentation does not mention about this enhancement.  |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| SyncSort drop in from time-to-time. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Would be pretty happy if get a chair to sit near those experts and influence my knowledge 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
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
| 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. 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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| 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 |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
