Latest versionsfixlist
11.1.0.6 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26
Question in the chat LiveZilla Live Help

DB2 - Problem description

Problem IC77427 Status: Closed

BUFFER POOL DECREASE / STMM HANG WAITING ON SPECIAL TEMP BUFFER POOL PAGE

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
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
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All systems                                                  * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.5 Fix Pack 9                        * 
****************************************************************
Local Fix:
Fix the sizes of buffer pools associated with system temporary 
table spaces.
available fix packs:
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

Solution
Problem first fixed in DB2 Version 9.5 Fix Pack 9
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
08.07.2011
12.06.2012
12.06.2012
Problem solved at the following versions (IBM BugInfos)
9.5.FP9
Problem solved according to the fixlist(s) of the following version(s)
9.5.0.9 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.