IC83184: NO AVAILABLE BUFFER POOL PAGES IN BUFFER POOL BLOCK AREA

DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• 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.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error description listed above.                          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 9.7 FP7 or subsequent fix pack.               *
  ****************************************************************
  

Problem conclusion

• Problem First Fixed in DB2 Version 9.7 Fix Pack 7
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  IC87853

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• R970 PSN

     UP