DB2 FOR LUW / DB2FORLUW / 970 - DB2

In systems that generate large numbers of unique statements with 
literals, turning statement concentrator on reduces the number 
of unique SQL statements (concentrated statements) that are 
compiled.     However, the dynamic SQL cache can be 
over-populated by statement entries of the original statement 
(ie, the pre-concentrated statements that contain the literals). 
     These statements are themselves not compiled - but are 
meant to help route repeating statements containing literals to 
the same 'concentrated statement'.    However due to the reduced 
number of concentrated statements there is much more room for 
many of the 'pre-concentrated statements'. 
 
Depending on the package cache size and the number of unique 
literal containing statements that are issued, the package cache 
can contain many thousands of pre-concentrated statement 
entries.   This can result in the package cache containing long 
chains and increasing the cost of lookup in the package cache 
 
Using db2pd -db <dbname> -dynamic, one can evaluate the contents 
of the dynamic SQL part of the package cache and determine how 
the contents of the dynamic SQL cache and the amount of 
statements containing literals can be monitored/evaluated.

**************************************************************** 
* USERS AFFECTED:                                              * 
* databases with stmt_conc=literals                            * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Package cache lookup degrades                                * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* upgrade to DB2 for LUW v9.7 fixpack 5                        * 
****************************************************************

Decreasing the package cache can help reduce the impact of this 
by forcing earlier eviction of the unconcentrated statements 
 
Disabling STMT_CONC will avoid this issue - although will lose 
the benefits of statement concentration

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
 

Fixed in DB2 for LUW V9.7 fixpack 5

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

26.06.2011
02.01.2012
02.01.2012

9.7.FP5