|
View previous topic :: View next topic
|
| Author |
Message |
Shweta12j
New User
Joined: 10 May 2010
Posts: 32
Location: Mumbai
|
|
|
|
Dear All ,
I am facing an issue with Join keys mentioned below.
I have 2 files with me. RECFM of both the files is FB adn LRECL is 57.
File01 content :
| Code: |
10018KO0000156435670001000000000469
10012KO0000162667162001000000000647
10040KO0000279912830001000000000353
10040KO0000279912875001000000000450
10040KO0000279912883001000000000469
10040KO0000279912903001000000000825
10040KO0000279912911001000000000892
10040KO0000279932440001000000000728
10013KO0000280936031001000000000612
10040KO0000280936574001000000000035 |
File02 content :
| Code: |
10004KO0000131926500002000000000035
10021KO0000162667162002000000000094
10004KO0000239155685002000000000043
10016KO0000258758008002000000000043
10021KO0000279912883002000000000094
10021KO0000280935517004000000000094
10021KO0000280935517004000000000086
10006KO0000280936760002000000000094
10006KO0000280936760002000000000035
10006KO0000280936825002000000000035 |
My requirement is to create 3 output files.
1) File01 records which donot have match in File02
2) File02 records which donot have match in File01
3) Matched content based on joinkey (6,15,CH,A)
Sort Card which I used :
| Code: |
JOINKEYS FILE=F1,FIELDS=(6,15,CH,A)
JOINKEYS FILE=F2,FIELDS=(6,15,CH,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,57,F2:1,57)
OPTION COPY
OUTFIL FNAMES=SORTOF01,INCLUDE=(60,1,CH,EQ,C'1'),
BUILD=(1,57)
OUTFIL FNAMES=SORTOF02,INCLUDE=(60,1,CH,EQ,C'2'),BUILD=(1,57)
OUTFIL FNAMES=SORTOF03,BUILD=(1,57,/,1,57) |
However I'm getting incorrect output.
Regards,
Shweta. |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Shewta,
A quick question
Are you using SYNCSORT or DFSORT?? |
|
| Back to top |
|
|
Shweta12j
New User
Joined: 10 May 2010
Posts: 32
Location: Mumbai
|
|
|
|
Hello ,
I am using Syncsort .
Regards,
Shweta |
|
| Back to top |
|
|
Naish
New User
Joined: 07 Dec 2006
Posts: 82
Location: UK
|
|
|
|
Suggest you look HERE for 1-byte indicator.
Hope that helps. |
|
| Back to top |
|
|
Shweta12j
New User
Joined: 10 May 2010
Posts: 32
Location: Mumbai
|
|
|
|
Hi Naish ,
I did refer this document however couldn't solve this issue.
Regards,
Shweta. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
For Syncsort (please post in the correct forum next time) you have to rely on the absence of data to identify the mismatches.
So, pick a field which can't have the default FILL value, test for that value.
| Code: |
FILL DATA
DATA FILL
DATA DATA |
If field in first file is FILL, output second file.
If field in second file is FILL, output first file.
If neither of the above, output two records to third file. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
First output file
Check first 57 ne Space and next 57 bytes Eq Space
Second output file
check opposite of previous check
Third file
Add SAVE before build in your outfil |
|
| Back to top |
|
|
Naish
New User
Joined: 07 Dec 2006
Posts: 82
Location: UK
|
|
|
|
Shweta,
Apologies for the confusion. I was oblivious of SYNCSORT features until Bill's post. I hope you flush out what I suggested.
Thanks Bill. |
|
| Back to top |
|
|
Shweta12j
New User
Joined: 10 May 2010
Posts: 32
Location: Mumbai
|
|
|
|
Hello ,
Could you please elaborate on what is wrong in sort card.
I am a new user on joinkeys hence little more information would be of great help.
Regards,
Shweta. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
It will probably help if you post the complete jobstep (JCL and sort control statements). |
|
| Back to top |
|
|
Shweta12j
New User
Joined: 10 May 2010
Posts: 32
Location: Mumbai
|
|
|
|
Hi Dick ,
Please find the JCL below :
| Code: |
//STEP01 EXEC PGM=SORT
//SORTJNF1 DD DSN=XB99.PRIM.MATCH.OUT.NEW1,
// DISP=SHR
//SORTJNF2 DD DSN=XB99.SEC.MATCH.OUT.NEW1,
// DISP=SHR
//SORTOF01 DD DSN=XB99.SORT01.SS036.OUT(+1),
// DISP=(NEW,CATLG,DELETE),
// DATACLAS=DCALNXL
//SORTOF02 DD DSN=XB99.SORT02.SS036.OUT(+1),
// DISP=(NEW,CATLG,DELETE),
// DATACLAS=DCALNXL
//SORTOF03 DD DSN=XB99.SORT03.SS036.OUT(+1),
// DISP=(NEW,CATLG,DELETE),
// DATACLAS=DCALNXL
//SYSIN DD DSN=XB99.CTNPCD.PGMCCD(SSS36I),
// DISP=SHR
|
SSS36I here refers to below content :
| Code: |
JOINKEYS FILE=F1,FIELDS=(6,15,CH,A)
JOINKEYS FILE=F2,FIELDS=(6,15,CH,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,57,F2:1,57)
OPTION COPY
OUTFIL FNAMES=SORTOF01,INCLUDE=(60,1,CH,EQ,C'1'),
BUILD=(1,57)
OUTFIL FNAMES=SORTOF02,INCLUDE=(60,1,CH,EQ,C'2'),BUILD=(1,57)
OUTFIL FNAMES=SORTOF03,BUILD=(1,57,/,1,57)
|
Regards,
Shweta. |
|
| Back to top |
|
|
Naish
New User
Joined: 07 Dec 2006
Posts: 82
Location: UK
|
|
|
|
Hello Shweta.
1. Did you try Bill's suggestion/solution?
2. You mentioned you got incorrect output. Please post what output you got. Also, what is inside SORTJNF1 and SORTJNF2?
3. Suggest (I hope not to mess up this time) you read the manual (since you mentioned you are new to joinkeys). You will get a clear understanding of the constructs/syntax/keywords/options used and I am sure you can then deduce what you want. |
|
| Back to top |
|
|
Shweta12j
New User
Joined: 10 May 2010
Posts: 32
Location: Mumbai
|
|
|
|
Dear Niash ,
I have read the manual however still I am getting an issue.
Below is a complete jcl with respective input and output.
Input File content :
File Name : XB99.PRIM.MATCH.OUT.NEW1
| Code: |
10018KO0000156435670001000000000469
10012KO0000162667162001000000000647
10040KO0000279912830001000000000353
10040KO0000279912875001000000000450
10040KO0000279912883001000000000469
10040KO0000279912903001000000000825
10040KO0000279912911001000000000892
10040KO0000279932440001000000000728
10013KO0000280936031001000000000612
10040KO0000280936574001000000000035
|
File Name : XB99.SEC.MATCH.OUT.NEW1
| Code: |
10004KO0000131926500002000000000035
10021KO0000162667162002000000000094
10004KO0000239155685002000000000043
10016KO0000258758008002000000000043
10021KO0000279912883002000000000094
10021KO0000280935517004000000000094
10021KO0000280935517004000000000086
10006KO0000280936760002000000000094
10006KO0000280936760002000000000035
10006KO0000280936825002000000000035
|
JCL :
| Code: |
//STEP01 EXEC PGM=SORT
//SORTJNF1 DD DSN=XB99.PRIM.MATCH.OUT.NEW1,
// DISP=SHR
//SORTJNF2 DD DSN=XB99.SEC.MATCH.OUT.NEW1,
// DISP=SHR
//SORTOF01 DD DSN=XB99.SORT01.SS036.OUT(+1),
// DISP=(NEW,CATLG,DELETE),
// DATACLAS=DCALNXL
//SORTOF02 DD DSN=XB99.SORT02.SS036.OUT(+1),
// DISP=(NEW,CATLG,DELETE),
// DATACLAS=DCALNXL
//SORTOF03 DD DSN=XB99.SORT03.SS036.OUT(+1),
// DISP=(NEW,CATLG,DELETE),
// DATACLAS=DCALNXL
//SYSIN DD DSN=XB99.CTNPCD.PGMCCD(SSS36I),
// DISP=SHR
//SORTWK01 DD DATACLAS=DCALNXL
//SORTWK02 DD DATACLAS=DCALNXL
//SORTWK03 DD DATACLAS=DCALNXL
//SORTWK04 DD DATACLAS=DCALNXL
//SORTWK05 DD DATACLAS=DCALNXL
|
Sort Card Content :
| Code: |
JOINKEYS FILE=F1,FIELDS=(6,15,CH,A)
JOINKEYS FILE=F2,FIELDS=(6,15,CH,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,57,F2:1,57)
OPTION COPY
OUTFIL FNAMES=SORTOF01,INCLUDE=(60,1,CH,EQ,C'1'),
BUILD=(1,57)
OUTFIL FNAMES=SORTOF02,INCLUDE=(60,1,CH,EQ,C'2'),BUILD=(1,57)
OUTFIL FNAMES=SORTOF03,BUILD=(1,57,/,1,57)
|
Result :
XB99.SORT01.SS036.OUT(0) - Empty datasets
XB99.SORT02.SS036.OUT(0) - Empty datasets
File Name : XB99.SORT03.SS036.OUT(0)
| Code: |
10018KO0000156435670001000000000469
10018KO0000156435670001000000000469
10012KO0000162667162001000000000647
10012KO0000162667162001000000000647
10040KO0000279912830001000000000353
10040KO0000279912830001000000000353
10040KO0000279912875001000000000450
10040KO0000279912875001000000000450
10040KO0000279912883001000000000469
10040KO0000279912883001000000000469
10040KO0000279912903001000000000825
10040KO0000279912903001000000000825
10040KO0000279912911001000000000892
10040KO0000279912911001000000000892
10040KO0000279932440001000000000728
10040KO0000279932440001000000000728
|
Desired Output :
My requirement is to create 3 output files.
1) File01 records which donot have match in File02
2) File02 records which donot have match in File01
3) Matched content based on joinkey (6,15,CH,A)
Please let me know in case you need information from my end.
Regards,
Shweta. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Shweta,
For the given input 60,1 is always 0
Two of output is empty because the Include condition for SORTOF01 & SORTOF02 doesnt seem to work
Also suggest you to revisit how reformattung works
To understand clearly what happens after Join unpaired please try to remove the statements after sort fieldd and execute by addin sortout
Then think about the data you need in three files :-)
Trust me this will help you well |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
You are using one byte at position 60 in the REFORMAT record to decide on file 1/2, and you are using a particular value for that position.
If you have an unmatched file 1, then the whole 1,57 for file two will be space (or binary zeros, or whatever, depending on your installation).
If you have unmatched file 2, then the whole 1,57 for file one will be, as above.
If you have a match, both 1,57s will have data.
If you have a particular byte which cannot contain the FILL value used for unmatched records on the JOINKEYS then you can test that byte. At worst you can test the whole record as Pandora-box has already suggested.
There are examples in the JCL forum. Search for JOINKEYS and FILL. |
|
| Back to top |
|
|
bhavin.mehta
New User
Joined: 25 Jun 2012
Posts: 34
Location: India
|
|
|
|
@Shweta12j:
I assume you had copied the DFSORT syntax to get the 3 outputs and you must have used similar card before :-
JOINKEYS FILE=F1,FIELDS=(6,15,CH,A)
JOINKEYS FILE=F2,FIELDS=(6,15,CH,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,57,F2:1,57,?)
OPTION COPY
Here you would have faced SYNTAX error, as "?" in the SORT card is not supported by SYNCSORT. Please understand all the DFSORT syntax wouldnt work for SYNCSORT |
|
| Back to top |
|
|
bhavin.mehta
New User
Joined: 25 Jun 2012
Posts: 34
Location: India
|
|
|
|
As per my experience in SYNCSORT I don't think you can get all the 3 outputs using 1 Sortcard. You will have to use 3 Sortcards (i.e. you will require 3 steps) 1 for each file.
To get F1 unmatched records
JOINKEYS FILES=F1,FIELDS=(6,15,A)
JOINKEYS FILES=F2,FIELDS=(6,15,A)
JOIN UNPAIRED,F1,ONLY
To get F2 unmatched records
JOINKEYS FILES=F1,FIELDS=(6,15,A)
JOINKEYS FILES=F2,FIELDS=(6,15,A)
JOIN UNPAIRED,F2,ONLY
And 3rd 1 without the UNPAIRED to get the match records. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Bhavin,
Appreciate your enthusiasm
But please revisit your understanding
FYI
JOIN UNPAIRED F1,F2 IS LIKE FULL OUTER JOIN of SQL
You dont need three sort cards |
|
| Back to top |
|
|
Naish
New User
Joined: 07 Dec 2006
Posts: 82
Location: UK
|
|
|
|
Hello bhavin.mehta,
Did you even read earlier replies/posts by other members? |
|
| Back to top |
|
|
bhavin.mehta
New User
Joined: 25 Jun 2012
Posts: 34
Location: India
|
|
|
|
Pandora, I agree ou have better understanding than me. But I'm suggesting as I had faced the same issue before.
I agree that JOIN UNPAIRED F1,F2 IS LIKE FULL OUTER JOIN of SQL and in DFSORT you have the ability to identify the
1. Unmatched F1 records
2. Unmatched F2 records and
3. F1 F2 matched records
with just 1 sortcard with the help of '?' in it, which would be the last byte in your REFORMAT record. But this kind of functionality is not available SYNCSORT. The DFSORT example is explained here
publib.boulder.ibm.com/infocenter/zos/v1r12/index.jsp?topic=%2Fcom.ibm.zos.r12.icea100%2Fice1ca50119.htm
Underthis goto -> JOINKEYS Application Examples -> Example5
And if you see, Shweta has NOT used '?' in her sortcard, while she is trying to replicate the same as DFSORT!!
@Naish: I did read the previos posts, but I didn't get anything which would backfire me. Can you direct me please. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| So, what you missed, is the possibility to do in Syncsort testing for the FILL character. |
|
| Back to top |
|
|
Naish
New User
Joined: 07 Dec 2006
Posts: 82
Location: UK
|
|
|
|
To quote a couple:
| Pandora-Box wrote: |
First output file
Check first 57 ne Space and next 57 bytes Eq Space
Second input file
check opposite of previous check
Third file
Add SAVE before build in your outfil |
| Bill Woodger wrote: |
You are using one byte at position 60 in the REFORMAT record to decide on file 1/2, and you are using a particular value for that position.
If you have an unmatched file 1, then the whole 1,57 for file two will be space (or binary zeros, or whatever, depending on your installation).
If you have unmatched file 2, then the whole 1,57 for file one will be, as above.
If you have a match, both 1,57s will have data.
If you have a particular byte which cannot contain the FILL value used for unmatched records on the JOINKEYS then you can test that byte. At worst you can test the whole record as Pandora-box has already suggested.
There are examples in the JCL forum. Search for JOINKEYS and FILL. |
bhavin.mehta,
Nobody is 'backfiring' you. But, the previous posts implied what you mentioned in your post. So as Bill mentioned you didn't test (possibly) for FILL character in Syncsort. |
|
| Back to top |
|
|
bhavin.mehta
New User
Joined: 25 Jun 2012
Posts: 34
Location: India
|
|
|
|
| Apologies. |
|
| Back to top |
|
|
bodatrinadh
Active User
Joined: 05 Jan 2007
Posts: 101
Location: chennai (India)
|
|
|
|
| Quote: |
As per my experience in SYNCSORT I don't think you can get all the 3 outputs using 1 Sortcard. You will have to use 3 Sortcards (i.e. you will require 3 steps) 1 for each file.
|
@bhavin : It can be achived in single sort step.
Here is the JCL:-
| Code: |
//STEP1 EXEC PGM=SORT
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//UNMATF3 DD SYSOUT=*
//UNMATF2 DD SYSOUT=*
//MATCHF1 DD SYSOUT=*
//SORTJNF1 DD *
10018KO0000156435670001000000000469
10012KO0000162667162001000000000647
10040KO0000279912830001000000000353
10040KO0000279912875001000000000450
10040KO0000279912883001000000000469
10040KO0000279912903001000000000825
10040KO0000279912911001000000000892
10040KO0000279932440001000000000728
10013KO0000280936031001000000000612
10040KO0000280936574001000000000035
//SORTJNF2 DD *
10004KO0000131926500002000000000035
10021KO0000162667162002000000000094
10004KO0000239155685002000000000043
10016KO0000258758008002000000000043
10021KO0000279912883002000000000094
10021KO0000280935517004000000000094
10021KO0000280935517004000000000086
10006KO0000280936760002000000000094
10006KO0000280936760002000000000035
10006KO0000280936825002000000000035
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(6,15,A)
JOINKEYS FILES=F2,FIELDS=(6,15,A)
SORT FIELDS=COPY
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,57,F2:1,57)
OUTFIL FNAMES=MATCHF1,INCLUDE=(6,1,CH,NE,C' ',AND,63,1,CH,NE,C' '),
OUTREC=(1,57)
OUTFIL FNAMES=UNMATF2,INCLUDE=(6,1,CH,NE,C' ',AND,63,1,CH,EQ,C' '),
OUTREC=(1,57)
OUTFIL FNAMES=UNMATF3,SAVE,OUTREC=(58,57)
|
Output file contians:-
3) Matched content based on joinkey (6,15,CH,A).
| Code: |
10012KO0000162667162001000000000647
10040KO0000279912883001000000000469
|
2) File02 records which donot have match in File01
UNMATF3:-
| Code: |
10018KO0000156435670001000000000469
10040KO0000279912830001000000000353
10040KO0000279912875001000000000450
10040KO0000279912903001000000000825
10040KO0000279912911001000000000892
10040KO0000279932440001000000000728
10013KO0000280936031001000000000612
10040KO0000280936574001000000000035
|
1) File01 records which donot have match in File02
UNMATF2:-
| Code: |
10004KO0000131926500002000000000035
10004KO0000239155685002000000000043
10016KO0000258758008002000000000043
10021KO0000280935517004000000000094
10021KO0000280935517004000000000086
10006KO0000280936760002000000000094
10006KO0000280936760002000000000035
10006KO0000280936825002000000000035
|
Thanks
-3nadh |
|
| Back to top |
|
|
bhavin.mehta
New User
Joined: 25 Jun 2012
Posts: 34
Location: India
|
|
|
|
| @bodatrinadh: I tried it too. It was successful. Had a lot of learning today. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
