DB2 Version 9.7 for Linux, UNIX, and Windows

Enabling self-tuning memory

Self-tuning memory simplifies the task of memory configuration by automatically setting values for memory configuration parameters and sizing buffer pools.

About this task

When enabled, the memory tuner dynamically distributes available memory resources between several memory consumers, including buffer pools, locking memory, package cache, and sort memory.

Procedure

Enable self-tuning memory for the database by setting the self_tuning_mem database configuration parameter to ON using the UPDATE DATABASE CONFIGURATION command or the db2CfgSet API.
To enable the self tuning of memory areas that are controlled by memory configuration parameters, set the relevant configuration parameters to AUTOMATIC using the UPDATE DATABASE CONFIGURATION command or the db2CfgSet API.
To enable the self tuning of a buffer pool, set the buffer pool size to AUTOMATIC using the CREATE BUFFERPOOL statement or the ALTER BUFFERPOOL statement. In a partitioned database environment, that buffer pool should not have any entries in SYSCAT.BUFFERPOOLDBPARTITIONS.

Results

Note:

Because self-tuned memory is distributed between different memory consumers, at least two memory areas must be concurrently enabled for self tuning at any given time; for example, locking memory and database shared memory. The memory tuner actively tunes memory on the system (the value of the self_tuning_mem database configuration parameter is ON) when one of the following conditions is true:
- One configuration parameter or buffer pool size is set to AUTOMATIC, and the database_memory database configuration parameter is set to either a numeric value or to AUTOMATIC
- Any two of locklist, sheapthres_shr, pckcachesz, or buffer pool size is set to AUTOMATIC
- The sortheap database configuration parameter is set to AUTOMATIC
The value of the locklist database configuration parameter is tuned together with the maxlocks database configuration parameter. Disabling self tuning of the locklist parameter automatically disables self tuning of the maxlocks parameter, and enabling self tuning of the locklist parameter automatically enables self tuning of the maxlocks parameter.
Automatic tuning of sortheap or the sheapthres_shr database configuration parameter is allowed only when the database manager configuration parameter sheapthres is set to 0.
The value of sortheap is tuned together with sheapthres_shr. Disabling self tuning of the sortheap parameter automatically disables self tuning of the sheapthres_shr parameter, and enabling self tuning of the sheapthres_shr parameter automatically enables self tuning of the sortheap parameter.
Self-tuning memory runs only on the high availability disaster recovery (HADR) primary server. When self-tuning memory is activated on an HADR system, it will never run on the secondary server, and it runs on the primary server only if the configuration is set properly. If the HADR database roles are switched, self-tuning memory operations will also switch so that they run on the new primary server. After the primary database starts, or the standby database converts to a primary database through takeover, the self-tuning memory manager (STMM) engine dispatchable unit (EDU) might not start until the first client connects.