Question & Answer
Question
I have many applications connecting to a database that are using declared global temporary tables. From time to time I see that most of applications go into "Commit Active" state. Why does it happen?
Cause
A big bufferpool associated with temporary tablespace.
Answer
Look at the buffer pool configuration using the db2pd command:
db2pd -db <database> -bufferpools
A big bufferpool, associated with the temporary tablespace used for declared global temporary tables (DGTT) objects can contain a large number of dirty pages. For example, when the application used DGTTs resets its connection, DB2 will drop all the temporary objects that application has created before. What happens if the caller asks buffer pool component (BPS) to flush a certain object? Buffer pool component must in turn latch every dirty list and walk through it sequentially in order to find and flush every dirty page belonging to the object to be flushed. Clearly, the longer the dirty lists, the more time this operation takes. The latch is exclusive, which means while the walk latch is being held, other applications accessing the bufferpool are blocked.
To eliminate this problem, create a smaller bufferpool and associate it with a temporary tablespace.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21422394