-ALTER BUFFERPOOL command (Db2)

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

Abbreviation: -ALT BPOOL

Environment for -ALTER BUFFERPOOL

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 for -ALTER BUFFERPOOL

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 using primary and secondary authorization IDs. Start of changeA logged-on z/OS user ID must be defined in RACF or a similar security server.End of change

Syntax for -ALTER BUFFERPOOL

Read syntax diagramSkip visual syntax diagramALTER BUFFERPOOL( bpname)VPSIZE( integer)VPSIZEMIN(*integer)VPSIZEMAX(*integer)FRAMESIZE(4K1M2G)VPSEQT( integer)VPPSEQT( integer)DWQT( integer)VDWQT( integer1, integer2)PGSTEAL(LRUFIFONONE)PGFIX(NOYES)AUTOSIZE(NOYES)SPSIZE( integer)SPSEQT( integer)

Option descriptions for -ALTER BUFFERPOOL

( 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.

Start of changeThe value of integer can range 0–4000000000 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.End of change

Start of changeDb2 limits the sum of VPSIZE and SPSIZE for all buffer pools to 16 TB. In addition, Db2 limits the sum of buffer pool storage and simulated buffer pool storage to the smaller of the following values:End of change

Start of change
  • Twice the available real storage in the z/OS system
  • 16TB
End of change

When you set VPSIZE to 0 for an active buffer pool, the Db2 database manager 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. In addition, when you set VPSIZE to 0, and a simulated buffer pool is allocated, the database manager deletes the simulated buffer pool.

VPSIZEMIN (integer|*)
Sets the minimum size for the buffer pool. Possible values are:
*

Indicates that Db2 sets the minimum value to 75% of the current size. * is the default.

integer
Specifies the minimum number of buffers to allocate to the active buffer pool when AUTOSIZE(YES) is in effect. The following rules apply to integer:
  • For buffer pools other than BP0, BP8K0, BP16K0, or BP32K, the value of integer for VPSIZEMIN must be less than or equal to the value of integer for VPSIZE. The value of VPSIZEMIN cannot be 0.
  • For buffer pools BP0, BP8K0, BP16K0, or BP32K, valid ranges for integer are:
    Buffer pool page size Range for integer
    4 KB Start of change2000–4000000000End of change
    8 KB Start of change1000–2000000000End of change
    16 KB Start of change500–1000000000End of change
    32 KB Start of change250–500000000End of change

Abbreviation: VPMIN

VPSIZEMAX (integer|*)
Sets the maximum size for the buffer pool. Possible values are:
*

Start of changeIndicates that Db2 sets the maximum value to 125% of the current size. Each time that the buffer pool is allocated or reallocated, if automatic buffer pool management is enabled and the buffer pool size increases, the maximum size of the buffer pool is 125% of the new, larger buffer pool size.End of change

* is the default.

integer
Specifies the maximum number of buffers to allocate to the active buffer pool when AUTOSIZE(YES) is in effect. The following rules apply to integer:
  • For buffer pools other than BP0, BP8K0, BP16K0, or BP32K, the value of integer for VPSIZEMAX must be greater than or equal to the value of integer for VPSIZE. The value of VPSIZEMAX cannot be 0.
  • For buffer pools BP0, BP8K0, BP16K0, or BP32K, integer has the following range:
    Buffer pool page size Range for integer
    4 KB Start of change2000–4000000000End of change
    8 KB Start of change1000–2000000000End of change
    16 KB Start of change500–1000000000End of change
    32 KB Start of change250–500000000End of change

Abbreviation: VPMAX

FRAMESIZE(4K|1M|2G)
Sets the frame size for the buffer pool. Possible values are 2G, 1M, or 4K.

Start of changeIf you issue the ALTER BUFFERPOOL command with the FRAMESIZE option to modify the frame size for a buffer pool, the change is pending, and the buffer pool becomes fixed only at the next allocation.End of change

If FRAMESIZE is 1M, and PGFIX is NO, Db2 uses 4 KB frames.

The frame size can be changed to 2 GB only if 2 GB of real memory is available. In addition, 2 GB of buffer storage must be available. If 2 GB frames cannot be allocated, Db2 allocates 1 MB frames.

PGSTEAL(NONE) and FRAMESIZE(2G) are not compatible in Db2 12. If you specify these options together, Db2 issues message DSNB549I and uses the PGSTEAL(LRU) algorithm until the next allocation of the buffer pool. However, PGSTEAL(NONE) is recorded in the BSDS. To use PGSTEAL(NONE), specify FRAMESIZE(1M) or FRAMESIZE(4K). For more information, see Changed behavior for PGSTEAL(NONE) buffer pools in Storage release incompatibilities in Db2 12.

The following examples demonstrate the number of 2 GB frames that are allocated for various VPSIZE values and 4 KB buffer pools. This information is subject to change, and is intended only to give an approximate idea of the way that frames are allocated.

VPSIZE Number of 2 GB frames allocated Comment
100 0 The amount of storage that is available is less than 2 GB, and is less than the internally defined limit for rounding up to 2 GB.
498688 1 The amount of storage that is available is less than 2 GB, but is within an internally defined limit for rounding up to 2 GB.
524288 1 The amount of storage that is available is exactly 2 GB.
549888 1 The amount of storage that is available is greater than 2 GB, and less than an internally defined limit for rounding up to 4 GB.

Abbreviation: FRAME

VPSEQT ( integer )
Changes the sequential steal threshold for the buffer pool.

This threshold is a percentage of the buffer pool that might be occupied by sequentially accessed pages. The pages can be in any state: updated, in-use, or available.

The getpage operations for a transaction are classified as sequential if the transaction attempts to prefetch the pages. The buffers that contain those pages are governed by VPSEQT, which makes them more likely to be stolen or demoted out of the buffer pool than buffers that are not governed by VPSEQT. A random buffer can never be reclassified as sequential, but a sequential buffer that is touched by a random getpage operation is classified as random.

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. The value of integer must be 0–100, inclusive. The default value is 80.

The sequential steal threshold:

  • Prevents a sequential scan, a disorganized index scan, or certain other prefetch operations from overwhelming the buffer pool
  • Helps reduce synchronous I/O by favoring random pages
  • Affects the allocation of buffers and least-recently used (LRU) algorithms

If PGSTEAL(LRU) is specified for a buffer pool, and the number of sequential buffers in the pool is less than the VPSEQT value, Db2 steals the oldest buffer. When the number of sequential buffers in the pool is greater than the VPSEQT value, Db2 steals the oldest sequential buffer.

Buffers that are classified as sequential are stolen more quickly than buffers that are classified as random because VPSEQT limits the number of sequential buffers. Therefore, Db2 uses random buffers more than sequential buffers to reduce the frequency of synchronous I/O. As a result, Db2 might use more asynchronous prefetch I/O. Db2 classifies a buffer as sequential when a buffer is allocated for one of the following purposes:

  • For use by Db2 prefetch
  • For reading a LOB

    Buffers for reading LOBs are classified as sequential because LOB pages are less likely to be referenced again than other types of pages.

  • When a Db2 utility is writing a new Db2 data set sequentially

Db2 might also reclassify a sequential buffer as a random buffer if a random getpage operation touches a sequential buffer.

Setting VPSEQT to 0 disables prefetch. Any sequentially accessed pages are discarded when the number of available buffers is exceeded by the number of objects that are accessed. You can set VPSEQT to 0 to avoid unnecessary prefetch scheduling when the pages are already in the buffer pool, such as in the case of in-memory indexes or data. However, setting VPSEQT to 0 might disable parallelism. You can achieve the same result, and use fewer system resources, by specifying PGSTEAL(NONE).

To avoid accelerated LRU demotion, increase the value of VPSEQT to 99 or 100. You might need to increase the value if it is likely that prefetched pages are referenced more than one time, especially if the additional references are random getpage operations.

To encourage faster LRU demotion of the sequential pages, lower the VPSEQT value. However, if VPSEQT × VPSIZE is less than 160 MB, sequential I/O performance is reduced because a smaller value lowers the amount of sequential prefetch and I/O that is used by queries and utilities.

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.

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

VPXPSEQT (integer)
Sysplex query parallelism is no longer supported. Specifying this parameter has no effect.
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.

Simulated buffer pools can be used only when PGSTEAL is set to LRU.

(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. Start of change Db2 implicitly creates an overflow area for pages that do not fit in the buffer pool. The overflow area is created when the buffer pool is allocated. The size of the overflow area is based on the VPSIZE value for the buffer pool. The overflow area is generally 10 percent of the VPSIZE value in the range of 50–6400 buffers. Db2 issues message DSNB604I message when the overflow area is used. Page stealing can occur in the overflow area. LRU chains are maintained for the overflow area, but buffer stealing in the overflow area uses the FIFO page-stealing algorithm. End of change

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. Start of changeIf VPSEQT=100, Db2 automatically does a prefetch of the object into the buffer pool, which can reduce the synchronous read I/O activity after these transitions occur.End of change

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.

If PGFIX is set to NO, Db2 uses a 4 KB frame size.

(YES)
Specifies that the buffer pool is 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.

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 2.1 or later, automatic buffer pool management increases or decreases the buffer pool sizes. For z/OS 2.1, automatic buffer pool management only increases the buffer pool sizes, and you might need to manually reduce the size of a buffer pool that becomes too large.

Start of changeWhen PGSTEAL(NONE) is used, AUTOSIZE(YES) is ignored. However, the AUTOSIZE attribute is saved. If the PGSTEAL attribute is later changed to something other than NONE, the AUTOSIZE(YES) attribute takes effect again when the buffer pool is reallocated with the new PGSTEAL attribute.End of change

SPSIZE ( integer )
Start of changeCauses Db2 to simulate buffer pool behavior when the buffer pool size is increased by integer. End of change

Start of changeThe value of integer specifies the number of buffers that are to be added to VPSIZE for the simulation. For example, if VPSIZE is currently 5000 buffers, set SPSIZE to 1000 to see what happens if you increase the buffer pool size by 20%.End of change

Start of changeDb2 limits the sum of VPSIZE and SPSIZE for all buffer pools to 16 TB. In addition, Db2 limits the sum of buffer pool storage and simulated buffer pool storage to the smaller of the following values:End of change

Start of change
  • Twice the available real storage in the z/OS system
  • 16TB
End of change

The minimum value of integer depends on the buffer pool page size. The maximum value of integer depends on the buffer pool page size and VPSIZE.

The following table shows the minimum and maximum values for SPSIZE:

Buffer pool page size (KB) Minimum value for SPSIZE Maximum value for SPSIZE
4 200 Start of change4000000000 - VPSIZEEnd of change
8 100 Start of change2000000000 - VPSIZEEnd of change
16 50 Start of change1000000000 - VPSIZEEnd of change
32 25 Start of change500000000 - VPSIZEEnd of change

If you specify SPSIZE(0) for a simulated buffer pool, Db2 quiesces all activity for the simulated buffer pool, and then deletes the simulated buffer pool. If a simulated buffer pool is active, and you specify a value for SPSIZE that is smaller than the current size but greater than 0, Db2 deletes the current simulated buffer pool and allocates a new simulated buffer pool with the smaller size.

SPSEQT (integer)
Changes the sequential steal threshold for the simulated buffer pool.

This threshold is the percentage of the total simulated buffer pool size that is used for sequentially accessed pages. In the simulated buffer pool, a simulated buffer is classified as sequential if the page was in a sequential buffer in the virtual buffer pool when the page was logically moved into the simulated buffer pool from the virtual buffer pool.

The value must be in the range 0–100. The initial default value is the value of VPSEQT, which is the sequential steal threshold for the virtual buffer pool. The initial default value is set when the SPSIZE is altered to a value greater than zero, if SPSEQT was never specified before.

When Db2 steals a buffer in the simulated buffer pool, if the percentage of sequential buffers to total buffers in the simulated buffer pool is greater than the SPSEQT value, Db2 steals the oldest sequential buffer. Otherwise, Db2 steals the oldest buffer.

Usage notes for -ALTER BUFFERPOOL

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 changeTo 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.
End of change
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
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)
Requirements for simulated buffer pools
You can allocate simulated buffer pools to determine the best buffer pool sizes for your database system. When you issue ALTER BUFFERPOOL to create simulated buffer pools, the following conditions must be met:
  • SPSIZE is greater than 0.
  • The virtual buffer pool is allocated. This means that you set VPSIZE to a value greater than 0 when you previously issued ALTER BUFFERPOOL, or you set VPSIZE to a value greater than 0 when you issue ALTER BUFFERPOOL to create the simulated buffer pool.
  • PGSTEAL must be LRU.

    If the value of SPSIZE is greater than 0, and you set PGSTEAL to NONE or FIFO, the Db2 database manager sets SPSIZE to 0. If the simulated buffer pool is already allocated, the database manager deletes it. If the value of PGSTEAL is NONE or FIFO, and you issue ALTER BUFFERPOOL to set the SPSIZE value from 0 to a value greater than 0, the SPSIZE value is not changed.

Start of changeRestriction on changing VPSIZE during buffer pool simulationEnd of change
Start of changeDo not set VPSIZE to 0 while you are doing buffer pool simulation. Setting VPSIZE to 0 deallocates real and simulated buffer pools, even if the SPSIZE value is greater than 0.End of change
Prerequisite for FRAMESIZE(2G)
Before you can use 2 GB page frames, you must configure a 2 GB frame area after the initial program load (IPL) of z/OS. To do that, you need to specify parameter keywords in the LFArea keyword in the active IEASYSxx member of SYS1.PARMLIB to specify the amount of real storage that is to be used for 1 MB and 2 GB pages.

Examples for -ALTER BUFFERPOOL

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 minimum and maximum buffer pool size
This command sets the minimum size of buffer pool BP32K to 1500, and the maximum size of buffer pool BP32K to 2500.
-ALTER BUFFERPOOL(BP32K) VPSIZEMIN(1500) VPSIZEMAX(2500)
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.