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

JOIN UNPAIRED looping with 3 key fields defined


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
madmartinsonxx

New User


Joined: 10 Dec 2010
Posts: 96
Location: Massachusetts

PostPosted: Thu Jul 21, 2011 7:11 pm
Reply with quote

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 :

Code:

----+----1----+----2----+----3----+----4----+----5----+----6
********************************* Top of Data **************
000011-0          |ZHL0|IHBR|4155|D|0000000|N|300|GRPC|     
000011-0          |ZHL0|MECH|L823|D|9999999|Y|300|GRPC|     
000012-0          |ZHL0|MECH|C403|D|0000000|N|270|GRPC|     
000012-0          |ZHL0|MECH|C721|D|0000000|N|270|GRPC|     
000012-0          |ZHL0|MECH|C722|D|0000000|N|270|GRPC|     
000012-0          |ZHL0|MECH|C732|D|0000000|N|270|GRPC|     
000012-0          |ZHL0|MECH|C740|D|0000000|N|270|GRPC|     
000017-0          |ZHL0|MECH|C106|D|0000000|N|465|GRPC|     
000018-0          |ZHL0|ENGR|P205|D|0000000|N|670|GRPC|     
000019-0          |ZHL0|MECH|C336|D|0000000|N|210|GRPC|     
000019-0          |ZHL0|MECH|C712|D|0000000|N|210|GRPC|     
000019-0          |ZHL0|MECH|C724|D|0000000|N|210|GRPC|     
000019-0          |ZHL0|MECH|L280|D|       |Y|900|GRPC|     
000019-0          |ZHL0|MECH|L370|D|0000000|N|900|GRPC|     
000019-0          |ZHL0|MECH|L823|D|4525230|Y|210|GRPC|     
000020-0          |ZHL0|MECH|C156|D|0000000|N|270|GRPC|     
000020-0          |ZHL0|MECH|C323|D|0000000|N|270|GRPC|     

Prior file looks like this:
Code:

----+----1----+----2----+----3----+----4----+----5----+----6
********************************* Top of Data **************
000011-0          |ZHL0|MECH|L823|D|9999999|Y|300|GRPC|     
000012-0          |ZHL0|MECH|C403|D|0000000|N|270|GRPC|     
000012-0          |ZHL0|MECH|C721|D|0000000|N|270|GRPC|     
000012-0          |ZHL0|MECH|C722|D|0000000|N|270|GRPC|     
000012-0          |ZHL0|MECH|C732|D|0000000|N|270|GRPC|     
000012-0          |ZHL0|MECH|C740|D|0000000|N|270|GRPC|     
000017-0          |ZHL0|MECH|C106|D|0000000|N|465|GRPC|     
000018-0          |ZHL0|ENGR|P205|D|0000000|N|670|GRPC|     
000019-0          |ZHL0|MECH|C336|D|0000000|N|210|GRPC|     
000019-0          |ZHL0|MECH|C712|D|0000000|N|210|GRPC|     
000019-0          |ZHL0|MECH|C724|D|0000000|N|210|GRPC|     
000019-0          |ZHL0|MECH|L280|D|       |Y|900|GRPC|     
000019-0          |ZHL0|MECH|L370|D|0000000|N|900|GRPC|     
000019-0          |ZHL0|MECH|L823|D|4525230|Y|210|GRPC|     
000020-0          |ZHL0|MECH|C156|D|0000000|N|270|GRPC|     
000020-0          |ZHL0|MECH|C323|D|0000000|N|270|GRPC|     
000020-0          |ZHL0|MECH|C718|D|9999999|N|270|GRPC|     


this is my Joinkey step:
Code:

//* PIN THE KEYED RECORDS TOGETHER                       
//* GET ADDS FILE                                       
//PINKEYED EXEC PGM=ICEMAN                               
//IN01      DD DISP=SHR,DSN=TS2.MA.SAPLOAD.P2P292.CURRENT
//IN02      DD DISP=SHR,DSN=TS2.MA.SAPLOAD.P2P292.PRIOR 
//F1ONLY   DD DISP=(NEW,CATLG,DELETE),                   
//            DSN=TS2.MA.SAPLOAD.P2P292.ADDS,           
//            UNIT=DISK,SPACE=(TRK,(02,01),RLSE),       
//            DCB=(RECFM=VB,BLKSIZE=0,LRECL=0059)       
//F2ONLY   DD  SYSOUT=*                                 
//SORTSNAP DD  DUMMY                                     
//BOTH      DD DISP=(NEW,CATLG,DELETE),                 
//             DSN=TS2.MA.SAPLOAD.P2P292.PANDC,         
//             UNIT=DISK,SPACE=(TRK,(05,05),RLSE),       
//             DCB=(RECFM=FB,BLKSIZE=0,LRECL=0000)       
//SYSSOUT  DD  SYSOUT=*                                 
//DFSMSG   DD  SYSOUT=*                                 
//SYSOUT   DD  SYSOUT=*                                 
//SORTOUT   DD SYSOUT=*                                 
000133 //SYSIN DD *                                       
000134 *                                                 
000135  JOINKEYS F1=IN01,FIELDS=(01,18,A,19,04,A,24,04,A)
000136  JOINKEYS F2=IN02,FIELDS=(01,18,A,19,04,A,24,04,A)
000137  JOIN UNPAIRED,F1,F2                               
000138 * REPORT FIELDS                                   
000139  REFORMAT FIELDS=(F1:1,0055,F2:1,0055,?)           
000140 * CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)
000141  OPTION COPY                                       
000142  OUTFIL FNAMES=F1ONLY,INCLUDE=(0111,1,CH,EQ,C'1'),
000143     FTOV,BUILD=(1,0055),VLTRIM=C' '               
000144  OUTFIL FNAMES=F2ONLY,INCLUDE=(0111,1,CH,EQ,C'2'),
000145     BUILD=(0056,0055)                             
000146  OUTFIL   FNAMES=BOTH,INCLUDE=(0111,1,CH,EQ,C'B'),
000147     BUILD=(01,0055,0056,0055)                     
000148 /*                                                 

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:
Code:

000011-0          |ZHL0|IHBR|4155|D|0000000|N|300|GRPC|                         
000011-0          |ZHL0|MECH|L823|D|9999999|Y|300|GRPC|000011-0          |ZHL0|M
000012-0          |ZHL0|MECH|C403|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C403|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C403|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C403|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C403|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C721|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C721|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C721|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C721|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C721|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C722|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C722|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C722|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C722|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C722|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C732|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C732|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C732|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C732|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C732|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C740|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C740|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C740|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C740|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000012-0          |ZHL0|MECH|C740|D|0000000|N|270|GRPC|000012-0          |ZHL0|M
000017-0          |ZHL0|MECH|C106|D|0000000|N|465|GRPC|000017-0          |ZHL0|M
000018-0          |ZHL0|ENGR|P205|D|0000000|N|670|GRPC|000018-0          |ZHL0|E
000019-0          |ZHL0|MECH|C336|D|0000000|N|210|GRPC|000019-0          |ZHL0|M
000019-0          |ZHL0|MECH|C336|D|0000000|N|210|GRPC|000019-0          |ZHL0|M
000019-0          |ZHL0|MECH|C336|D|0000000|N|210|GRPC|000019-0          |ZHL0|M
000019-0          |ZHL0|MECH|C336|D|0000000|N|210|GRPC|000019-0          |ZHL0|M
000019-0          |ZHL0|MECH|C336|D|0000000|N|210|GRPC|000019-0          |ZHL0|M
000019-0          |ZHL0|MECH|C336|D|0000000|N|210|GRPC|000019-0          |ZHL0|M
000019-0          |ZHL0|MECH|C712|D|0000000|N|210|GRPC|000019-0          |ZHL0|M
000019-0          |ZHL0|MECH|C712|D|0000000|N|210|GRPC|000019-0          |ZHL0|M


How would one stop this step from looping and creating all those additional records ?
thanks[/code]
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Thu Jul 21, 2011 7:35 pm
Reply with quote

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).

Thanks,
Back to top
View user's profile Send private message
madmartinsonxx

New User


Joined: 10 Dec 2010
Posts: 96
Location: Massachusetts

PostPosted: Thu Jul 21, 2011 7:57 pm
Reply with quote

fair enough question sql. my understanding of the SORTOUT dd is that is where the procedure is obtaining the record data for F1, F2 and BOTH.
Code:

OUTFIL   FNAMES=BOTH,INCLUDE=(0111,1,CH,EQ,C'B'),
000147     BUILD=(01,0055,0056,0055) 


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.
Back to top
View user's profile Send private message
madmartinsonxx

New User


Joined: 10 Dec 2010
Posts: 96
Location: Massachusetts

PostPosted: Thu Jul 21, 2011 8:30 pm
Reply with quote

same thing happened using 1,28,a.
out of options now.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Thu Jul 21, 2011 9:04 pm
Reply with quote

madmartinsonxx,
Quote:
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.

Thanks,
Back to top
View user's profile Send private message
madmartinsonxx

New User


Joined: 10 Dec 2010
Posts: 96
Location: Massachusetts

PostPosted: Thu Jul 21, 2011 9:41 pm
Reply with quote

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:

Code:

 OPTION COPY                               
 OUTFIL FNAMES=SORTOUT,                   
 IFTHEN=(WHEN=(027,007,CH,NE,0072,0007,CH),
 BUILD=(C'C|',001,045,/,C'P|',046,045))   


again, thanks.
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.
icon_idea.gif
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Thu Jul 21, 2011 10:10 pm
Reply with quote

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

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ice1ca50/2.4.4.6?

madmartinsonxx wrote:

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.
icon_idea.gif


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.

Code:

//JNF2CNTL  DD *
  SUM FIELDS=NONE
//*
Back to top
View user's profile Send private message
madmartinsonxx

New User


Joined: 10 Dec 2010
Posts: 96
Location: Massachusetts

PostPosted: Thu Jul 21, 2011 11:01 pm
Reply with quote

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 !
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 -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts How to get a stack trace on a looping... ABENDS & Debugging 5
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts Join 2 files according to one key field. JCL & VSAM 3
No new posts JOIN STATEMENT PERFORMANCE. DFSORT/ICETOOL 12
No new posts how to keep unpaired records with REPRO. JCL & VSAM 9
Search our Forums:

Back to Top