Self-tuning memory in a DB2 pureScale environment

When self-tuning memory is enabled in a DB2® pureScale® environment, the tuning member monitors the memory configuration and propagates any configuration changes to all other members.

When self-tuning memory is enabled in a DB2 pureScale environment, there is a single member (known as the tuning member) that monitors the memory configuration and propagates any configuration changes to all other members to maintain a consistent configuration across all the members in the instance.

In a DB2 pureScale environment, when the tuning member is specified as -1, the tuning member is randomly selected every time the database is activated. Also, if the member on which the tuner is running deactivates, the tuner will automatically start on another member that is currently able to run the tuner. In order for a member to run the tuner, the database must be active on that member and that member must have self_tuning_mem set to ON.

• To determine which member is currently specified as the tuning member, call the ADMIN_CMD procedure as follows:

  CALL SYSPROC.ADMIN_CMD('get stmm tuning member')

• To change the tuning member, call the ADMIN_CMD procedure as follows:

  CALL SYSPROC.ADMIN_CMD('update stmm tuning member membernum')

  The tuning member can be switched between having the tuning member randomly selected and using an explicitly specified tuning member as required.

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.

Enabling self-tuning memory in nonuniform environments

The workload that is run on each member is expected to have similar memory requirements. The memory requirements can be skewed across members, for example, if resource-intensive sorts are only performed on one member or, if some members are associated with different hardware and more available memory than others. Self tuning memory can still be activated on some members. To take advantage of self-tuning memory in heterogeneous memory environments, identify a set of members that have similar memory requirements and activate the self tuning memory on those members. The self-tuning memory can then be disabled on the remaining members and their memory can be configured manually.