|
Problem IC66876
|
Status: Closed |
Statement compilation might return -101 for query with cartesian products when STMTHEAP is AUTOMATIC. |
| product: |
DB2 FOR LUW / DB2FORLUW / 980 - DB2 |
| Problem description: |
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: |
****************************************************************
* USERS AFFECTED: *
* All Platforms *
****************************************************************
* PROBLEM DESCRIPTION: *
* Statement compilation might return -101 for query with *
* cartesian *
* products when STMTHEAP is AUTOMATIC. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to v9.8 Fixpack 4 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.8 Fix Pack 4 for AIX and Linux
DB2 Version 9.8 Fix Pack 5 for AIX and Linux
|
| Solution |
First fixed in v9.8 Fixpack 4 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 04.03.2010
04.08.2011
04.08.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.8.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.8.0.4
|
 |
