Help required with 5 table join

[peskyangel]

I have 5 DB2 tables, all with the same unique primary key.
That key is SEGMENT, CORP and ACCOUNT.

I can have 1 row for a given SEGMENT, CORP and ACCOUNT on all 5 of these tables, or 1 row on 4 of them and not the 5th, 1 row on 3 of them and not the other 2, 1 row on 2 of them and not the other 3, etc. etc.
There might not even be a row on any of the tables for a given SEGMENT, CORP and ACCOUNT.

I am interested in returning the values of two columns from each of these tables.

At the moment, my online COBOL program reads the tables consecutively. If it finds a row on the table, it populates details in a data block. If it doesn't find a row, these details are set to spaces. If there's another SQLCODE the program abends.

I would like to read the tables concurrently.

The SQL I've written to do this is as follows.

SELECT T1.STATUS_TYP, T1.STATUS_START_TS
,T2.STATUS_TYP, T2.STATUS_START_TS
,T3.STATUS_TYP, T3.STATUS_START_TS
,T4.STATUS_TYP, T4.STATUS_START_TS
,T5.STATUS_TYP, T5.STATUS_START_TS
FROM BASSDEV.BRC_DECEASED T1
RIGHT OUTER JOIN BASSDEV.BRC_DEFAULT T2
ON T1.SEGMENT = T2.SEGMENT
AND T1.CORP = T2.CORP
AND T1.ACCOUNT = T2.ACCOUNT
RIGHT OUTER JOIN BASSDEV.BRC_GONE_AWAY T3
ON T1.SEGMENT = T3.SEGMENT
AND T1.CORP = T3.CORP
AND T1.ACCOUNT = T3.ACCOUNT
RIGHT OUTER JOIN BASSDEV.BRC_EXCEPTION T4
ON T1.SEGMENT = T4.SEGMENT
AND T1.CORP = T4.CORP
AND T1.ACCOUNT = T4.ACCOUNT
RIGHT OUTER JOIN BASSDEV.BRC_EXCEPTION T5
ON T1.SEGMENT = T5.SEGMENT
AND T1.CORP = T5.CORP
AND T1.ACCOUNT = T5.ACCOUNT
WHERE (T1.SEGMENT = '01'
AND T1.CORP = '10'
AND T1.ACCOUNT = '4775965306368373')

Now this SQL returns 1 row containing the information I require when SEGMENT '01', CORP '10' and ACCOUNT '4775965306368373' is present on T1, and one, two, three or all four of the other tables.

If the record is not present on T1, nothing is returned, regardless of the fact that the row exists on T2, T3, T4 or T5.

1) Does anyone actually understand what I've just waffled on about? ;-)

2) Can anyone help? I'm pulling my hair out!

Thank you in advance.

 

[juliane26]

The WITH expression is just to make your SQL look 'nicer', you can implement the same using a bunch of subselects as well. So your so called temp table does not create additional overhead.

(BTW, I know temp tables as DECLARE GLOBAL TEMPORARY TABLE, which might be an overhead but is not needed here. WITH is still a common table expression)

Anyway, rewrite your SQL in that manner, it is much easier to be reviewed then.
unfortunately I did not get, what you actually want, still the following sample should work, just to see how to use it:

with
t1 as
(select * from syscat.tables),
t2 as
(select * from syscat.indexes)

select
t1.tabschema,
t1.tabname,
t2.indname
from t1 left outer join t2
on t1.tabname = t2.tabname and t1.tabschema = t2.tabschema


Juliane

 

[peskyangel]

This is the error message I get when I attempt to run any Query starting WITH:

DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD AS, TOKEN IS
<HEXSTRING> <CHARSTRING> <GRAPHSTRING> WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 76 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'0000004C' X'00000000' SQL DIAGNOSTIC INFORMATION

I'm using version 7.1 of DB2, running SPUFI under ZOs 1.4.

I like the concept of temporary tables, though.

 

[PruSQLer]

Oh, SPUFI. I doubt you can use temp tables with that. That's too bad since they're very handy to have. Not to criticize, because this has been a fun post, but you probably should have specified your platform right off the bat.

So I guess you're back to the individual calls although you might be able to do something with subqueries possibly(?) Subqueries aren't always the most efficient solution though.

Juliane,

Yes, you could do the Global Temp Table declaration as well. Those are actually faster than common table expressions, at least in my experience.

 

[peskyangel]

I'm only "testing" the SQL through SPFUI. I think I said in my first post that it was an online COBOL program that would be running the code in production. Sorry for any confusion!

 

[kkitt]

PruSQLer,

the Code below:

with t1 (id_1, id_2)
As (
select  distinct t1.agmt_pkge_id,  t2.agmt_pkge_id
from pfmc.rwt_agmt_elem_prod t1  [b]left outer join[/b] pfmc.rwt_agmt_pkge t2
     on t1.agmt_pkge_id = t2.agmt_pkge_id
     Where t1.agmt_pkge_id = ' 628838040000')
 ,
t2 (id_1, id_2, id_3)
As (
 Select distinct id_1, id_2,  t3.agmt_pkge_id     
From t1  left outer join pfmc.rwt_mon_prv_single t3
     on id_1 = t3.agmt_pkge_id  )
  
Select distinct id_1, id_2, id_3,  t4.agmt_pkge_id      
     
     from t2 left outer join pfmc.rwt_pips_lgr_trans t4
     on id_1 = t4.agmt_pkge_id

Will work if the information that he is looking for is, 1) on both table, or 2) if only on "T2". if the data is only on "T2" then the first join is going to return nothing since it is on a LEFT OUTER JOIN, and would have to be changed to a "RIGHT OUTER JOIN to get this information.

Here is something that may help out. Have a table generated that only holds the three key fields. Insert these values into it then execute the following query:

SELECT T1.t1StatusType, T1.t1StatusStart, tmpMain.mSegment, tmpMain.mCorp, tmpMain.mAccount, T2.t2StatusType, T2.t2StatusStart, 
             T3.t3StatusType, T3.t3StatusStart, T4.t4StatusType, T4.t4StatusStart, T5.t5StatusType, T5.t5StatusStart

FROM (((((tmpMain LEFT JOIN T1 ON tmpMain.mSegment = T1.t1Segment AND tmpMain.mCorp = T1.t1Corp AND tmpMain.mAccount = T1.t1Account) 
    LEFT JOIN T2 ON  tmpMain.mSegment = T2.t2Segment AND tmpMain.mCorp = T2.t2Corp AND tmpMain.mAccount = T2.t2Account)
    LEFT JOIN T3 ON tmpMain.mSegment = T3.t3Segment AND tmpMain.mCorp = T3.t3Corp AND tmpMain.mAccount = T3.t3Account) 
    LEFT JOIN T4 ON tmpMain.mSegment = T4.t4Segment AND tmpMain.mCorp = T4.t4Corp AND tmpMain.mAccount = T4.t4Account) 
    LEFT JOIN T5 ON tmpMain.mSegment = T5.t5Segment AND tmpMain.mCorp = T5.t5Corp AND tmpMain.mAccount = T5.t5Account)

WHERE tmpMain.mSegment="01" AND tmpMain.mCorp="10" AND tmpMain.mAccount="4775965306368373";

then delete the entry off the temp table. I have tested this with values on all table and just a few and it brings back the information that you are wanting.

 

[peskyangel]

Now that looks like it's going to do the trick!

However, in creating the temp table, inserting the row, running the SQL above and then deleting the row from the temp table, have I not performed more I/O than the 5 individual queries running consecutively?

 

[kkitt]

You mentioned that this is a online application, so I would just have the table created once and just do inserts/deletes to it.

Depending on how often is the same segment, code, account looked up, Deletes could be handled in a separate application that runs nighty/weekly/monthly/ect... if need be, deleting based off a TS that it has to be more then so old, but program would have to handle the dup inserts if it happens.

This will limit you I/O just to the insert/deletes and not needing to create/delete a temp table for each running instance. Also since you will not be needing to run the 5 seperate queries then I/O will also be reduced.

One other way would be to pre-populate this table and use it as a main diver to the other and change the inital application that creates accounts to add to this new table, this will elimate some extra I/O from this application.

As for any changes, testing is the only way to see what actual happens.... and as you can see there are many ways to go about it once a solution is found and many more issues that come about also.