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
V10.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 activeValue 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:
- Disable self tuning for this buffer pool by issuing an ALTER BUFFERPOOL
statement, setting the buffer pool size to a specific value.
- Issue another ALTER BUFFERPOOL statement to set the size of the
buffer pool on this member to
the default.
- Enable self tuning for this buffer pool by issuing another ALTER
BUFFERPOOL statement, setting the buffer pool size to AUTOMATIC.