DB2 FOR LUW / DB2FORLUW / 970 - DB2

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.

**************************************************************** 
* USERS AFFECTED:                                              * 
* all                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade the DB2 server to V9.7 Fix Pack 6                    * 
****************************************************************

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
 

This problem was first fixed in V9.7 Fix Pack 6

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC79968 IC79969 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

14.11.2011
19.06.2012
19.06.2012

9.7.FP6