Technical Blog Post
Abstract
SQLB_BPFULL error repeats or persists inspite of tuning the bufferpool
Body
You may sometimes face a situation when you keep getting a SQLB_BPFULL error even if you have increased the bufferpool size to accommodate the load (or a particular SQL).
How to find out the problem ?
In such cases, first check your db2diag.log error message in detail :
2017-04-15-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.
The function sqlbFreeUpBlock() which reports the SQLB_BPFULL error is the main eyecatcher here.
This tells that you are using block-based buffer pools. They are used typically for large table scans during SEQUENTIAL prefetching.
Reference link :https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/c0009651.html
How to solve the problem ?
- Increasing the general bufferpool size will not help in this case. You also would need to increase NUMBLOCKPAGES,
e.g.
ALTER BUFFERPOOL BP_8K NUMBLOCKPAGES 24000
However, changing NUMBLOCKPAGES is always deferred. That means a DB restart is required to activate the change.
If changing NUMBLOCKPAGES also leads to SQLB_BPFULL you need to wait for the query to finish and then check if SQLB_BPFULL still persists.
If SQLB_BPFULL continues, you are hitting a known APAR IT83184 : http://www-01.ibm.com/support/docview.wss?uid=swg1IC83184
UID
ibm11140550