This problem is only affecting block-based buffer pools. When a
large number of sequential prefetch requests is serviced by a
single buffer pool, the block area of the buffer pool may become
permanently occupied by pages that can never be removed from the
buffer pool. The eyecatcher is SQLB_BPFULL reported by
sqlbFreeUpBlock() in the diagnostic log, for example:
2012-03-29-19.09.01.604728-300 I6250582A498 LEVEL: Warning
PID : 7667754 TID : 12338 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000
EDUID : 12338 EDUNAME: db2pfchr (SAMPLE) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbFreeUpBlock,
probe:20
MESSAGE : ZRC=0x8502002C=-2063466452=SQLB_BPFULL
"no available buffer pool pages"
DATA #1 : <preformatted>
Buffer pool 2 (BP_8K) is full.
This message will be suppressed for 60 minutes.
This error is not to be confused with legitimate "buffer pool
full" errors. In the normal "buffer pool full" case, the error
disappears once the workload decreases; for this case it is
recommended to tune the buffer pool. In the malicious case,
SQLB_BPFULL will persist regardless of the workload until the
next database restart, and no tuning can alleviate the error.
Also, a buffer pool contents dump (to be taken by DB2 Support
Analyst) will reveal that the block area is permanently occupied
by a relatively small number of pages that are located on
so-called "alternate hate lists" (term understood by DB2
Service).
Upon encountering SQLB_BPFULL from sqlbFreeUpBlock(), DB2 will
revert to using the page-based area of the buffer pool, and no
application error will be returned. This means that in many
cases, the impact will be unnoticeable. However, the permanent
presence of the error may compromise the performance of
sequential prefetching.
WORKAROUND:
1. Restart the database (deactivate/activate), OR
2. If feasible, revert to using page-based buffer pools only. |
