By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,977 Members | 1,352 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,977 IT Pros & Developers. It's quick & easy.

Federated Database: performance problem with outer-joins

P: n/a
hi,

os: aix 4.3.3
DB2: version 7 FP3

we are using a federated DB setup, datasource and fed-Db are both
V7FP3 (in fact they are on the same server) and are having massiv
performance problems.

i tracked it back to the way the queries are push-downed to the
datasource.

(simplified) example, dynexpln-output:
-----------------------------------------------------------------------------
SQL Statement:

select count(*)
from aix.tkonto o left outer join aix.tkunde u on
u.kunden_nummer=o.kunden_nummer
Estimated Cost = 3850
Estimated Cardinality = 1

Distributed Subquery #2
| #Columns = 1
Left Outer Merge Join
| Distributed Subquery #1
| | #Columns = 1
Aggregation
| Column Function(s)
Return Data to Application
| #Columns = 1

Distributed Subquery #1:
Server: ODSDB (DB2/6000 7.2)
Subquery SQL Statement:

SELECT A0."KUNDEN_NUMMER"
FROM "AIX"."TKUNDE" A0
ORDER BY 1 ASC

Nicknames Referenced:
AIX.TKUNDE ID = 121 Base = AIX.TKUNDE
#Output Columns = 1

Distributed Subquery #2:
Server: ODSDB (DB2/6000 7.2)
Subquery SQL Statement:

SELECT A0."KUNDEN_NUMMER"
FROM "AIX"."TKONTO" A0
ORDER BY 1 ASC

Nicknames Referenced:
AIX.TKONTO ID = 117 Base = AIX.TKONTO
#Output Columns = 1
-----------------------------------------------------------------------------
both tables are on the remote DB, instead of pushing down the whole
query, DB2 translates it to two subqueries. both tables are quite
large and therefore a massive overhead is introduced.
(the similar query as INNER JOIN ist completly pushdowned)

are there any tuning-parameters (i know about Serveroptions PUSHDOWN
'Y' etc. tried them to no avail) ?
is this a know restriction ?

greetings . . . joerg
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.