New bufferpool not being used

As part of a migration, I created a new bufferpool - say BP8K - size 8K, and a tablespace using that bp, and a table in that tablespace, that is loaded with data. However, I get a:

SQL1218N There are no pages currently available in bufferpool "4097"

during that process. Looking at db2top it appears as if the default bufferpool IBMSYSTEMBP8K is used instead of BP8K which explains the phenomena.

I've seen similar when there is not sufficient shared memory to start the bufferpool, but in this case BP8K shows up in both db2top, and db2pd -d ... -bufferpools. It is also possible to change the size of the bp which in the case of insufficient memory, usually results in a warning that the bufferpool is not started.

A db2stop; db2start fixes the problem, but should that really be necessary? The migration is a sql-script that runs from an upgrade framework, so I would rather not add some hook that forces all applications. Thoughts?

db2level
DB21085I This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL11050" with level
identifier "0601010F".
Informational tokens are "DB2 v11.5.0.0", "s1906101300", "DYN1906101300AMD64",
and Fix Pack "0".
Product is installed at "/opt/ibm/db2/V11.5".

db2licm -l
Product name: "DB2 Enterprise Server Edition"
License type: "Restricted"
Expiry date: "Permanent"
Product identifier: "db2ese"
Version information: "11.5"
Max amount of memory (GB): "128"
Max number of cores: "16"

cat /proc/meminfo
MemTotal: 164759044 kB
MemFree: 4267032 kB
MemAvailable: 131089520 kB


Mark Barinstein helped me determine that the bufferpool is not started after all:

`ADM6073W The table space "TBSPC8K" (ID "9") is configured to use
buffer pool ID "3", but this buffer pool is not active at this time.
In the interim the table space will use b

db2stop/db2start is not required to make a newly created bufferpool useable, but in most cases you must deactivate and reactivate the database for the tablespaces to be able to use the new bufferpool.

This is because, even if the IMMEDIATE option is specified or implicitly assumed


If there is not enough reserved space in the database shared memory to allocate the new buffer pool (SQLSTATE 01657) the statement is executed as DEFERRED.

Depending on the various memory configuration parameters in effect, particularly database_memory, there may not be enough memory reserved by the database manager to immediately allocate the new bufferpool.

There could be also an issue of timing, which is evident from the diagnostic log fragment added to the question later, where one can see that the tablespace creation occurs (with a warning) before the bufferpool for it is successfully allocated. It takes some time for the new shared memory for the BP to be allocated -- the database manager does a "memory walk", visiting every page in it to make sure it's committed by the operating system. Introducing a pause between create bufferpool and create tablespace might solve the problem.

StackExchange Database Administrators Q#262775, answer score: 4