DB2 FOR LUW / DB2FORLUW / 950 - DB2

This APAR applies to SQL queries which have all of the 
following conditions: 
 
1) The WHERE clause of the SQL query applies OR operators 
with a large number of subterms, and each subterm is of the form 
 
(C1=literal_1 AND C2=literal_2 AND ...), where literal_1 
etc. is either a literal, a host variable, a special register, 
or a parameter marker. 
2) The OR predicate contains only local equality predicates in 
all of the subterms. 
3) All of the subterms reference the same set of 
columns, and there are at least two columns referenced. 
4) There is an index with leading columns matching 
at least two of the columns referenced, or there is column 
group statistics with leading columns matching at least two of 
the columns referenced 
 
This kind of SQL query can take a long time and a lot of memory 
to compile. Furthermore, when an application is compiling this 
kind of SQL query, the application cannot be forced off; that 
is, the "force application" command may not take effect. 
 
SQL example: 
SELECT * FROM T 
WHERE 
(C1 = '20070301' AND C2 = '01' AND C3 = 6 ) 
OR (C1 = '20070204' AND C2 = '05' AND C3 = 23 ) 
..... 
..... 
..... 
OR (C1 = '20071212' AND C2 = '08' AND C3 = 55 )

**************************************************************** 
* USERS AFFECTED:                                              * 
* All platform                                                 * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* This APAR applies to SQL queries which have all of           * 
* thefollowing conditions:1) The WHERE clause of the SQL query * 
* applies OR operatorswith a large number of subterms, and     * 
* each subterm is of theform(C1=literal_1 AND C2=literal_2 AND * 
* ...), where literal_1etc. is either a literal, a host        * 
* variable, a specialregister,or a parameter marker.2) The OR  * 
* predicate contains only local equality predicatesinall of    * 
* the subterms.3) All of the subterms reference the same set   * 
* ofcolumns, and there are at least two columns referenced.4)  * 
* There is an index with leading columns matchingat least two  * 
* of the columns referenced, or there is columngroup           * 
* statistics with leading columns matching at least twoofthe   * 
* columns referencedThis kind of SQL query can take a long     * 
* time and a lot ofmemoryto compile. Furthermore, when an      * 
* application is compilingthiskind of SQL query, the           * 
* application cannot be forced off;thatis, the "force          * 
* application" command may not take effect.SQL example:SELECT  * 
* * FROM TWHERE(C1 = '20070301' AND C2 = '01' AND C3 = 6 )OR   * 
* (C1 = '20070204' AND C2 = '05' AND C3 = 23                   * 
* )...............OR (C1 = '20071212' AND C2 = '08' AND C3 =   * 
* 55 )                                                         * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.5 Fix Pack 6                        * 
****************************************************************

Modify the OR predicate to a in-list. For example, the above 
SQL query can be modified as SELECT * FROM T WHERE (C1, 
C2, C3) IN ( 
VALUES 
('20070301', '01', 6 ), 
('20070204', '05', 23 ), 
..... 
..... 
..... 
('20071212', '08', 55 ) 
)

DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
 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
 

Problem was first fixed in DB2 Version 9.5 Fix Pack 6

see local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC64200 IC64201 IC64202 
follow-up : 

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

25.10.2009
21.10.2010
21.10.2010

9.5.FP6