DB2 FOR LUW / DB2FORLUW / 970 - DB2

CASE expressions containing a large number of WHEN clauses which 
are used in a <, <=, <>, =, >= or > predicate or OR predicates 
containing a very large number of AND subterms can consume a 
large amounts of memory during statement compilation. This can 
cause SQL0101N errors if  STMTHEAP, DATABASE_MEMORY or 
INSTANCE_MEMORY is limited. 
 
Examining the text  for the failing query, if you find a CASE 
expression involving more then 100 WHEN clauses involved in a 
relational predicate or OR predicates with more then 10000 
subterms then this APAR may be applicable.

Users Affected: 
All users prior to V97 FP1 
 
Problem Description: 
CASE EXPRESSION IN PREDICATES OR LARGE OR PREDICATES MAY CAUSE 
SQL0101 ERROR WHEN STMTHEAP IS SMALL /INSTANCE MEMORY IS LIMITED 
 
LOCAL FIX: 
1) Increase the STMTHEAP database configuration parameter, or if 
STMTHEAP is already large or set to AUTOMATIC then check the 
DATABASE_MEMORY and/or INSTANCE_MEMORY to ensure those limits 
are not impacting the amount of STMTHEAP which can be use. 
 
2) Modify the query to simplify the CASE or OR predicate. 
 
3) If the query contains a large CASE expression in a predicate 
causing the memory problems then lower the query optimization 
level for this query to 0 or 1.

1) Increase the STMTHEAP database configuration parameter, or if 
STMTHEAP is already large or set to AUTOMATIC then check the 
DATABASE_MEMORY and/or INSTANCE_MEMORY to ensure those limits 
are not impacting the amount of STMTHEAP which can be use. 
 
2) Modify the query to simplify the CASE or OR predicate. 
 
3) If the query contains a large CASE expression in a predicate 
causing the memory problems then lower the query optimization 
level for this query to 0 or 1.

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 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 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 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 Conclusion: 
 
First Fixed in DB2 V97 FP1

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

22.10.2009
13.03.2010
13.03.2010

9.7.FP1