Alter Buffer Pool -- Fields and controls

Assisting parallel sequential threshold - VPXPSEQT
Deferred write threshold - DWQT
Discard hiperpool data - CASTOUT
Hiperpool sequential steal threshold - HPSEQT
Hiperpool size - HPSIZE
Parallel sequential threshold - VPPSEQT
Sequential steal threshold - VPSEQT
Vertical deferred write - VDWQT
Virtual buffer pool size - VPSIZE

 Virtual buffer pool size (VPSIZE):

The integer you specify for the virtual buffer pool size is the number of buffers to allocate to the active virtual buffer pool. This value can be from 0 to 400000 for 4KB page buffer pools, except BP0. For BP0, the minimum is 56. 32KB buffer pools can be from 0 to 50000. The total VPSIZE for all buffer pools is limited to 1.6 GB. In addition, the amount of available real and virtual storage can also limit the amount of virtual buffer pool storage that DB2 for OS/390 can acquire.

For releases later than DB2 for OS/390 Version 5, there are also 8- and 16KB buffer pools. The ranges are:

 Hiperpool size (HPSIZE):

This value specifies the number of buffers for a hiperpool. If you are altering a buffer pool, and the buffer pool is active at the time the command is issued, the hiperpool is created, expanded, contracted, or deleted depending on the new HPSIZE. The ranges are: The total HPSIZE for all buffer pools is limited to 8GB. In addition, the amount of available expanded or real storage can also limit the amount of buffer pool storage that DB2 can acquire.

 Sequential steal threshold for the virtual buffer pool (VPSEQT):

This value is expressed as a percentage of the total virtual buffer pool size. Valid values range from 0 to 100. This threshold affects the allocation of buffers in the virtual buffer pool to page read requests that are part of a sequential access pattern.

When the value is 0, sequentially accessed pages are not kept in the virtual buffer pool after being released by the accessing agent. This also disables prefetch.

When the value is 100, DB2 for OS/390 does not prefer reusing sequential buffers over using non-sequential buffers.

 Parallel sequential threshold for the virtual buffer pool (VPPSEQT):

This value determines how much of the virtual buffer pool is used for parallel processing operations. It is expressed as a percentage of the sequential steal threshold. Valid values range from 0 to 100. The initial value is 50.

When the value is 0, parallel processing operations are disabled.

 Assisting parallel sequential threshold for the virtual buffer pool (VPXPSEQT):

This threshold determines the portion of the virtual buffer pool that is used for processing queries that originate on other members of the data sharing group. This value is valid only for systems using data sharing. Otherwise, it is ignored.

The value is expressed as a percentage of the parallel sequential threshold (VPPSEQT). Whenever the sequential steal threshold or the parallel sequential threshold is altered, it directly affects the portion or buffer resources dedicated to "assistant" parallel operations. The valid values range from 0 to 100. The initial value is 0.

When the value is 0, this buffer pool cannot be used to assist another DB2 with parallel processing.

 Hiperpool sequential steal threshold (HPSEQT):

This value is expressed as a percentage of the total hiperpool size. Valid values range from 0 to 100. The default value is 80. This threshold affects the allocation of hiperpool buffers when casting out sequentially accessed pages from the virtual pool.

When the value is 0, sequentially accessed pages are not cast out to the hiperpool.

When the value is 100, DB2 for OS/390 does not prefer reusing sequential buffers over using non-sequential buffers.

 Deferred write threshold (DWQT):

This value is expressed as percentage of the total virtual buffer pool size. Valid values range from 0 to 90. This threshold determines when deferred writes begin, based on the number of unavailable (non-stealable) buffers. When the count of non-stealable buffers exceeds the threshold, deferred writes begin. The initial default value is 50.

 Vertical deferred write threshold (VDWQT):

This value is expressed as a percentage of the total virtual buffer pool size. Valid values range from 0 to 90. This threshold determines when deferred writes begin, based on the number of updated pages for a given data set. When a data set's updated buffer count exceeds the threshold, deferred writes begin for that data set.

Because any buffers that count toward VDWQT also count toward DWQT, setting VDWQT higher than DWQT has no effect: DWQT is reached first, write operations are scheduled, and VDWQT is never reached. Therefore, altering the buffer pool does not allow you to set VDWQT to a value greater than DWQT.

 Discard hiperpool data (CASTOUT):

The hiperspaces used to back the hiperpool. The default value is YES for all hiperpools.

When CASTOUT is YES, MVS discards the data cached in the hiperpool when a shortage of expanded storage arises. When this data is discarded, hiperspace backing expanded storage pages is released.

Selecting NO tells MVS to assign a high priority to keeping the data cached in the hiperpool. This can severely limit the availability of expanded storage to other processes on the system. So, you should select NO only for buffer pools associated with databases for which response time is critical.