DB2 FOR LUW / DB2FORLUW / 980 - DB2

The DB2 Optimizer may underestimate the number of pages read 
from disk when a join to a data partitioned table is using the 
data partitioning columns. This can cause the optimizer to 
choose a plan that will perform poorly. 
 
This may be applicable to queries with an inner join involving a 
data partitioned table or an outer join when the NULL producing 
input to the join is a data partitioned table. In both cases, 
the query will have equality (=) join predicates involving all 
the data partitioning columns of the data partitioned table. 
 
For the poorly performing query, obtain the output from db2exfmt 
and look for a NLJOIN operator with a TBSCAN of the data 
partitioned table as the right-side input. Furthermore the 
estimated I/O cost of the NLJOIN will look small in comparison 
to the size of the data partitioned table. 
 
For example: 
            4.52747e+07 
              NLJOIN 
              (  27) 
              7.66338 
               1985                  <<< I/O Cost estimate is 
small given the large table 
        /-------+--------\ 
                         37666.1 
                         TBSCAN 
                         (  29) 
                         7.64325 
                          1985 
                           | 
                       4.52747e+07 
                   DP-TABLE: DB2INST1 
                       PARTTABLE1 
                           Q11 
 
When examining the operator details for the TBSCAN(29), the join 
predicates are used a DP Elim predicates: 
 
        DP Elim Predicates: 
        ----------------- 
        Range 1) 
            Start Predicate: (Q11.PARTCOL = Q10.COL1 
            Stop  Predicate: (Q11.PARTCOL = Q10.COL1

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See above.                                                   * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 9.8 Fix Pack 3 or later release that      * 
* contains the fix.                                            * 
****************************************************************

1) Create an optimization profile for the query forcing either a 
different join method for the join in question or forcing the 
access of the data partitioned table to use an IXSCAN of a good 
index. 
 
2) Increase the values for SYSSTAT.TABLES.NPAGES and 
SYSSTAT.TABLES.FPAGES for the data partitioned table until a 
different plan is chosen.

DB2 Version 9.8 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.8 Fix Pack 4 for AIX and Linux
 DB2 Version 9.8 Fix Pack 5 for AIX and Linux
 

Problem was first fixed in Version 9.8 Fix Pack 3.

keiner bekannt / siehe Local-Fix

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

16.04.2010
06.01.2011
06.01.2011

9.8.FP3