DB2 FOR LUW / DB2FORLUW / 970 - DB2

Performance degradation may be noticed after the upgrade to DB2 
v97 from previous versions of DB2 using statement concentrator 
which is introduced in DB2 v97. 
 
During the performance degradation, package cache growth can be 
seen which may result into package cache overflow. 
 
To identify the issue, db2pd -apm dynamic can be collected for 
couple of iterations during the performance issue and you can 
see the number of variation inserts, the number of statement 
inserts/deletes into the package cache grows signifacantly. 
 
 
Also from traces collected, or from tprof output, you can see 
the following function being called and be the top user of CPU: 
 
sqlra_csm_del_stmt_empties 
 
This issue can only happen when stmt_conc database configuration 
parameter is set to LITERALS.

PERFORMANCE DEGRADATION AFTER THE UPGRADE TO DB2 V97 FROM 
EARLIER DB2 VERSIONS WHEN STMT_CONC IS ON.

Turn off the statement concentrator if not needed: 
 
db2 update db cfg for <db_name> using STMT_CONC OFF

DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 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 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Fixed in DB2 v97FP3 
 
LOCAL FIX: 
Turn off the statement concentrator if not needed: 
 
db2 update db cfg for <db_name> using STMT_CONC OFF

not known / see Local fix

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

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

31.05.2010
29.09.2010
29.09.2010

9.7.FP3