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
Environment
Steps
# db2 "select * from SYSCAT.BUFFERPOOLEXCEPTIONS"
BUFFERPOOLID MEMBER NPAGES
------------ ------ -----------
1 0 485043
1 1 485043
IF you try to turn on Automatic tuning ,
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.
# db2 "select * from SYSCAT.BUFFERPOOLEXCEPTIONS"
BUFFERPOOLID MEMBER NPAGES
------------ ------ -----------
1 0 485043
1 1 485043
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
db2 alter bufferpool ibmdefaultdp member 0 size 8000000 (edited)
db2 alter bufferpool ibmdefaultbp member 1 size 8000000 (edited)
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.
Document Location
Worldwide
Was this topic helpful?
Document Information
More support for:
Db2 for Linux
Software version:
All Versions
Document number:
7180804
Modified date:
20 January 2025
UID
ibm17180804