DB2 Version 9.7 for Linux, UNIX, and Windows

self_tuning_mem- Self-tuning memory configuration parameter

This parameter determines whether the memory tuner will dynamically distribute available memory resources as required between memory consumers that are enabled for self-tuning.

Configuration type

Database

Parameter type

Configurable Online

Propagation class

Immediate

Default [range]

Single-database partition environments: ON [ON; OFF]
Multi-database partition environments: OFF [ON; OFF]

In a database that is upgraded from an earlier version, self_tuning_mem will be set to OFF.

Note: The default value is subject to change by the DB2® Configuration Advisor after initial database creation.

Because memory is being traded between memory consumers, there must be at least two memory consumers enabled for self-tuning in order for the memory tuner to be active. When self_tuning_mem is set to ON, but there are less than two memory consumers enabled for self-tuning, the memory tuner is inactive. (The exception to this is the sort heap memory area, which can be tuned regardless of whether other memory consumers are enabled for self-tuning or not.)

This parameter is ON by default in single database partition environments. In multi-database partition environments, it is OFF by default.

The memory consumers that can be enabled for self-tuning include:

Buffer pools (controlled by the size parameter of the ALTER BUFFERPOOL and CREATE BUFFERPOOL statements)
Package cache (controlled by the pckcachesz configuration parameter)
Lock List ( controlled by the locklist and maxlocks configuration parameters)
Sort heap (controlled by the sheapthres_shr and sortheap configuration parameters)
Database shared memory (controlled by the database_memory configuration parameter)

To view the current setting for this parameter, use the GET DATABASE CONFIGURATION command specifying the SHOW DETAIL parameter. The possible settings returned for this parameter are:

Self Tuning Memory          (SELF_TUNING_MEM) = OFF
Self Tuning Memory          (SELF_TUNING_MEM) = ON (Active)
Self Tuning Memory          (SELF_TUNING_MEM) = ON (Inactive)
Self Tuning Memory          (SELF_TUNING_MEM) = ON

The following values indicate:

ON (Active) - the memory tuner is actively tuning the memory on the system
ON (Inactive) - that although the parameter is set ON, self-tuning is not occurring because there are less than two memory consumers enabled for self-tuning, or the database or instance is in quiesce mode
ON without (Active) or (Inactive) - from a query without the SHOW DETAIL option, or without a database connection.

In partitioned environments, the self_tuning_mem configuration parameter will only show ON (Active) for the database partition on which the tuner is running. On all other nodes self_tuning_mem will show ON (Inactive). As a result, to determine if the memory tuner is active in a partitioned database, you must check the self_tuning_mem parameter on all database partitions.

If you have upgraded to DB2 Version 9 from an earlier version of DB2 and you plan to use the self-tuning memory feature, you should configure the following health indicators to disable threshold or state checking:

Shared Sort Memory Utilization - db.sort_shrmem_util
Percentage of sorts that overflowed - db.spilled_sorts
Long Term Shared Sort Memory Utilization - db.max_sort_shrmem_util
Lock List Utilization - db.locklist_util
Lock Escalation Rate - db.lock_escal_rate
Package Cache Hit Ratio - db.pkgcache_hitratio

One of the objectives of the self-tuning memory feature is to avoid having memory allocated to a memory consumer when it is not immediately required. Therefore, utilization of the memory allocated to a memory consumer might approach 100% before more memory is allocated. By disabling these health indicators, you will avoid unnecessary alerts triggered by the high rate of memory utilization by a memory consumer.

Instances created in DB2 Version 9 will have these health indicators disabled by default.