DB2 FOR LUW / DB2FORLUW / 970 - DB2

The DB2_INLIST_TO_NLJN registry variable causes the optimizer to 
favour nested loop joins (NLJOIN) to join the list of values, 
using the table that contributes the IN list as the inner table 
in the join. If detailed index statistics are collected, which 
is the default collection method if automatic statistics 
collection is enabled, the optimizer might favour converting the 
predicate with the IN list to a join, using an NLJOIN operation 
where the join is not applied as a range delimiting predicate 
and when the predicate does not reduce the stream significantly. 
 
 
To identify if your query is impacted by this problem, you can 
collect an EXPLAIN of the query and compare the I/O cost of the 
NLJOIN operator and the inner FETCH operator; for example 
 
                 30 
               NLJOIN 
               (   9) 
               5832.86 
               232.835 
         /--------+-------\ 
        2                  15 
     TBSCAN              FETCH 
     (  10)              (  13) 
   0.00202749            9769.94 
        0                390.524 
       |               /---+----\ 
        2           4000     1.0e+06 
     SORT          IXSCAN   TABLE: DB2ADMIN 
     (  11)        (  14)         T1 
   0.00145019      51.9437        Q7 
        0             2 
       |             | 
        2        1.0e+096 
     TBSCAN    INDEX: DB2ADMIN 
     (  12)          IX1 
   4.2511e-05        Q7 
        0 
       | 
        2 
TABFNC: SYSIBM 
     GENROW 
       Q3 
 
 
The I/O of FETCH(13) is larger than the I/O of NLJN (9). If the 
join predicate is not applied at IXSCAN (14), then the query is 
likely affected.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users that have DB2_INLIST_TO_NLJN set and detailed      * 
* index statistics collected.                                  * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 7.                       * 
****************************************************************

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
 

Problem first fixed in DB2 Version 9.7 Fix Pack 7.

keiner bekannt / siehe Local-Fix

Vorgänger  : APAR is sysrouted TO one or more of the following: IC88327 
Nachfolger : 

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

11.05.2012
07.11.2012
07.11.2012

9.7.FP7