DB2 FOR LUW / DB2FORLUW / 970 - DB2

Starting DB2 v9.7, relatively large query with multiple UNION 
ALL clauses and subquery with DISTINCT keyword may have slow 
performance than previous versions. This is a very uncommon 
scenario which Optimizer has missed the opportunity to choose 
the best plan. 
User can check for following conditions 
 
-There is a fairly big cartesian join below the outer join in 
the access plan. 
 DB2 may push down a join (in this case a subquery with a GROUP 
BY) that can magnify the size of the cartesian join which can 
cause a huge SORT to remove duplicates. 
For example, In previous version same query would have access 
plan which shows: 
 
 
                  join (predicate between T1 and T3) 
                 /     \ 
         outer join     GB over T3 
        /          \ 
   MEDIUM SORT     T2 
      / 
 cartesian join 
/             \ 
XXX            T1 
 
 
Where in v9.7, following access plan is chosen which degrades 
the performance. 
 
           outer join 
          /          \ 
      BIG SORT        T2 
        / 
      join (predicate between T1 and T3) 
     /    \ 
   join    T1 
  /   \ 
XXX    GB over T3 
 
 
The fix for this APAR will enhance this so that DB2 Optimizer 
will choose a much smaller SORT to remove duplicates and not 
push down the subquery if the target is a cartesian join 
 
                  join (predicate between T1 and T3) 
                 /     \ 
         outer join     GB over T3 
        /          \ 
 cartesian join    T2 
/             \ 
XXX            SMALL SORT to remove duplicates from T1

**************************************************************** 
* USERS AFFECTED:                                              * 
* All.                                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Problem Description above.                               * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 5.                       * 
****************************************************************

Rewrite the query to replace DISTINCT by GROUP BY clause.

DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

First Fixed in Version 9.7 Fix Pack 5.

keiner bekannt / siehe Local-Fix

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

04.03.2011
23.12.2011
23.12.2011

9.7.FP5