DB2 FOR LUW / DB2FORLUW / A50 - DB2

Performance could be poor when a small table joins a large table 
join result, e.g. 
 
select * from small_nick a, (select * from large_nick1 ln1, 
large_nick2 ln2 where ln1.c = ln2.c) b where a.c = b.c 
 
where the join of ln1 and ln2 generates a large result set. The 
bad performance can be resulted from Optimizer fails to generate 
a optimal plan. This is because, the optimal plan is a NLJN with 
the large table as the inner side, and the join predicate is 
pushdown to remote server with a FILTER under SHIP of inner side 
to filter data fetched as much as possible, that is: 
           NLJN 
   /                  \ 
SHIP              SHIP 
 |                      | 
a                   FILTER 
                /                 \ 
              large_nick1    large_nick2 
 
However, due to limitation of Optimizer, a plan with the join 
predicate above the SHIP will be chosen, such a poor plan could 
be like: 
                  NLJN 
           /                 \ 
       SHIP             TBSCAN  <---join predicate is included 
here, right above the SHIP 
         |                    | 
         a                TEMP 
                              | 
                          SHIP 
                             | 
                         FILTER 
                             .... 
                       /                 \ 
                 large_nick1    large_nick2 
 
With such poor plan, all data from the two large nickames will 
be fetched, causing bad performance.

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users who use Federation Server v10.5                        * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Federation Server v10.5 fp4 or above              * 
****************************************************************

DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
 

The problem is fixed in v10.5 fp4

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

03.02.2014
08.09.2014
08.09.2014