|
Problem IC66867
|
Status: Closed |
Statement compilation might return -101 for query with cartesian products when STMTHEAP is AUTOMATIC. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - 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: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* Problem:Under rare conditions, statement compilation might *
* return-101when STMTHEAP is AUTOMATIC. This problem only *
* applies toqueries with greater than ten table references *
* used incartestian products. For the error to be observed, *
* duringstatement compilation, the aggregate use of statement *
* heapmemory at the database partition by all *
* connectedapplicationsmust exceed a certain database and/or *
* instance memorythreshold.The exact threshold is not *
* quantifiable. It is likely thatrecompiling the query will *
* succeed.Workaround:Try compiling the statement again.As a *
* last resort, modify STMTHEAP to a manual value *
* withouttheAUTOMATIC attribute. For example, if "GET *
* DATABASECONFIGURATION [FOR database-alias] [SHOW DETAIL]" *
* shows:Description Parameter Current *
* ValueDelayed *
* Value---------------------------------------------------------
* statement heap (4KB) (STMTHEAP) = *
* AUTOMATIC(4267)AUTOMATIC(12000)you can modify STMTHEAP to *
* the delayed on-disk value of12000with:UPDATE DATABASE *
* CONFIGURATION [FOR database-alias] USINGSTMTHEAP *
* 12000Connect to the database prior to the UPDATE action so *
* thatthechange will be dynamic versus deferred. After the *
* UPDATEaction, verify the current value to confirm the change *
* tookeffect. *
****************************************************************
* RECOMMENDATION: *
* Look to Upgrade to a Version 9.7 Fix Pack 3 or later *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
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 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 9a 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 was first fixed in Version 9.7 Fix Pack 3 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 04.03.2010
27.09.2010
27.09.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.3
|
 |
| 9.7.0.3
|
|
