DB2 FOR LUW / DB2FORLUW / 970 - DB2

Buffer pool decreases, often initiated by STMM, may hang for 
long periods due to special system temporary pages that are 
being held in the buffer pool.  If STMM initiated the buffer 
pool decrease, it will be hung and unable to respond to changes 
in workload or memory availability. 
 
The "special temp pages" in question are for internal system 
temporary objects that are cached for reuse until application 
termination.  A common triggering scenario is performing 
special operations such as create index or runstats on user 
temporary tables (declared or created global temporary tables). 
 
The workaround is to avoid decreasing or fix the sizes of 
buffer pools associated with system temporary tablespaces.  Note 
that although the triggering activity may be associated with 
user temporary tables in "user" temporary tablespaces, the 
internal objects are created in the "system" temporary 
tablespaces. 
 
If STMM is inoperative for long periods of time, and user 
temporary tables are commonly created on the system, this is the 
likely source of the problem. 
 
One way of determining that STMM is hung is that regular 
configuration updates cease.  A second is that the STMM logs 
stop being updated.  The STMM thread, db2stmm, regularly writes 
messages to the STMM logs (in DIAGPATH/stmmlog).  Note db2agent 
threads may still write messages to the STMM logs. 
 
A call stack of the db2stmm thread will show that it is in the 
midst of a buffer pool decrease : 
thread_wait 
getConflictComplex 
getConflict 
getConflict 
sqlbSecurePageProtection 
sqlbRemInvalidPagesFromBufferPool 
sqlbDecreaseBufferpoolSize 
sqlbResizeBufferPool 
sqlbAlterAutomaticBufferPool

**************************************************************** 
* USERS AFFECTED:                                              * 
* Systems with STMM tuning a bufferpool with an associated     * 
* tempspace                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 5                        * 
****************************************************************

Fix the sizes of buffer pools associated with system temporary 
table spaces.

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
 

Problem first fixed in DB2 Version 9.7 Fix Pack 5

not known / see Local fix

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

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

06.07.2011
27.01.2012
27.01.2012

9.7.FP5