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