|
Problem IC77368
|
Status: Closed |
EXPLAIN_DIAGNOSTIC TABLE MAY NOT HAVE DIAGNOSTICS FOR OPTIMIZATION PROFILE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Under very limited conditions, the optimizer may not
consistently
be able to log diagnostic information into the
EXPLAIN_DIAGNOSTIC table when an Optimization Profile is not
applied to a particular query.
The conditions under which this can occur include:
- multiple queries compiling at the same time
- statement heap is AUTOMATIC
- An Optimization Profile matches the query being compiled
- An error exists in the Optimization Profile preventing it from
being applied to the generation of the Query Access Plan.
Under these conditions, the optimizer may not log the
Optimization Profile errors into the EXPLAIN_DIAGNOSTIC tables,
nor will it return an SQL0101N (The statement is too long or too
complex) error, nor an SQLSTATE 54001. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users of DB2LUW using statement heap automatic and *
* optimization profiles *
****************************************************************
* PROBLEM DESCRIPTION: *
* Under very limited conditions, the optimizer may not *
* consistantly be able to log diagnostic information into the *
* EXPLAIN_DIAGNOSTIC table when an Optimization Profile is not *
* applied to a particular query. *
* *
* The conditions under which this can occur include: *
* - multiple queries compiling at the same time *
* - statement heap is AUTOMATIC *
* - An Optimization Profile matches the query being compiled *
* - An error exists in the Optimization Profile preventing it *
* from being applied to the generation of the Query Access *
* Plan. *
* *
* Under these conditions, the optimizer may not log the *
* Optimization Profile errors into the EXPLAIN_DIAGNOSTIC *
* tables, nor will it return an SQL0101N (The statement is too *
* long or too complex) error, nor an SQLSTATE 54001. *
****************************************************************
* RECOMMENDATION: *
* The customer should upgrade to DB2 Version 9.7 for Linux, *
* UNIX, and Windows Fix Pack 5. *
**************************************************************** |
| Local Fix: |
Re-prepare/re-compile the query with statement heap set to a
fixed value. |
| available fix packs: |
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 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
The fix for this problem is included in DB2 Version 9.7 for
Linux, UNIX, and Windows Fix Pack 5.
When an out of memory condition is encountered during
compilation of an SQL Statement that requires the loging of
Explain Diagnostic information for an Optimization Profile that
contains errors, an SQL0101N (The statement is too long or too
complex) error with an SQLSTATE 54001 will be returned. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 06.07.2011
19.01.2012
19.01.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
