Joined: 10 Dec 2010 Posts: 96 Location: Massachusetts
I have been using this Joinkey process with just one key field for quite some time now with grade a results. I have 2 files of lrecl=55. A Prior file and a Current file. These files are reformatted to a 3rd party layout before they enter the Joinkey step and have 3 fields that make up the key on which i wish to Join.
Current file looks like this :
When the Joinkey step is executed the JES Spool starts filling up with the SORTOUT sysout up to about 5M before i have to cancel the darn thing.
Here is the JES job sysout:
Code:
PREFIX=Z3A3E* DEST=(ALL) OWNER=* SORT=StepName/A SYSNAME=
NP DDNAME StepName ProcStep DSID Owner C Dest Rec-Cnt
JESJCLIN 1 Z3A3E N 81
$JOURNAL 6 Z3A3E A 506
101 Z3A3E N 1
102 Z3A3E N 23
103 Z3A3E N 1
104 Z3A3E N 23
JESMSGLG JES2 2 Z3A3E N 4
JESJCL JES2 3 Z3A3E N 111
JESYSMSG JES2 4 Z3A3E N 2
$INTTEXT JES2 5 Z3A3E A 53
SYSIN PINKEYED 105 Z3A3E N 14
F2ONLY PINKEYED 115 Z3A3E N 0
SYSOUT PINKEYED 118 Z3A3E N 0
SORTOUT PINKEYED 119 Z3A3E N 1M
JNF1JMSG PINKEYED 120 Z3A3E N 0
JNF2JMSG PINKEYED 121 Z3A3E N 0
the SORTOUT builds multiple records for every one record i have in my 2 datasets being joined.
SORTOUT looks like this with only 80 bytes shown with the start of the second joined record included:
madmartinsonxx,
It creating cross product of both the input file (many to many join). Do you really need SORTOUT? Even if you do need, why are you routing that to SYSOUT? Here SORTOUT is nothing but your entire joined record.
Looking at your sortcard, looks like you are creating F1ONLY,F2ONLY and BOTH files already, and you don't SORTOUT at all (right above line number 133 in your job).
and i get the many to many now. 3 key fields * number of key records, like the 000012-0 key, and i get 25 output records in SORTOUT.
What I will try next then is using the first 28 bytes as 1 key field for the Join which is the entire 3 field key plus some formatting pipe characters.
I will post if successful and it appears as if i will be.
my understanding of the SORTOUT dd is that is where the procedure is obtaining the record data for F1, F2 and BOTH.
Not correct. Comment or Delete SORTOUT and your sort would still work fine.
Quote:
same thing happened using 1,28,a.
You don't have to change matching key. You should use keys which matches your business requirement. Cross product or many to many match was just an explanation why your output is creating multiple records which you thought were "additional records".
Going back to your original questions...
Quote:
When the Joinkey step is executed the JES Spool starts filling up with the SORTOUT sysout up to about 5M before i have to cancel the darn thing.
This is because you are routing SORTOUT to sysout.
Code:
//SORTOUT DD SYSOUT=*
Did you try your original job without SORTOUT?
Quote:
How would one stop this step from looping and creating all those additional records ?
Its not looping or creating additional records. Sort is doing what you asked for.
FYI... your F2ONLY (//F2ONLY DD SYSOUT=*) is going to SYSOUT as well, if you do not intend to use it, remove it. This is probably good for testing but if you are moving this code to production, it would be useful if you delete this line or route them to flat file for research purpose, rather than routing them to SYSOUT.
Joined: 10 Dec 2010 Posts: 96 Location: Massachusetts
Did you try your original job without SORTOUT?
yes. //SORTOUT DD DUMMY. then it bombed on writing to BOTH. So, I will try to get away with a HUMONGOUS disk data set or spin that baby out to CART as a &&temp. BOTH is a holding area so I can search blocks of it for deltas like this:
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
madmartinsonxx wrote:
Did you try your original job without SORTOUT?
yes. //SORTOUT DD DUMMY. then it bombed on writing to BOTH. So, I will try to get away with a HUMONGOUS disk data set or spin that baby out to CART as a &&temp. BOTH is a holding area so I can search blocks of it for deltas like this:
What do you mean it bombed? Are you telling that it is not writing records to BOTH outfil file?
If you specify OUTFIL statements, you do not have to specify a SORTOUT DD statement. Read about SORTOUT and OUTFIL DD Statements here
I checked the rest of the items in your reply by placing a STOPAFT=20000 in my parsing step's and I got the desired results.
If you don't want many to many output why don't you eliminate duplicates in file2 using JNF2CNTL passing SUM FIELDS=NONE? This will give you Many to one matching results.
Joined: 10 Dec 2010 Posts: 96 Location: Massachusetts
What do you mean it bombed? = B37
I get the elimination of SORTOUT DD with OUTFIL now, thank you.
why don't you eliminate duplicates in file2 . I do want duplicates of the fields of the keyed records in both files so I can identify if any of the other remaining fields have changed. That is the whole premise of the procedure.
and thank you for pointing out the
Code:
//JNF2CNTL DD *
SUM FIELDS=NONE
//*
with that code snippet I suppose I could use the whole record as the key ( only 55 bytes ) and get rid of the whole record duplicates and deal with the changed fields only. I will try this and post.
thanks !