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

Using splice to effect two (table level) left outer joins


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

Active User


Joined: 31 Oct 2006
Posts: 131
Location: brisbane

PostPosted: Wed May 02, 2007 11:30 am
Reply with quote

I have a requirement to effect two (table level) left outer joins using splice. The equivalent SQL would look something like :
Code:
select    T2.*
   , ...
from
(
   select    T1.*,
            ... 
   from
   (
   ...
   ) as T1

   left outer join TT1 ....on ...

) AS T2

left outer join TT2 on ...

Easiest to illustrate with a simplified example. Assume my input dataset was :
Code:
base1   on1   data1
base2   on2   data2   
base3   on3   data3
base4   on4   data4
TT1     on1             x1
TT2     on2                     y1
TT1     on3             x2
TT2     on3                     y2    (1)
TT2     on3                     y3    (2)
TT1     on5             x3

then as ouput I want :
Code:
base1   on1   data1     x1
base2   on2   data2             y1
base3   on3   data3     x2      y2
base4   on4   data4


I tried the obvious WITHEACH .. WITH ... WITH ... approach, but it failed to return base2 correctly ...
so, my approach involves two SPLICE operations :
(note that i can get more than one duplicate, but i only care about the first one ... hence the WITHEACH. In the input dataset above I would discard (2))
...
Code:
//TOOLIN   DD *                                                       
      %%IF $%%EXTR EQ $ACTION                                         
*                                                                     
*  LOJ ON TR24 :                                                     
*                                                                     
   COPY FROM(INA) TO(TMP1) USING(CPY1)                               
   SPLICE FROM(TMP1) TO(TMP2) ON(36,9,CH) ON(93,15,CH) KEEPNODUPS -   
          WITHEACH WITH(219,19)                                       
*                                                                     
*  LOJ ON TR22 :                                                     
*                                                                     
   COPY FROM(TMP2) TO(TMP3) USING(CPY2)                               
   SPLICE FROM(TMP3) TO(TMP4) ON(36,9,CH) ON(93,15,CH) KEEPNODUPS -   
          WITHEACH WITH(240,25) USING(CTL1)                           
*                                                                     
*  SORT INTO FINAL ORDER                                             
*
.....
//CPY1CNTL DD *                                                               
  INREC IFTHEN=(WHEN=(2,4,CH,EQ,C'TR24'),                                     
               BUILD=(36:12,9,93:23,15,219:40,4,225:46,10,237:58,1)),         
        IFTHEN=(WHEN=NONE,BUILD=(1,264))                                     
/*                                                                           
//CPY2CNTL DD *                                                               
  INREC IFTHEN=(WHEN=(2,4,CH,EQ,C'TR22'),                                     
               BUILD=(36:12,9,93:23,15,240:40,10,252:52,10,264:64,1)),       
        IFTHEN=(WHEN=NONE,BUILD=(1,264))                                     
/*                                                                           
//CPY3CNTL DD *                                                               
  OUTFIL FNAMES=OUTA,FTOV                                                     
/*                                                                           
//CTL1CNTL DD *                                                               
  OUTFILE FNAMES=TMP4,INCLUDE=(2,9,CH,EQ,C'IA_ACTION')                       
/*                                               

i am hoping there is a one SPLICE step approach, or at least something that looks more elegant ...
Back to top
View user's profile Send private message
jzhardy

Active User


Joined: 31 Oct 2006
Posts: 131
Location: brisbane

PostPosted: Wed May 02, 2007 11:34 am
Reply with quote

sorry, the TABs did not come out properly. Input / Output should read as :

base1 on1 data1
base2 on2 data2
base3 on3 data3
base4 on4 data4
TT1__ on1 ______x1
TT2__ on2 ________ y1
TT1__ on3 ______x2
TT2__ on3 ________ y2 (1)
TT2__ on3 ________ y3 (2)
TT1__ on5 ______x3


then as ouput I want :

base1 on1 data1 x1
base2 on2 data2 __ y1
base3 on3 data3 x2 y2
base4 on4 data4
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Wed May 02, 2007 9:51 pm
Reply with quote

Here's a DFSORT/ICETOOL job that will give you the expected output for your simplified input. I don't know how well this relates to your actual situation but it's the best I can do given what you've said about your data. Hopefully, you can extrapolate the technique to your real data.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN DD *
base1 on1 data1
base2 on2 data2
base3 on3 data3
base4 on4 data4
TT1   on1       x1
TT2   on2          y1
TT1   on3       x2
TT2   on3          y2 (1)
TT2   on3          y3 (2)
TT1   on5       x3
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN   DD    *
COPY FROM(IN) TO(T1) USING(CTL1)
SORT FROM(IN) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(7,3,CH) KEEPNODUPS -
  WITHEACH WITH(17,2) WITH(20,2) USING(CTL3)
/*
//CTL1CNTL DD *
  OMIT COND=(1,3,SS,EQ,C'TT1,TT2')
/*
//CTL2CNTL DD *
  INCLUDE COND=(1,3,SS,EQ,C'TT1,TT2')
  SORT FIELDS=(7,3,CH,A,1,3,CH,A)
  OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(7,3))
  OUTFIL FNAMES=T1,IFOUTLEN=80,
    IFTHEN=(WHEN=(1,3,CH,EQ,C'TT2',AND,81,8,ZD,EQ,+1),
    BUILD=(1,18,/,1,21))
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,OMIT=(1,3,SS,EQ,C'TT1,TT2'),
    BUILD=(1,80)
/*
Back to top
View user's profile Send private message
jzhardy

Active User


Joined: 31 Oct 2006
Posts: 131
Location: brisbane

PostPosted: Thu May 03, 2007 11:37 am
Reply with quote

almost there ... but this has the same problem as my first attempt in that it does not handle the base2 case ...

i was able to get it working ok by adding the following step (this comes just before the SPLICE step):

Code:

*  3\ APPEND TR22s AGAIN ONTO TEMP1.  THIS IS TO ENSURE THAT IF                 
*     THERE IS NO TR24 MATCH, WE STILL GET A TR22 MATCH AS EXPECTED             
*                                                                               
COPY FROM(INA) USING(CTL3)                                                       
*                                                                               
...

//CTL3CNTL DD *                                                             
  INCLUDE COND=(2,4,CH,EQ,C'TR22')                                           
  OUTFIL FNAMES=TMP1,                                                       
     BUILD=(36:12,9,93:23,15,240:40,10,252:52,10,264:64,1,265:10X)           
/*     


not sure it's easier to read, but it does improve performance. I have to say that i've never needed to simulate two LOJs like this before, but there are times when db2 indexes just don't work in your favour ...
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Thu May 03, 2007 9:32 pm
Reply with quote

Quote:
almost there ... but this has the same problem as my first attempt in that it does not handle the base2 case ...


The solution I gave you does handle the base2 case for the simple input you showed which is all I had to go on. I don't know what your real input looks like (I don't know SQL) so I couldn't code for that, but I'm glad my example helped you figure it out.
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 Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts How to load to DB2 with column level ... DB2 6
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
Search our Forums:

Back to Top