DB2 FOR LUW / DB2FORLUW / 970 - 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              * 
* ofthefollowing conditions:1) The WHERE clause of the SQL     * 
* queryapplies OR operatorswith a large number of subterms,    * 
* andeach subterm is of theform(C1=literal_1 AND C2=literal_2  * 
* AND...), where literal_1etc. is either a literal, a          * 
* hostvariable, a specialregister, or a parameter marker.2)    * 
* The ORpredicate contains only local equality predicatesin    * 
* all ofthe subterms.3) All of the subterms reference the same * 
* setof columns,and there are at least two columns             * 
* referenced.4)There is an index with leading columns matching * 
* at leasttwoof the columns referenced, or there is            * 
* columngroupstatistics with leading columns matching at least * 
* twoof thecolumns referencedThis kind of SQL query can take   * 
* along time and a lot ofmemory to compile.Furthermore, when   * 
* anapplication is compiling this kind ofSQL query,            * 
* theapplication cannot be forced off; that is,the             * 
* "forceapplication" 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 )                                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to product Version 9.7 Fix Pack 2LOCAL FIX:Modifythe * 
* OR predicate to a in-list. For example, the aboveSQLquery    * 
* can be modified asSELECT * FROM T WHERE(C1, C2, C3)          * 
* IN(VALUES('20070301', '01', 6 ),('20070204', '05',           * 
* 23),...............('20071212', '08', 55 ))                  * 
****************************************************************

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.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a 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 10 for Linux, UNIX, and Windows
 

Problem was first fixed in Version 9.7 Fix Pack 2

not known / see Local fix

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

28.10.2009
14.06.2010
14.06.2010

9.7.FP2