DB2 FOR LUW / DB2FORLUW / 950 - DB2

The optimizer may choose a sub-optimal access plan for an SQL 
statement which uses an IN and a place holder variable as part 
of the FROM clause. 
 
e.g. 
select ... from tab1, tab2 where tab1.COLA=tab2.COLA and 
tab1.COLB IN (?,?,?,...); 
 
If there is an index with t1.COLB as the leading column in the 
key, the optimizer can consider an in-to-join transformation of 
the IN predicate shown in the WHERE clause. 
If the access of tab1 is on the inner of a NLJN, the optimizer 
might consider a plan with a TEMP over the inner access that 
might be beneficial for the in-to-join access on the inner.  In 
general, this is a good option to consider but when there are 
statement place holders, the cardinality estimates cannot always 
be accurate.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All Platforms                                                * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* THE OPTIMIZER MAY CHOOSE A SUB-OPTIMAL ACCESS PLAN FOR SQL   * 
* USING                                                        * 
* SUB-SELECT & PLACE HOLDER VARIABLE.                          * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to v9.5FP8 or higher                                 * 
****************************************************************

dbset DB2_EXTENDED_IN2JOIN=NLJN_TEMP_OFF

DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
 

First fixed in v9.5FP8 or higher

keiner bekannt / siehe Local-Fix

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

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

14.01.2011
07.07.2011
07.07.2011

9.5.FP8