|
Problem IC66865
|
Status: Closed |
Statement compilation might return -101 for query with cartesian products when STMTHEAP is AUTOMATIC. |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
Problem:
Under rare conditions, statement compilation might return -101
when STMTHEAP is AUTOMATIC. This problem only applies to
queries with greater than ten table references used in
cartestian products. For the error to be observed, during
statement compilation, the aggregate use of statement heap
memory at the database partition by all connected applications
must exceed a certain database and/or instance memory threshold.
The exact threshold is not quantifiable. It is likely that
recompiling the query will succeed.
Workaround:
Try compiling the statement again.
As a last resort, modify STMTHEAP to a manual value without the
AUTOMATIC attribute. For example, if "GET DATABASE
CONFIGURATION [FOR database-alias] [SHOW DETAIL]" shows:
Description Parameter Current Value
Delayed Value
----------------------------------------------------------------
---------------------
SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(4267)
AUTOMATIC(12000)
you can modify STMTHEAP to the delayed on-disk value of 12000
with:
UPDATE DATABASE CONFIGURATION [FOR database-alias] USING
STMTHEAP 12000
Connect to the database prior to the UPDATE action so that the
change will be dynamic versus deferred. After the UPDATE
action, verify the current value to confirm the change took
effect. |
| Problem Summary: |
Statement compilation might return -101 for query with cartesian
products when STMTHEAP is AUTOMATIC. |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in Version 9.5 Fix Pack 6 |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC66867 IC66876
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 04.03.2010
17.05.2010
17.05.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
