DB2 FOR LUW / DB2FORLUW / A10 - DB2

When compiling a SQL statement that includes joins with one or 
more range partitioned tables and includes multiple OR, or 
equivalent IN, predicates that references the partition by range 
key in a subset of the subterms, you might receive a SQL0101 
error.  You are more likely to encounter this error if the OR 
predicates contain a large number of subterms and the IN 
predicates contain a large list of values. 
 
The following is an example of an SQL statement that includes a 
join with a table, RP, that is partitioned by range on key 
column A and multiple IN and OR predicates that references it: 
 
SELECT * 
FROM 
    RP, T1, ... 
WHERE 
    T1.X = RP.X AND 
    RP.A IN (1,2,3,4,5,6,...) AND 
    (RP.A = 1 OR T1.A = 1 OR ...) AND 
    (RP.A = 2 OR T1.A = 2 OR ...) AND ...

**************************************************************** 
* USERS AFFECTED:                                              * 
* version 10.1                                                 * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to version 10.1 fix pack 5                           * 
****************************************************************

You can increase the database STMTHEAP value or set it to 
AUTOMATIC to avoid the SQL0101N error, but if there are other 
limits, such as INSTANCE_MEMORY, causing the SQL0101N error to 
persist, you can try any of the following solutions to limit the 
complexity of the query: 
 
1.  evaluate whether any of the joins or IN/OR predicatesare 
redundant or unnecessary, and if so, remove them from the query; 
or 
 
2.  reduce the query optimization class to 0, 1, or 2, which 
will limit optimizations performed and reduce the memory 
requirements; or 
 
3.  rewrite the join predicates or OR predicate subterms that 
reference the partition by range key column by using an 
equivalent expression; for example, the subterm RP.A = 1 in the 
example SQL statement can be 
rewritten to (RP.A + 0) = 1 since column A is an integer.

First fixed in version 10.1 fix pack 5

not known / see Local fix

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

25.03.2014
13.07.2015
13.07.2015