Latest versionsfixlist
11.1.0.6 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26
Question in the chat LiveZilla Live Help

DB2 - Problem description

Problem IC79968 Status: Closed

INTERNAL QUERY COMPILER STRUCTURE INCORRECTLY LEFT INITIALIZED AT THE WRONG
OPTIMIZATION LEVEL LEADING TO BAD PERFORMANCE

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
A sequence of SQL statements combined with changing of 
optimization level, can lead to query compilation with an 
incorrect query compiler setting. 
This can lead to poor query performance. 
 
An example of such a sequence executing on the same connection. 
 
1) A statement is compiled using query optimization level 0 ( 
There is no entry for this statement 
in the dynamic sql (package) cache at that time. ). 
2) The query optimization level special register is altered e.g. 
via 
SET CURRENT QUERY OPTIMIZATION 5 
3) A new invocation occurring, for example a 'CALL PROC' 
statement BUT the statement that causes the invocation 
must itself either be static or picked up from the dynamic sql 
cache. 
4) Inside the nested invocation, i.e. the stored procedure, it 
needs to drive another compile. In the stored procedure case 
that would include dynamic SQL or incremental bind. (  (e.g. due 
REOPT ONCE/ALWAYS 
specification or references to global temporary tables) 
 
 
If statements inexplicably run slower at times, then this apar 
may be the cause. 
To identify this, obtain the current execution plan using the 
EXPLAIN_FROM_SECTION stored procedure and 
compare the execution plan with an execution plan obtained 
through e.g. db2exfmt. 
A bad execution plan triggered by this apar may show a lack of 
certain operators that are available at a specific optimization 
level. 
( The DB2 Infocenter contains a description on what operations 
are considered in different optimization classes under this 
section : Database fundamentals > Performance tuning > Factors 
affecting performance > Query optimization > Optimizing query 
access plans ) 
 
To remedy the problem at runtime, the statement execution needs 
to be cancelled and a new compilation can be forced by running 
either : 
" 'EXPLAIN PLAN FOR <the statement> " 
or alternatively clearing the package cache by invoking the 
"FLUSH PACKAGE CACHE" statement.
Problem Summary:
Suboptimal query performance due to internal compiler structure 
problem.
Local Fix:
available fix packs:
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
This problem has first been fixed in V9.5 Fix Pack 9
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
23.11.2011
08.03.2012
08.03.2012
Problem solved at the following versions (IBM BugInfos)
9.5.FP9
Problem solved according to the fixlist(s) of the following version(s)
9.5.0.9 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.