DB2 FOR LUW / DB2FORLUW / A50 - DB2

Queries containing an OUTER JOIN and at least three other tables 
in the from clause may experience poor performance in some 
scenarios. 
 
Typically the two tables referenced in the OUTER JOIN will be 
large tables without any predicates for local filtering and the 
query will contain a series of joins involving at least 3 tables 
each using different join columns. None of the tables joined to 
the OUTER JOIN will contain local predicates. 
 
An example of such query is: 
 
SELECT * 
  FROM T1, T2, T3, T4, T5, T6 
  LEFT OUTER JOIN T7 
    ON T6.c5 = T7.c5 
 WHERE T1.c4 = T2.c4 
   AND T2.c3 = T3.c3 
   AND T3.c1 = T4.c1 
   AND T4.c2 = T5.c2 
   AND T5.c2 = T6.c2 
     AND T1.c4 = ? 
 
There is a linear sequence of joins from T1 to T6 which is 
referenced in the outer join. The only filtering is on T1.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 10.5 Fix Pack 7 or higher.            * 
****************************************************************

Rewrite the query: 
SELECT * 
  FROM T1 INNER JOIN T2 
            ON T1.c4 = T2.c4 
                    INNER JOIN T3 
                    ON T2.c3 = T3.c3 
                    INNER JOIN T4 
                    ON T3.c1 = T4.c1 
                    INNER JOIN T5 
                    ON T4.c2 = T5.c2 
                    INNER JOIN T6 
                    ON T5.c2 = T6.c2 
          LEFT OUTER JOIN T7 
          ON T6.c5 = T7.c5 
 WHERE T1.c4 = ?

Problem first fixed in DB2 version 10.5 Fix Pack 7.

not known / see Local fix

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

23.05.2014
28.01.2016
28.01.2016