DB2 FOR LUW / DB2FORLUW / 970 - DB2

You might encounter large statement heap usage, which can 
produce an SQL0101N error or SQL0437W warning, when compiling a 
SQL statement with a large number of joins between tables 
partitioned by range.  This problem can occur when the joins 
reference the range partitioning key and there are also 
predicates that equate the key to a constant, such as 
DP_KEY_COLUMN = 'Y'.

**************************************************************** 
* USERS AFFECTED:                                              * 
* Table partitioning data organization                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* You might encounter large statement heap usage, which can    * 
* produce an SQL0101N error or SQL0437W warning, when          * 
* compiling a SQL statement with a large number of joins       * 
* between tables partitioned by range.  This problem can occur * 
* when the joins reference the range partitioning key and      * 
* there are also predicates that equate the key to a constant, * 
* such as DP_KEY_COLUMN = 'Y'.                                 * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 9.7 Fix Pack 6 or later release           * 
* containing the fix.                                          * 
****************************************************************

You could try to make the predicates look complex to prevent the 
compiler generate join predicates. For example, modify predicate 
DP_KEY_COLUMN = 'Y' to  DP_KEY_COLUMN || '' = 'Y'.  However, 
this will prevent it from consideration as an index range 
delimiting predicate and for data partition elimination, and the 
query runtime 
performance may be affected.

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 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

The problem was first fixed in Version 9.7 Fix Pack 6

See Local Fix

forerunner  : APAR is sysrouted TO one or more of the following: IC84540 
follow-up : 

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

24.11.2011
08.06.2012
08.06.2012

9.7.FP6