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
 

 

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

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

New User


Joined: 31 Oct 2006
Posts: 23
Location: brisbane

PostPosted: Wed May 02, 2007 11:30 am    Post subject: Using splice to effect two (table level) left outer joins
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

New User


Joined: 31 Oct 2006
Posts: 23
Location: brisbane

PostPosted: Wed May 02, 2007 11:34 am    Post subject: Re: using splice to effect two LOJs
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 Moderator


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

PostPosted: Wed May 02, 2007 9:51 pm    Post subject:
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

New User


Joined: 31 Oct 2006
Posts: 23
Location: brisbane

PostPosted: Thu May 03, 2007 11:37 am    Post subject: Re: Using splice to effect two (table level) left outer join
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 Moderator


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

PostPosted: Thu May 03, 2007 9:32 pm    Post subject:
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    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 Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Single COPY CICS TS datasets and when... Kyle Carroll CICS 2 Tue Oct 11, 2016 9:47 pm


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