Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Tue Feb 26, 2013 8:36 pm    Post subject: Getting a cartesian join using JOINKEYS, but require 1 to 1
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7308

PostPosted: Tue Feb 26, 2013 9:21 pm    Post subject: Reply to: Getting a cartesian join using JOINKEYS, but requi
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7308

PostPosted: Tue Feb 26, 2013 9:59 pm    Post subject: Reply to: Getting a cartesian join using JOINKEYS, but requi
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    Post subject:
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: 8593
Location: Back in jolly old England

PostPosted: Wed Feb 27, 2013 1:09 pm    Post subject:
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    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 Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts Joinkeys with condition scdinesh DFSORT/ICETOOL 10 Tue Feb 14, 2017 12:20 am
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts Joinkeys with decimal scdinesh DFSORT/ICETOOL 3 Fri Feb 10, 2017 4:46 am
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us