IBM Support

SQLB_BPFULL error repeats or persists inspite of tuning the bufferpool

Technical Blog Post


SQLB_BPFULL error repeats or persists inspite of tuning the bufferpool


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


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 :





[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]