DB2 Buffer Pools - Automagically tuning

So I picked this nugget of wisdom up this week.

One of my most excellent DB2 SME colleagues pointed out the wonderful db2top utility, specifically in the context of monitoring Buffer Pools.

You see, ever since I learned to install IBM Operational Decision Manager (ODM), I've been creating a Buffer Pool, bp32k, as required by the documentation and, more importantly, the product: -

db2 create bufferpool BP32K size 8000 pagesize 32 K

The DB2 SME, let's call him ... John, pointed out that a Buffer Pool of 8,000 pages x 32K may not always be large enough for one's requirements.

He showed me how db2top can be used to see this, as per the following example ( NOT from ODM, hence the different BP names ): -

In this example, we have 5 Buffer Pools, one of which IBMDEFAULTBP, is getting the most hits.

John pointed out that a Buffer Pool can be reconfigured to support automatic tuning, allowing it to grow IF the need arises.

Failure to grow means that the Buffer Pool is going to start paging to disk, and we do NOT want paging to occur.

So we can check what Buffer Pools we have in a database: -

db2 connect to bpmdb1

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.1.3
 SQL authorization ID   = DB2INST1
 Local database alias   = BPMDB1

db2 "SELECT BPNAME, NPAGES, PAGESIZE FROM SYSCAT.BUFFERPOOLS"

BPNAME                                                                                                                           NPAGES      PAGESIZE   
-------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
IBMDEFAULTBP                                                                                                                              -2       32768

  1 record(s) selected.

and we can then check whether automatic configuration is enabled: -

db2 "select BP_NAME,AUTOMATIC FROM TABLE(MON_GET_BUFFERPOOL('',-2))"

BP_NAME                                                                                                                          AUTOMATIC
-------------------------------------------------------------------------------------------------------------------------------- ---------
IBMDEFAULTBP                                                                                                                             1
IBMSYSTEMBP4K                                                                                                                            0
IBMSYSTEMBP8K                                                                                                                            0
IBMSYSTEMBP16K                                                                                                                           0
IBMSYSTEMBP32K                                                                                                                           0

  5 record(s) selected.

*IF* the Buffer Pool hadn't been set to auto-tune, we could've then changed it as follows: -

db2 "alter bufferpool IBMDEFAULTBP immediate size 1000 automatic"

Bottom line, db2top is your friend, and one should always follow the same process: -

Instrument > Test > Monitor > Tune > Test > Monitor > Tune ............