read somewhere

DB2 join challenge
--------------------------------------------------------------------------------

I have table A with 2 columns: PK [integer] and Txt [varchar(128)],
size - 1000 rows. Also, I have table B with 100000 rows and 100 integer
columns, each one is a foreign key to A's PK. I need to create a view
in which every cell of B is replaced by the Txt from A according to the
PKs as in the example below. I assume I need to perform a join, but the
SQL I used writes a huge (several GB) temporary tablespace and thus it
is very slow. Any suggestions?

B:
X Y Z ...
----------------
1 3 5
3 2 1

A:
PK Txt
-----------
1 a
2 b
3 c
5 d

Resulting view:
X Y Z ...
----------------
a c d
c b a



--------------------------------------------------------------------------------

DB2 Admin Tool - Great manager for DB2 SQL server. Get access now. Download trial!. ( www.sqlmanager.net )
SQL Server Reporting Tool - Create SQL Reports fast! Simple Drag & Drop. Free Trial.. ( sql-reports.synaptris.com )
Backup Solutions - Data Protection-Business Continuity Expert Backup and Recovery Services. ( www.DataTechBackups.com )
Ads by Google
Knut Stolze
Guest
n/a Posts December 20th, 2005
03:25 PM
#2


Re: DB2 join challenge
--------------------------------------------------------------------------------

redeck wrote:
[color=blue]
> I have table A with 2 columns: PK [integer] and Txt [varchar(128)],
> size - 1000 rows. Also, I have table B with 100000 rows and 100 integer
> columns, each one is a foreign key to A's PK. I need to create a view
> in which every cell of B is replaced by the Txt from A according to the
> PKs as in the example below. I assume I need to perform a join, but the
> SQL I used writes a huge (several GB) temporary tablespace and thus it
> is very slow. Any suggestions?
>
> B:
> X Y Z ...
> ----------------
> 1 3 5
> 3 2 1
>
> A:
> PK Txt
> -----------
> 1 a
> 2 b
> 3 c
> 5 d
>
> Resulting view:
> X Y Z ...
> ----------------
> a c d
> c b a[/color]

Well, what have you tried already?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany


--------------------------------------------------------------------------------

redeck
Guest
n/a Posts December 21st, 2005
07:15 AM
#3


Re: DB2 join challenge
--------------------------------------------------------------------------------

CREATE VIEW VIEW_1 AS SELECT T1.TXT X, T2.TXT Y, T3.TXT Z FROM B LEFT
JOIN A AS T1 ON B.X = T1.PK LEFT JOIN A AS T2 ON B.Y = T2.PK LEFT JOIN
A AS T3 ON B.Z = T3.PK



--------------------------------------------------------------------------------

Knut Stolze
Guest
n/a Posts December 21st, 2005
08:05 AM
#4


Re: DB2 join challenge
--------------------------------------------------------------------------------

redeck wrote:
[color=blue]
> CREATE VIEW VIEW_1 AS SELECT T1.TXT X, T2.TXT Y, T3.TXT Z FROM B LEFT
> JOIN A AS T1 ON B.X = T1.PK LEFT JOIN A AS T2 ON B.Y = T2.PK LEFT JOIN
> A AS T3 ON B.Z = T3.PK[/color]

Why are you using a left outer join here? If I got this right, you'll not
have the case that there are any rows in table B that are not in A, right?
So you should try the following first:

SELECT t1.txt, t2.txt, t3.txt
FROM b JOIN a AS t1 ON ( b.x = t1.pk )
JOIN a AS t2 ON ( b.y = t2.pk )
JOIN a AS t3 ON ( b.z = t3.pk )

I would prefer the following way of writing query as it makes things more
explicit. However, I would not be surprised if that gives exactly the same
plan as the query above.

SELECT ( SELECT txt FROM a WHERE a.pk = b.x ),
( SELECT txt FROM a WHERE a.pk = b.y ),
( SELECT txt FROM a WHERE a.pk = b.z ),
FROM b


If removing the outer join is not leading to the desired results, you should
verify that you have the proper indexes defined on both tables. One index
on A(PK) is needed anyways for the primary key. Three indexes on B(X),
B(Y), and B(Z) might be helpful. Additionally you could try to use an
index A(PK) and include the TXT column. That way, it might be possible to
answer the query completely with index access only.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany


--------------------------------------------------------------------------------

mittalashish@gmail.com
Guest
n/a Posts December 21st, 2005
11:25 AM
#5


Re: DB2 join challenge
--------------------------------------------------------------------------------

Assuming that the table B has a primary key column, something like this
might work:

select b.pk, max(x) x, max(y) y, max(z) z ....
from (
select b.pk, case when b.x=a.pk then a.txt end x,
case when b.y=a.pk then a.txt end y,
case when b.z=a.pk then a.txt end....
from b,a where
b.x=a.pk or b.y=a.pk or b.z=a.pk....) etc.



--------------------------------------------------------------------------------

redeck
Guest
n/a Posts December 22nd, 2005
04:15 PM
#6


Re: DB2 join challenge
--------------------------------------------------------------------------------

Without the LEFT modifier I am getting the SQL0101N error: The
statement is too long or too complex. This is very strange because I
have STMTHEAP = 32768 x 4KB pages = 128 MB.



--------------------------------------------------------------------------------

Brian Tkatch
Guest
n/a Posts December 22nd, 2005
04:35 PM
#7


Re: DB2 join challenge
--------------------------------------------------------------------------------

This is just a wild guess.

Is it possible to split the query into smaller parts without LEFT, to
avoid the to complex error, and then use another view to UNION ALL them
together.

B.