DB2 FOR LUW / DB2FORLUW / A10 - DB2

In a query with multiple outer join operations and an IS NULL 
predicate, the DB2 SQL compiler might choose such a join order 
that does not apply the IS NULL predicate early. This might 
result in suboptimal performance if the IS NULL predicate can 
reduce the data stream. Consider the following example: 
 
select * 
from fact left outer join d1 on f1 = id1 
          left outer join d2 on f2 = id2 
          left outer join d3 on f3 = id3 
          left outer join d4 on f4 = id4 
where d3.c31 is null; 
 
The access plan graph generated for this query could be as 
follows 
 
                                          RETURN 
                                            | 
                                          >HSJOIN 
                                        /---+----\ 
                                    FILTER       TBSCAN 
                                      |            | 
                                    >HSJOIN        D4 
                            /---------+---------\ 
                        >HSJOIN                 TBSCAN 
                 /--------+--------\              | 
             >HSJOIN               TBSCAN         D3 
         /-----+------\              | 
     TBSCAN           TBSCAN         D2 
       |                | 
      FACT              D1 
 
where the IS NULL predicate is applied by FILTER operator. If 
the following conditions are true, then performing the join of 
the FACT table with table D3 first is the optimal join order : 
 
1. The left most table ( LMT ) involved in a chain of outer join 
operations is a base table, such as the FACT table above. 
2. There is a filtering IS NULL predicate applied to a table on 
the null-producing side of an outer join ( NPT ), such as the D3 
table above. 
3. The join between LMT and NPT is not an expanding join.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Please see the Error Description section.                    * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.1 Fix Pack 1.                      * 
****************************************************************

Rewrite the query such that the chain of outer joins begins with 
the join between LMT and NPT

DB2 Version 10.1 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

Upgrade to DB2 Version 10.1 Fix Pack 1.

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

13.06.2012
05.11.2012
05.11.2012