Currently on DB2 V9.7 FP2.

Seeing these warnings in db2diag.log although bufferpools are set to automatic and there is lot of free memory on the box.

2010-07-15-10.00.32.467237-240 I104056A505 LEVEL: Warning
PID : 2003078 TID : 7969 PROC : db2sysc 0
INSTANCE: teieoryz NODE : 000
EDUID : 7969 EDUNAME: db2pfchr (IEORTEST) 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 (IEORDATABP1) is full.
This message will be suppressed for 60 minutes.


$ db2 "select substr(BPNAME,1,18), BUFFERPOOLID, PAGESIZE, NPAGES from syscat.bufferpools"

1 BUFFERPOOLID PAGESIZE NPAGES
------------------ ------------ ----------- -----------
IBMDEFAULTBP 1 4096 -2
IEORDATABP1 2 4096 -2
IEORIDXBP1 3 4096 -2

3 record(s) selected.


Any help/info on this would be greatly appreciated

I would use hardcoded number of pages instead of -2 (STMM) fpr each bufferpool. How many DB2 databases do you have on the server and how much total physical memory is there? Is this development, production, etc. How large is the database?

In addition to what Marcus has asked, what are the settings for the instance and database memory? What other parameters are set to automatic?

There are two instances on the box each with one database. Total box has 8 GB of memory out of which 6.6 GB of memory is allocated to the instance_memory of this instance where I am noticing the errors while the other instance has 0.075 GB memory allocated.

Both instance memory and database memory are set to automatic and STMM is turned on.

Here are the other parameters set to automatic

Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(298900)
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(6656)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(98)
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(87210)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(1393)
Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(278)
Database heap (4KB) (DBHEAP) = AUTOMATIC(13666)
Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(256)
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(40000)
Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384)
Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC
Max number of active applications (MAXAPPLS) = AUTOMATIC(362)


Here is the memory usage of the instance

$ db2pd -dbptnmem

Database Partition 0 -- Active -- Up 1 days 04:23:27 -- Date 07/15/2010 17:56:48

Database Partition Memory Controller Statistics

Controller Automatic: Y
Memory Limit: 6977820 KB
Current usage: 1488832 KB
HWM usage: 1845440 KB
Cached memory: 413568 KB

Individual Memory Consumers:

Name Mem Used (KB) HWM Used (KB) Cached (KB)
================================================== ======
APPL-IEORTEST 160000 160000 156096
DBMS-teieoryz 71872 71872 12352
FMP_RESOURCES 22528 22528 0
PRIVATE 29120 29120 6144
DB-IEORTEST 1204032 1570304 238976
LCL-p1065122 256 256 0
LCL-p1966170 256 256 0
LCL-p1065122 256 256 0
LCL-p1212416 256 256 0
LCL-p1142902 256 256 0


Notice that the highwater mark is way below than the memory limit . DB2 should automatically increase the memory rather than thowing such errors.


Here is the size of the database. This is our test environment

$ db2 "call get_dbsize_info(?,?,?,0)"

Value of output parameters
--------------------------
Parameter Name : SNAPSHOTTIMESTAMP
Parameter Value : 2010-07-15-18.00.56.671086

Parameter Name : DATABASESIZE
Parameter Value : 5710028800

Parameter Name : DATABASECAPACITY
Parameter Value : 17851723776

Return Status = 0

Instance parameters which are set to automatic

Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(1024)
Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(1744455)
Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(400)
Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(400)
Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)
Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(4096)
No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC(2048)
(db2udb@palmetto)/db2udb:

blazer789, this looks very familiar

You can try checking the STMM log to see if it makes any adjustments to the bufferpool(s). The warning in db2diag.log may just indicates the event that triggers BP size changes by STMM.

This message is for a block-based area of the bufferpool (not tuned by STMM)

Even this answer looks very familiar Bella . Thanks anyways !!