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,
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,
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