Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
JOIN UNPAIRED looping with 3 key fields defined

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: JOIN UNPAIRED looping with 3 key fields defined
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: 578
Location: USA

PostPosted: Thu Jul 21, 2011 7:35 pm    Post subject:
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    Post subject:
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    Post subject:
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: 578
Location: USA

PostPosted: Thu Jul 21, 2011 9:04 pm    Post subject:
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    Post subject:
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    Post subject:
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

http://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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SORT BUT RETAIN HIGHEST VALUE ON NON-... leondan22 DFSORT/ICETOOL 2 Thu Dec 14, 2017 8:13 pm
No new posts Looping REXX - unable to interrupt RAVISANKAR07 CLIST & REXX 3 Wed Dec 06, 2017 1:08 pm
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts JOINKEYS Paired and Unpaired Sort on ... MFwhiz DFSORT/ICETOOL 7 Tue Nov 07, 2017 2:46 am
No new posts Subtract the ZD fields to provide neg... Balaryan DFSORT/ICETOOL 4 Wed Oct 11, 2017 10:51 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us