Posts Tagged ‘SQL20189W’

Increasing DB2 buffer pools

February 15, 2012

Just a small tip on DB2 memory allocation. It’s very well described in a number of articles, like this or in IBM DB2 official guide on Troubleshooting and Tuning Database Performance. What I want to describe here is how to increase buffer pools, probably one of the most important tuning parameters and very basic at the same time. The issue you can run into is when you increase buffer pool size you get an error SQL20189W:

The buffer pool operation (CREATE/ALTER) will not take effect until the next database startup due to insufficient memory.

It is not just a warning which suggests you to reboot. In fact, after a reboot your buffer pools won’t activate due to insufficient memory and database will work using small system buffers which will drastically decrease performance.

The reason why it happens is global memory cap which is configured in instance Configuration Parameters and called INSTANCE_MEMORY. It’s a total amount of memory which this instance can use for its operations. In order to have bigger buffer pools you must also increase this parameter. After that, SQL20189W goes away and you can tweak buffer pool memory on-the-fly. To check that change has happened use:

db2mtrk -d -v

and look for the line like

Buffer Pool Heap (1) is of size 3343450112 bytes