DB2 FOR LUW / DB2FORLUW / 970 - DB2

When compiling a complex SQL statement with a large number of 
range partitioned tables that join on the partition by range key 
columns, you might receive a SQL0101 error. For example, the 
following SQL statement contains a large number of joins between 
tables that are each partitioned by range on key column A: 
 
  SELECT * 
  FROM 
      T1,T2,T3,T4,T5,T6,T7,T8,T9,T10, ... 
  WHERE 
      T1.A = T2.A and T2.A = T3.A and 
      T3.A = T4.A and T4.A = T5.A and 
      T5.A = T6.A and T6.A = T7.A and 
      T7.A = T8.A and T8.A = T9.A and 
      T9.A = T10.A and ...

**************************************************************** 
* USERS AFFECTED:                                              * 
* Range partitioned tables users                               * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to db2 Version 9.7 FixPack 10 or higher              * 
****************************************************************

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 are 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 by using an equivalent 
expression; for example, the predicate T1.A = T2.A can be 
rewritten to T1.A = (T2.A + 0) if column A is an integer.

Fixed in Version 9.7 FixPack 10

not known / see Local fix

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

24.03.2014
10.11.2014
10.11.2014

9.7.FP10