DB2 FOR LUW / DB2FORLUW / 970 - DB2

Parformance degradation on running dynamic SQL can be observed 
if all the following conditions are met. 
 
- Package cache become full, i.e package cache logical size 
reaches or exceeds PCKCACHESZ. 
- Dynamic cache is filled by statement entries that cannot be 
removed from 
the cache since they are associated with an application 
statement handle 
that has been prepared. 
 
In this condition, all subsequent dynamic SQL requests would 
perform cache cleanup effort so that new cache entries to be 
inserted into package cache. But when all or most of the entries 
in the cache cannot be removed due to statement entries that 
cannot be removed, it will be a wasted effort, and it leads to 
latch 
contention in sqlra_csm_del_stmt_empties like as below: 
 
 getConflict 
 sqlra_csm_del_stmt_empties 
 sqlra_cache_space_mgmt 
 sqlra_cache_reserve_memory 
 
Users can also see high CPU symptom on the DB2 instance in this 
situation. 
 
Issuing db2pd -db <dbname> -dynamic, a high ratio of "Dynamic 
SQL Statement" entries to "Dynamic SQL Variations" will make it 
more likely to be exposed to this issue. 
 
As per the Local Fix, this is likely indicative of a poorly 
tuned package cache and the package cache size should be 
increased.

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users who run many kinds of dynamic SQL statements.          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please upgrade to V9.7 FixPack 7 or later.                   * 
****************************************************************

Please apply one or both of the followings. 
 
a) Increase PCKCACHESZ so that package cache satisfies to store 
cache entries for prepared statement cache. 
b) Decrease the number of prepared statement cache.

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 DB2 V9.7 FixPack 7.

not known / see Local fix

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

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

11.04.2012
31.12.2012
31.12.2012

9.7.FP7