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

Getting a cartesian join using JOINKEYS, but require 1 to 1


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

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Tue Feb 26, 2013 8:36 pm
Reply with quote

ICE201I is H

I am trying to join two files on keys and fill in the gap at bytes 4 and 5 of infile 2 with bytes 3 and 4 from infile 1 to get the required output. Was totally gobsmacked when the first attempt gave RC 0, but only until I looked at the output icon_sad.gif

Each of the infile 2 keys are being matched to each of the infile 1 keys. What I want is that the first record of infile 2 matched with the first record of infile 1 and so on.

I think that I could do it with SPLICE, but wondering if it was easier using JOINKEYS. I can not see anything in the JOINKEYS syntax documentation or anything in the JOINKEYS examples I've found to help.

Any help / guidance greatly appreciated.

Using the statements below
Code:

//SYSIN    DD *                                         
 JOINKEYS  FILE=F1,FIELDS=(1,2,A),SORTED,NOSEQCK               
 JOINKEYS  FILE=F2,FIELDS=(2,2,A),SORTED,NOSEQCK               
 REFORMAT  FIELDS=(F2:1,100,F1:1,10,?)                 
 OPTION    COPY                                         
 OUTFIL    FNAMES=FILEOUT,INCLUDE(111,1,CH,EQ,C'B'),   
           BUILD=(1,3,103,2,6,95)                       
/*                                                     


Input file 1, RECFM=FB, LRECL=10
Code:

GAAR
GAAS
GAAT
GAAU
GAAV
GB1R
GB1V
GB25
GB26
GB28
GB3K
GCDN
GCEK
GCFF
GCFU
GCFW
GCFX


Input file 2, RECFM=FB, LRECL=100
Code:

AGA  AAATEST FILE1       
AGA  AAATEST FILE2       
AGA  AAATEST FILE3       
AGB  AAATEST FILE1       
AGB  AAATEST FILE2       
AGC  AAATEST FILE1       
AGC  AAATEST FILE2       


Output file should be, RECFM=FB, LRECL=100
Code:

AGAARAAATEST FILE1       
AGAASAAATEST FILE2       
AGAATAAATEST FILE3       
AGB1RAAATEST FILE1       
AGB1VAAATEST FILE2       
AGCDNAAATEST FILE1       
AGCEKAAATEST FILE2       


Output file is being created as
Code:

AGAARAAATEST FILE1
AGAARAAATEST FILE2
AGAARAAATEST FILE3
AGAASAAATEST FILE1
AGAASAAATEST FILE2
AGAASAAATEST FILE3
AGAATAAATEST FILE1
AGAATAAATEST FILE2
AGAATAAATEST FILE3
AGAAUAAATEST FILE1
AGAAUAAATEST FILE2
AGAAUAAATEST FILE3
AGAAVAAATEST FILE1
AGAAVAAATEST FILE2
AGAAVAAATEST FILE3
.
.
.
.
and so on
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Feb 26, 2013 9:21 pm
Reply with quote

I think if you use IFTHEN=(WHEN=GROUP to PUSH a SEQ for each of the keys, then extend the JOINKEYS to include the SEQ field.

You could create the REFORMAT record already with the layout you want

Code:
(F2:1,3,F1:3,2,F2:6,95,?)


And then the BUILD become (1,100). If there are large amounts of data (unlikely) there is less data to "throw around".
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Feb 26, 2013 9:59 pm
Reply with quote

This:

Code:
//SYSIN    DD *
  JOINKEYS  FILE=F1,FIELDS=(1,2,A,5,3,A),SORTED,NOSEQCK
  JOINKEYS  FILE=F2,FIELDS=(2,2,A,101,3,A),SORTED,NOSEQCK
  REFORMAT  FIELDS=(F2:1,3,F1:3,2,F2:6,95,?)
  OPTION COPY
  OUTFIL    FNAMES=FILEOUT,INCLUDE=(101,1,CH,EQ,C'B'),
             BUILD=(1,100)
                                                           
//JNF1CNTL DD *
   INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,2),PUSH=(5:SEQ=3))
//JNF2CNTL DD *
   INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(2,2),PUSH=(101:SEQ=3))


Gives this, with your data:

Code:
AGAARAAATEST FILE1
AGAASAAATEST FILE2
AGAATAAATEST FILE3
AGB1RAAATEST FILE1
AGB1VAAATEST FILE2
AGCDNAAATEST FILE1
AGCEKAAATEST FILE2
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Tue Feb 26, 2013 10:34 pm
Reply with quote

I agree with bill. You can number the records and then perform the match. By default JOINKEYS only gives you paired aka matched records. so you really don't need the indicator and INCLUDE condition on OUTFIL.



Code:

//STEP0100 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=* 
//SORTJNF1 DD DISP=SHR,DSN=Your input FB 010 byte file1
//SORTJNF2 DD DISP=SHR,DSN=Your input FB 100 byte file2
//SORTOUT  DD SYSOUT=* 
//SYSIN    DD *                                             
  OPTION COPY                                               
  JOINKEYS FILE=F1,FIELDS=(1,2,A,005,3,A),SORTED,NOSEQCK     
  JOINKEYS FILE=F2,FIELDS=(2,2,A,101,3,A),SORTED,NOSEQCK   
  REFORMAT  FIELDS=(F2:1,3,F1:3,2,F2:6,95)                 
//*                                                         
//JNF1CNTL DD *                                             
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,2),PUSH=(5:SEQ=3)) 
//*                                                         
//JNF2CNTL DD *                                             
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(2,2),PUSH=(101:SEQ=3))
//*
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Wed Feb 27, 2013 1:09 pm
Reply with quote

Thank you Bill and Kolusu,

The examples have now given me a lot more knowledge into the workings of JOINKEYS. Seeing an example of working code for your own problem always seems to make the solution more understandable, especially for the use of the JNF1CNTL and JNF2CNTL DD statements.

Also excellent reference for future solutions when needed.

So, you really can teach an old dog some new tricks.
Now that you guys have so kindly given me the code, what shall I do until lunchtime icon_biggrin.gif
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 Joinkeys - 5 output files DFSORT/ICETOOL 7
No new posts PD not working for unsigned packed JO... DFSORT/ICETOOL 5
No new posts Def PD not working for unsigned packe... JCL & VSAM 3
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
Search our Forums:

Back to Top