DB2 10.5 for Linux, UNIX, and Windows

Determining which memory consumers are enabled for self tuning

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:
- To retrieve a list of the buffer pools that are enabled for self tuning from the command line, use the following query:
```
SELECT BPNAME, NPAGES FROM SYSCAT.BUFFERPOOLS
```
  When self tuning is enabled for a buffer pool, the NPAGES field in the SYSCAT.BUFFERPOOLS view for that particular buffer pool is set to -2. When self tuning is disabled, the NPAGES field is set to the current size of the buffer pool.
- To determine the current size of buffer pools that are enabled for self tuning, use the GET SNAPSHOT command and examine the current size of the buffer pools (the value of the bp_cur_buffsz monitor element):
```
GET SNAPSHOT FOR BUFFERPOOLS ON database-alias
```
  An ALTER BUFFERPOOL statement that specifies the size of a buffer pool on a particular database partition creates an exception entry (or updates an existing entry) for that buffer pool in the SYSCAT.BUFFERPOOLDBPARTITIONS catalog view. If an exception entry for a buffer pool exists, that buffer pool does not participate in self-tuning operations when the default buffer pool size is set to AUTOMATIC.