|
Problem IC64006
|
Status: Closed |
CASE EXPRESSION IN PREDICATES OR LARGE OR PREDICATES MAY CAUSE SQL0101 ERROR WHEN STMTHEAP IS SMALL /INSTANCE MEMORY IS LIMITED |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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. |
| Problem Summary: |
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. |
| 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. |
| available fix packs: |
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
|
| Solution |
Problem Conclusion:
First Fixed in DB2 V97 FP1 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.10.2009
13.03.2010
13.03.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
