You can view the self-tuning memory settings that are controlled
by configuration parameters or that apply to buffer pools.
About this task
It is important to note that responsiveness of the memory
tuner is limited by the time required to resize a memory consumer.
For example, reducing the size of a buffer pool can be a lengthy process,
and the performance benefits of trading buffer pool memory for sort
memory might not be immediately realized.
Procedure
- To view the settings for configuration parameters, use
one of the following methods:
- Use the GET DATABASE CONFIGURATION command,
specifying the SHOW DETAIL parameter.
The
memory consumers that can be enabled for self tuning are grouped together
in the output as follows:
Description Parameter Current Value Delayed Value
--------------------------------------------------------------------------------------------
Self tuning memory (SELF_TUNING_MEM) = ON (Active) ON
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(37200) AUTOMATIC(37200)
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(7456) AUTOMATIC(7456)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(98) AUTOMATIC(98)
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(5600) AUTOMATIC(5600)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(5000) AUTOMATIC(5000)
Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(256) AUTOMATIC(256)
- Use the db2CfgGet API.
The following
values are returned:
SQLF_OFF 0
SQLF_ON_ACTIVE 2
SQLF_ON_INACTIVE 3
SQLF_ON_ACTIVE indicates
that self tuning is both enabled and active, whereas
SQLF_ON_INACTIVE indicates
that self tuning is enabled but currently inactive.
- To view the self-tuning settings for buffer pools, use
one of the following methods: