DB2 FOR LUW / DB2FORLUW / A10 - DB2

A query that contains a large IN predicate applied to the same 
column as an equality join may show a RIDSCAN consuming many 
IXSCAN inputs instead of using a nested loop join to probe the 
index with the key values. For example the following query could 
suffer from this issue when both T1.c1 and T2.c1 have indexes 
defined. 
 
SELECT * 
FROM T1, T2 
WHERE T1.c1 = T2.c1 
  AND T1.c1 in (?, ?, ?, ?, ?, ?, ?, 
        ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
?, ?, ?, ?, 
        ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
?, ?, ?, ?, 
        ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
?, ?, ?, ?, 
        ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
 
Upon examination of the access plan you may see a NLJOIN between 
T1 and a GENROW operator followed by a subsequent join to T2 
that is accessed using RIDSCAN with many IXSCAN input plans.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.1 Fix Pack 2.                      * 
****************************************************************

Create and apply an optimization profile that forces the IN to 
join plan using the following guideline 
 
<OPTGUIDELINES><INLIST2JOIN/></OPTGUIDELINES>

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
 

First fixed in DB2 Version 10.1 Fix Pack 2.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

28.11.2012
09.04.2013
09.04.2013