IBM Support

How to remove buffer pool exception entry from DB2 LUW DPF Database to enable it for Self Tuning.

How To


Summary

When self-tuning memory is enabled in partitioned database environments, there is a single database partition (known as the tuning partition)
that monitors the memory configuration and propagates any configuration changes to all other database partitions to maintain a consistent
configuration across all the participating database partitions.

In a partitioned database environment, the memory tuner will start only if the database is activated by an explicit ACTIVATE DATABASE command,
because self-tuning memory requires that all partitions be active.

To disable self-tuning memory for a particular buffer pool on a specific database partition, issue the ALTER BUFFERPOOL statement,
specifying a size value and the partition on which self-tuning memory is to be disabled.

An ALTER BUFFERPOOL statement that specifies the size of a buffer pool on a particular database partition will create an exception entry
(or update an existing entry) for that buffer pool in the SYSCAT.BUFFERPOOLDBPARTITIONS catalog view. If an exception entry for a buffer pool exists,
that buffer pool will not participate in self-tuning operations when the default buffer pool size is set to AUTOMATIC. To remove an exception entry so that a buffer pool can be enabled for self tuning:

Objective

To remove buffer pool exception entry from DB2 LUW DPF Database to enable it for self tuning.

Environment

DB2 LUW All Versions

Steps


Steps to Remove Buffer pool Exception

1->Disable self tuning for this buffer pool by issuing an ALTER BUFFERPOOL statement, setting the buffer pool size to a specific value.

2->Issue another ALTER BUFFERPOOL statement to set the size of the buffer pool on these database partition to the size value specified in Step 1.

3->Enable self tuning for this buffer pool by issuing another ALTER BUFFERPOOL statement, setting the buffer pool size to AUTOMATIC.

Example 

In below case IBMDEFAULTBP has exception value set , When you try to turn on Automatic tuning you have to follow define steps.
 
# db2 "select * from SYSCAT.BUFFERPOOLEXCEPTIONS"
BUFFERPOOLID MEMBER NPAGES
------------ ------ -----------
   1   0   485043
   1   1   485043

From above output can see Buffer pool id 1 has exception for member 0 and 1. 

IF you try to turn on Automatic tuning , 

db2 alter bufferpool ibmdefaultbp size 8000000 Automatic it won't turn it on.

if you query syscat.bufferpool it will show npages value -2 ... it mean Automatic is enable for this bufferpool

 # db2 "select * from syscat.bufferpools"
BPNAME                                                                                                                           BUFFERPOOLID DBPGNAME                                                                                                                         NPAGES      PAGESIZE    ESTORE NUMBLOCKPAGES BLOCKSIZE   NGNAME  
-------------------------------------------------------------------------------------------------------------------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ------ ------------- ----------- --------------------------------------------------------------------------------------------------------------------------------
IBMDEFAULTBP                                                                                                                                1 -                                                                                                                                         -2        4096 N                  0           0 -       


But db2pd -d sample -bufferpools output shows Automatic is false. Why db2pd still shows its Automatic value false?
 
# db2pd -d sample -bufferpool
Address            Id   Name               PageSz     PA-NumPgs  BA-NumPgs  BlkSize    NumTbsp    PgsToRemov CurrentSz  PostAlter  SuspndTSCt Automatic
0x0A000300801D1F20 1    IBMDEFAULTBP       4096       485043     0          0          51         0          485043     485043     0          False


If you are not aware of DPF bufferpool exception than it will raises concerns in your mind when you see difference in db2pd and syscat.bufferpool output. 
This case You can query SYSCAT.BUFFERPOOLEXCEPTIONS catalog table to check this bufferpool has any exception.
 
# db2 "select * from SYSCAT.BUFFERPOOLEXCEPTIONS"
BUFFERPOOLID MEMBER NPAGES
------------ ------ -----------
      1      0      485043
      1      1      485043


To enable automatic tuning here you have to remove bufferpool exceptions of these DPF partitions. 

You need to follow below steps to remove Bufferpool Exception mention in the start the this article. 

1->Disable self tuning for this buffer pool by issuing an ALTER BUFFERPOOL statement, setting the buffer pool size to a specific value.

#db2 alter bufferpool ibmdefaultbp size 8000000


2->Issue another ALTER BUFFERPOOL statement to set the size of the buffer pool on this database partition to the size value specified in Step 1.
 
db2 alter bufferpool ibmdefaultdp member 0 size 8000000 (edited) 

db2 alter bufferpool ibmdefaultbp member 1 size 8000000 (edited) 


Note repeat step 2 for all DB partitions has exception entry in BUFFERPOOLEXCEPTIONS catalog view.

3->Enable self tuning for this buffer pool by issuing another ALTER BUFFERPOOL statement, setting the buffer pool size to AUTOMATIC.

db2 alter bufferpool ibmdefaultdp Automatic


You can verify querying SYSCAT.BUFFERPOOLEXCEPTIONS to check for any exceptions for any DB partition still exists

# db2 "select * from SYSCAT.BUFFERPOOLEXCEPTIONS"
BUFFERPOOLID MEMBER NPAGES
------------ ------ -----------




No exception records are displayed it means bufferpool is enable for Automatic tuning. You can run db2pd and check Automatic is set to True , see below
 
# db2pd -d sample -bufferpool
Address            Id   Name               PageSz     PA-NumPgs  BA-NumPgs  BlkSize    NumTbsp    PgsToRemov CurrentSz  PostAlter  SuspndTSCt Automatic
0x0A000300801D1F20 1    IBMDEFAULTBP       4096       485043     0          0          51         0          8000000     8000000     0          True

 
# db2 "select * from syscat.bufferpools"
BPNAME                                                                                                                           BUFFERPOOLID DBPGNAME                                                                                                                         NPAGES      PAGESIZE    ESTORE NUMBLOCKPAGES BLOCKSIZE   NGNAME  
-------------------------------------------------------------------------------------------------------------------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ------ ------------- ----------- --------------------------------------------------------------------------------------------------------------------------------
IBMDEFAULTBP                                                                                                                                1 -                                                                                                                                         -2        4096 N                  0           0 -       


Now IBMDEFAULT bufferpool turn on for automatic syscat.bufferpools and db2pd -buff page both showing it enable for self tuning.
    
Refer : https://www.ibm.com/docs/en/db2/11.5?topic=stmo-using-self-tuning-memory-in-partitioned-database-environments
 

Document Location

Worldwide


[{"Type":"MASTER","Line of Business":{"code":"","label":""},"Business Unit":{"code":"","label":""},"Product":{"code":"SS2PSR","label":"Db2 for Linux"},"ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

More support for:
Db2 for Linux

Software version:
All Versions

Document number:
7180804

Modified date:
20 January 2025

UID

ibm17180804

Manage My Notification Subscriptions