-ALTER BUFFERPOOL (DB2)

The DB2® command ALTER BUFFERPOOL alters attributes for active or inactive buffer pools. Altered values are used until altered again.

Abbreviation: -ALT BPOOL

Environment

This command can be issued from a z/OS® console, a DSN session under TSO, a DB2I panel (DB2 COMMANDS), an IMS™, or CICS® terminal, or a program using the instrumentation facility interface (IFI).

Data sharing scope: Member

Authorization

To issue this command, you must use a set of privileges for the process that includes one of the following authorities:

SYSOPR authority
SYSCTRL authority
SYSADM authority

DB2 commands that are issued from a logged-on z/OS console or TSO SDSF can be checked by DB2 authorization that uses primary and secondary authorization IDs.

Syntax

>>-ALTER BUFFERPOOL--(bpname)--+-----------------+-------------->
                               '-VPSIZE(integer)-'   

>--+-----------------+--+------------------+-------------------->
   '-VPSEQT(integer)-'  '-VPPSEQT(integer)-'   

>--+-------------------+--+---------------+--------------------->
   '-VPXPSEQT(integer)-'  '-DWQT(integer)-'   

>--+--------------------------+--------------------------------->
   '-VDWQT(integer1,integer2)-'   

>--+-------------------------------------------------+---------->
   '-PGSTEAL(-+-LRU------------------------------+-)-'   
              +-FIFO-----------------------------+       
              '-Start of changeNONEEnd of change-'       

>--+------------------+--+---------------------+---------------><
   |        .-NO--.   |  |           .-NO--.   |   
   '-PGFIX(-+-YES-+-)-'  '-AUTOSIZE(-+-YES-+-)-'

Option descriptions

( bpname )

Specifies the buffer pool to alter.

4 KB page buffer pools are named BP0 through BP49
8 KB page buffer pools are named BP8K0 through BP8K9
16 KB page buffer pools are named BP16K0 through BP16K9
32 KB page buffer pools are named BP32K through BP32K9

VPSIZE ( integer )

Changes the buffer pool size.

The value of integer specifies the number of buffers to allocate to the active buffer pool.

The value of integer can range 0 - 250000000 for 4 KB page buffer pools other than BP0. For BP0, the minimum value is 2000. For 8 KB page buffer pools, the minimum value is 1000. For 16 KB page buffer pools, the minimum value is 500. For 32 KB page buffer pools, the minimum value is 250.

DB2 limits the total VPSIZE for all buffer pools to 1 TB. In addition, DB2 limits the amount of buffer pool storage to twice the available real storage for the z/OS image.

If you specify VPSIZE as 0 for an active buffer pool, DB2 quiesces all current database access and update activities for that buffer pool and then deletes the buffer pool. Subsequent attempts to use table spaces or indexes that are assigned to that buffer pool fail.

VPSEQT ( integer )

Changes the sequential steal threshold for the buffer pool.

The value of integer specifies the sequential steal threshold for the buffer pool. This value is expressed as a percentage of the total buffer pool size, and valid values range 0 - 100. This threshold affects the allocation of buffers in the buffer pool to page read requests that are part of a sequential access pattern. This includes pages that are prefetched. If the number of buffers that contain sequentially accessed pages exceeds the threshold, a sequential request attempts to reuse one of those buffers rather than a buffer that contains a non-sequentially accessed page. The initial default value is 80.

When VPSEQT=0, sequentially accessed pages are not kept in the buffer pool after the accessing agent releases them. Also, prefetch is disabled.

When VPSEQT=100, DB2 does not prefer reusing sequential buffers over using non-sequential buffers.

VPPSEQT (integer)

Changes the parallel sequential threshold for the buffer pool. This threshold determines how much of the buffer pool is used for parallel processing operations.

The value of integer specifies the parallel sequential threshold for the buffer pool. This value is expressed as a percentage of the sequential steal threshold, and valid values range 0 - 100. The initial default value is 50.

Start of change When VPPSEQT=0, parallel processing operations and prefetch operations that are triggered by index I/O parallelism are disabled. End of change

VPXPSEQT (integer)

Changes the assisting parallel sequential threshold for the buffer pool. This threshold determines the portion of the buffer pool that is used for processing queries that originate on other members of the data sharing group. This option is valid and effective only when DB2 is in data sharing mode; it is ignored when DB2 is not in data sharing mode.

The value of integer specifies the assisting parallel sequential threshold for the buffer pool. The value of integer is expressed as a percentage of the parallel sequential threshold (VPPSEQT). Altering the sequential steal threshold or the parallel sequential threshold directly affects the portion of buffer resources that are dedicated to assistant parallel operations. The valid values range 0 - 100. The initial default value is 0.

When VPXPSEQT=0, this buffer pool cannot be used to assist another DB2 with parallel processing.

DWQT (integer)

Changes the buffer pool's deferred write threshold.

The value of integer specifies the deferred write threshold for the buffer pool. This value is expressed as a percentage of the total buffer pool size, and valid values range 0 - 90. This threshold determines when deferred writes begin, based on the number of unavailable buffers. When the count of unavailable buffers exceeds the threshold, deferred writes begin. The initial default value is 30 percent.

VDWQT (integer-1,integer-2)

Changes the buffer pool's vertical deferred write threshold.

The value of integer1 specifies the vertical deferred write threshold for the buffer pool. integer1 is expressed as a percentage of the total buffer pool size, and valid values range 0 - 90.

This threshold determines when deferred writes begin, based on the number of updated pages for a particular data set. Deferred writes begin for that data set when the count of updated buffers for a data set exceeds the threshold. This threshold can be overridden for page sets accessed by DB2 utilities. It must be less than or equal to the value specified for the DWQT option.

The default value is 5 percent. A value of 0 indicates that the deferred write of 32 pages begins when the updated buffer count for the data set reaches 40.

The value of integer-2 specifies the vertical deferred write threshold for the buffer pool. integer-2 is expressed as an absolute number of buffers. You can use integer2 when you want a relatively low threshold value for a large buffer pool, but integer-1 cannot provide a fine enough granularity between integer-1 values of 0 and 1. The value of integer-2 applies only when the value of integer-1 is 0. DB2 ignores a value that is specified for integer-2 if the value specified for integer-1 is non-zero. The value of integer-2 can range 0 - 9999. The default value is 0.

If the value of integer-1 is 0 and integer-2 is a non-zero value, DB2 uses the value that is specified for integer-2 to determine the threshold. If both values are 0, the integer-1 value of 0 is used as the threshold.

PGSTEAL

Specifies the page-stealing algorithm that DB2 uses for the buffer pool.

The initial default is PGSTEAL(LRU).

(LRU): Specifies that the buffer pool buffers are managed according to the rules of a least recently used (LRU) algorithm.
(FIFO): Specifies that the buffer pool buffers are managed according to the rules of a first-in-first-out (FIFO) algorithm. This option reduces the cost of maintaining the information about which buffers are least-recently used.
(NONE): Specifies that no page stealing occurs if the buffer pool is large enough to contain all assigned open objects. Under this option, DB2 pre-loads the buffer pools when the objects are opened. If the buffer pool is not large enough to contain the object, page-stealing might occur. Pages that do not fit within the size of the buffer pool are managed by the FIFO algorithm.
In a data sharing environment, after a page set or partition becomes non-GBP dependent and GBP dependent again, high levels of synchronous read I/O activity might occur. Resolving synchronous read I/O problems in data sharing environments offers suggestions for how to resolve this performance problem.

PGFIX

Specifies whether the buffer pool is fixed in real storage when it is used.

(NO): Specifies that the buffer pool is not fixed in real storage. Page buffers are fixed and unfixed in real storage across each I/O and group buffer pool operation.
This value is the default.
(YES): Specifies that the buffer pool is fixed in real storage. Page buffers are fixed when they are first used after the buffer pool is allocated or expanded.

AUTOSIZE

Specifies whether the buffer pool adjustment is turned on or off.

(NO): Specifies that DB2 does not use Workload Manager (WLM) services for automatic buffer pool size adjustment.
This value is the default.
(YES): Specifies that DB2 uses WLM services, if available, to automatically adjust the buffer pool size as appropriate.
For z/OS V2R1 or later, automatic buffer pool management increases or decreases the buffer pool sizes. For versions of z/OS below z/OS V2R1, automatic buffer pool management only increases the buffer pool sizes. If you use one of those versions of z/OS, you might need to manually reduce the size of a buffer pool that becomes too large.

Usage notes

The following description contains additional information about how to use the ALTER BUFFERPOOL command.

Changing several buffer pool attributes

A failure to modify one buffer pool attribute has no effect on other modifications that are requested in the same command.

Contracting an active buffer pool

If you use ALTER BUFFERPOOL to contract the size of an active buffer pool, DB2 contracts the pool by marking active buffers as "to be deleted," which means that they are not reusable to satisfy other page requests. However, the virtual storage might not be freed immediately. Determine the status of the buffer pool by issuing the DISPLAY BUFFERPOOL command.

Important: Start of change

To avoid a major performance impact when you contract an active buffer pool, follow these guidelines:

Do not lower the size of an active buffer pool by a large amount when there is a significant amount of buffer pool activity in the subsystem. Issue DISPLAY BUFFERPOOL to determine the amount of activity before you attempt to contract the buffer pool.
If you need to do a very large buffer pool contraction, issue ALTER BUFFERPOOL several times to do multiple smaller contractions, instead of one large contraction.

Deleting an active buffer pool

If you use ALTER BUFFERPOOL to delete an active buffer pool (by specifying 0 for VPSIZE), DB2 issues a message to indicate that it is ready to explicitly delete this buffer pool. When DB2 accepts the delete buffer pool request, the buffer pool is marked as "delete pending". All current access to the buffer pool is quiesced, later access attempts fail with an error message, and all open page sets that refer to the buffer pool are closed.

Altering attributes that are stored in the BSDS

The buffer pool attributes that are stored in the BSDS cannot be changed offline.

Setting a buffer pool to be fixed in real storage

If you use the ALTER BUFFERPOOL command with the PGFIX option set to YES to fix a buffer pool in real storage, the change is pending and the buffer pool becomes fixed only at the next allocation.

In order to fix the buffer pool in real storage, issue the command ALTER BUFFERPOOL(bpname) PGFIX(YES). If the buffer pool that you specify for bpname is not currently allocated, the buffer pool becomes fixed in real storage when it is allocated. If the buffer pool that you specify for bpname is currently allocated, do one of the following procedures to fix the buffer pool in real storage:

If the buffer pool that you specify for bpname is not one of the buffer pools that is used for the DB2 catalog and directory (BP0, BP8K0, BP16K0, or BP32K):
1. Issue the ALTER BUFFERPOOL command with the VPSIZE option set to 0 to deallocate the buffer pool:
```
-ALTER BUFFERPOOL(bpname) VPSIZE(0)
```
2. Issue the ALTER BUFFERPOOL command with the VPSIZE and PGFIX options to change the buffer pool size and to use long-term page-fixing at the next allocation:
```
-ALTER BUFFERPOOL(bpname) VPSIZE(vpsize) PGFIX(YES)
```
If the buffer pool that you specify for bpname is one of the buffer pools that is used for the DB2 catalog and directory (BP0, BP8K0, BP16K0, or BP32K):
1. Issue the ALTER BUFFERPOOL command with the PGFIX option to change the buffer pool to use long-term page fixing (the change is pending until the next allocation of the buffer pool):
```
-ALTER BUFFERPOOL(bpname) PGFIX(YES)
```
2. Issue the STOP DATABASE command or the STOP DB2 command to deallocate the buffer pool
3. Issue the START DATABASE command or the START DB2 command to reallocate the buffer pool (depending on which command you used to deallocate the buffer pool)

Relating VPPSEQT and VPXPSEQT

The following table explains how the two parallel sequential thresholds, VPPSEQT for parallel sequential and VPXPSEQT for assisting parallel sequential threshold, are related. VPXPSEQT is a percentage of VPPSEQT, which is itself a portion of VPSEQT. Multiply VPXPSEQT by VPPSEQT to obtain the total amount of the buffer pool that can be used to assist another DB2 subsystem with parallel processing. In addition, VPPSEQT is affected by changing VPSIZE and VPSEQT; therefore, VPXPSEQT is also affected by VPSIZE and VPSEQT.

Table 1. Relationship between VPPSEQT and VPXPSEQT
If VPPSEQT is set to:	and VPXPSEQT is set to:	The percentage of the buffer pool available to assist Sysplex query parallelism equals:
50	50	25
50	100	50
100	50	50
Any value.	0	0
0	Any value.	0

Examples

Example: Setting the buffer pool size

This command sets the size of buffer pool BP0 to 2000.

-ALTER BUFFERPOOL(BP0) VPSIZE(2000)

Example: Setting the sequential steal threshold of a buffer pool

This command sets the sequential steal threshold of buffer pool BP0 to 75% of the buffer pool size.

-ALTER BUFFERPOOL(BP0) VPSEQT(75)

Example: Deleting a buffer pool

This command deletes buffer pool BP1.

-ALTER BUFFERPOOL(BP1) VPSIZE(0)

Use this option carefully because specifying a 0 size for an active buffer pool causes DB2 to quiesce all current database access. All subsequent requests to open page sets fail.