DB2 FOR LUW / DB2FORLUW / A10 - DB2

An SQL statement containing a join between a table and a VALUES 
clause might result in suboptimal performance under any of the 
following conditions: 
 
1.  The VALUES clause contains 1 set of values 
2.  The VALUES clause contains > 1 set of values and the SQL 
statement contains multiple predicates between the base table 
and VALUES clause 
 
The following is an example of condition 1: 
 
WITH TMP (A,B) as (VALUES (1,2)) 
SELECT ... FROM TMP, T1 
WHERE TMP.A=T1.A AND TMP.B=T1.B 
 
The following is an example of condition 2: 
 
WITH TMP (A,B) as (VALUES 
(1,1),(1,2),(1,3),(1,4),(2,5),(2,6),(2,7),(2,8)) 
SELECT ... FROM TMP, T1 
WHERE TMP.A=T1.A AND TMP.B=T1.B 
AND T1.A IN (SELECT A FROM TMP) 
AND T1.B IN (SELECT B FROM TMP)

**************************************************************** 
* USERS AFFECTED:                                              * 
* All DB2 v10.1 on DB2 LUW                                     * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 v10.1 Fix Pack 4 or above                     * 
****************************************************************

You can avoid this problem by removing the redundant joins in 
the query.  In the example for condition 1, since the VALUES 
clause contains a single set of values, you can remove the TMP 
table expression and replace the values in the predicates.  In 
the example for condition 2, you can remove the redundant IN 
predicates, which are effectively joins between the VALUES 
clause and the table T1.

DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

Fixed in DB2 v10.1 Fix Pack 4

not known / see Local fix

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

31.01.2014
09.06.2014
09.06.2014