Member self-tuning memory in Db2 pureScale environments

In a Db2 pureScale environment, each member has its own self-tuning memory manager (STMM) tuner, which actively tunes the memory configurations of the particular member that is based on dynamic workload characteristics and local resources.

For a new database that is created in the 11.5 release, the default value of STMM tuning member in the SYSCAT table is -2. This setting ensures that each member has its own tuner, tuning independently to balance the following factors:
  • Workload
  • Db2 memory requirements
  • System memory requirements
The memory requirements might be different on each member.
Ensuring that STMM tuning capabilities are present on each member is important in the following scenarios:
  • A consolidated environment where multiple databases can have workload peak at different time of the day.
  • The “member subsetting” capability is enabled so that a workload can be spread across selected members.
STMM decides on which member the STMM tuner is active based on the value in the SYSCAT table. The SYSCAT table is updated by using the UPDATE STMM TUNING MEMBER stored procedure, as shown:
         CALL SYSPROC.ADMIN_CMD('update stmm tuning member member')
Here is a summary of the members on which the STMM tuner is active based on the value in the SYSCAT table.
Table 1. Determining the members on which STMM tuner is active
Value in SYSCAT table Member where STMM Tuner is running
-2 All members.
-1 One member, which is chosen by STMM.
Any number that matches a member number Member with number that matches the value in SYSCAT
Any number that does not match a member number Defaults to -1, where the tuner runs on one member, which is chosen by STMM

Note that when the tuning member changes, some data collected from the member which was running the tuner, is discarded. This data must be recollected on the new tuning member. During this short period of time when the data is being recollected, the memory tuner will still tune the system; however, the tuning can occur slightly differently than it did on the original member.

Starting the memory tuner in a Db2 pureScale environment

In a Db2 pureScale environment, the memory tuner will run whenever the database is active on one or more members that have self_tuning_mem set to ON.

Disabling self-tuning memory for a specific member

  • To disable self-tuning memory for a subset of database members, set the self_tuning_mem database configuration parameter to OFF for those members.
  • To disable self-tuning memory for a subset of the memory consumers that are controlled by configuration parameters on a specific member, set the value of the relevant configuration parameter to a fixed value on that member. It is recommended that self-tuning memory configuration parameter values be consistent across all running members.
  • To disable self-tuning memory for a particular buffer pool on a specific member, issue the ALTER BUFFERPOOL statement, specifying a size value and the member on which self-tuning memory is to be disabled.

An ALTER BUFFERPOOL statement that specifies the size of a buffer pool on a particular member will create an exception entry (or update an existing entry) for that buffer pool in the SYSCAT.BUFFERPOOLEXCEPTIONS 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 used for self tuning:

  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 this member to the default.
  3. Enable self tuning for this buffer pool by issuing another ALTER BUFFERPOOL statement, setting the buffer pool size to AUTOMATIC.