Buffer Pool Parameters

This topic shows detailed information about System Parameters - Buffer Pool Parameters.

Normally, buffer pool information is reported once for each buffer pool if the attributes remain the same over the reporting period.

Buffer pool attributes can be changed while DB2 is active using the DB2 ALTER BUFFERPOOL command. If the performance trace class 10 is active, the event is recorded in the system parameters report.

When an ALTER BUFFERPOOL command is recorded, the status of the buffer pool before and after the command is shown.

BUFFER POOL PARAMETERS
---------------------------------------------------------------------------
TIMESTAMP  11/12/14 12:17:49.66  VPOOL SIZE (PAGES)                    5000
BUFFER POOL ID              BP0  VPOOL SEQ THRESH                        80
                                 HORIZ DEFER WRITE THRESH                30
                                 VERT DEFER WRITE THRESH (%)              5
                                 VERT DEFER WRITE THRESH (BUF)            0
                                 VPOOL PARALLEL SEQ THRESH               50
                                 ASSISTING PARALLEL SEQ THRESH            0
                                 PGFIX ATTRIBUTE                         NO
                                 PAGE STEAL METHOD                      LRU
                                 AUTOSIZE                                NO 
                                 FRAMESIZE                               4K
                                 VPOOL SIZE MIN                           0
                                 VPOOL SIZE MAX                           0
                                 SIM POOL SIZE                          300
                                 SIM POOL SEQ THRESH                     75

BUFFER POOL ID

Buffer pool name.

Field Name: QDBPNM

VPOOL SIZE (PAGES)

The size of the virtual buffer pool.

Old status taken from the DB2 field QW0201OP.

New status taken from the DB2 field QW0201NP.

Field Name: QDBPVPSZ

VPOOL SEQ THRESH

Virtual pool sequential threshold (VPSEQT). This threshold is a percentage of the virtual buffer pool that might be occupied by sequentially accessed pages. The pages can be in the state updated, in use, or available. Therefore, each page might count regarding exceeding any other buffer pool threshold.

The default value for VPSEQT is 80%. You can change this value to a value from 0% to 100% by using the VPSEQT option of the ALTER BUFFERPOOL command.

VPSEQT is checked before stealing a buffer for a sequentially accessed page instead of accessing the page in the virtual buffer pool. If the threshold is exceeded, DB2 tries to steal a buffer that holds a sequentially accessed page rather than one that holds a randomly accessed page.

If you set VPSEQT to 0%, sequential pages cannot occupy space in the virtual buffer pool. In this case, prefetch is disabled, and sequentially accessed pages are discarded when they are released. You can, however, set the value for HPSEQT to a value above zero and the value for VPSEQT to zero. If you set VPSEQT to 100%, sequential pages can monopolize the entire virtual buffer pool.

Field Name: QDBPVPSH

HORIZ DEFER WRITE THRESH

This threshold is a percentage of the virtual buffer pool that might be occupied by unavailable pages, including updated pages and pages in use.

The default value for QWQT is 30%. You can change this value to any value from 0% to 90% using the DWQT option of the ALTER BUFFERPOOL command.

DB2 checks QWQT when an update to a page is complete. If the percentage of unavailable pages in the virtual buffer pool exceeds QWQT, write operations are scheduled for up to 128 pages per data set to decrease the number of unavailable buffers to 10% below QWQT. For example, if QWQT is 50%, the number of unavailable buffers is reduced to 40%.

When the limit of QWQT is reached, data sets containing the oldest updated pages are written asynchronously. DB2 continues to write pages until the ratio goes below the QWQT.

Field Name: QDBPDWQT

VERT DEFER WRITE THRESH (%)

Vertical deferred write threshold (VDWQT). This threshold is similar to the deferred write threshold but it applies to the number of updated pages for one single page set in the buffer pool. If the percentage or number of updated pages for the data set exceeds the threshold, writes up to 128 pages are scheduled for that data set.

VDWQT can be specified in one of the following ways:

• As a percentage of the virtual buffer pool that might be occupied by updated pages from one single page set. The default value for this threshold is 5%. You can change the percentage to any value from 0% to 90%.
• As the total number of buffers in the virtual buffer pool that might be occupied by updated pages from one single page set. You can specify the number of buffers from 0 to 9999. If you want to use the number of buffers as your threshold, you must set the percentage threshold to 0.

Field Name: QDBPVDQT

VERT DEFER WRITE THRESH (BUF)

The vertical deferred write threshold (VDWQT), shown as the number of buffers in the virtual buffer pool that might be occupied by updated pages from a single page set.

Field Name: QDBPVDQB

VPOOL PARALLEL SEQ THRESH

Virtual buffer pool parallel sequential threshold (VPPSEQT). This threshold is a part of the virtual buffer pool that might support parallel operations. It is measured as a percentage of the sequential steal threshold (VPSEQT). Setting VPPSEQT to zero disables parallel operation.

The default value for this threshold is 50% of the sequential steal threshold (VPSEQT). You can change the default value to any value from 0% to 100% by using the VPPSEQT option on the ALTER BUFFERPOOL command.

Field Name: QDBPPSQT

ASSISTING PARALLEL SEQ THRESH

Virtual buffer pool assisting parallel sequential threshold (VPXPSEQT). This threshold is a part of the virtual buffer pool that might support parallel operations initiated from another DB2 in the data sharing group. It is measured as a percentage of VPPSEQT.

Setting VPXPSEQT to zero (default) prevents DB2 from supporting sysplex query parallelism at run time for queries that use this buffer pool.

You can change the default value to any value from 0% to 100% using the VPXPSEQT option of the ALTER BUFFERPOOL command.

Field Name: QDBPXSQT

PGFIX ATTRIBUTE

Indicates whether a page is fixed in real storage when it is first used. It can have one of the following values: YES or NO.

Field Name: QDBPPFIX

PAGE STEAL METHOD

Identifies the page stealing algorithm (PGSTEAL) that is used for the virtual buffer pool. It controls when and whether performance-critical objects in buffer pools are removed from buffer pools when the space is needed by other objects. Possible values are:

LRU

Least recently used (LRU) objects are removed first. This means it takes away pages that are not used so that more recently used pages can remain in the virtual buffer pool. This is used by default.

FIFO

First-In-First-Out (FIFO) means that the oldest objects are removed first. This results in a small decrease in the cost of a Getpage operation. It can reduce internal DB2 latch contention in environments that require very high concurrency.

NONE

Objects are not removed from buffer pool (no page stealing). This setting provides the highest availability for business-critical objects.

Old status taken from the DB2 field QW0201OK.

New status taken from the DB2 field QW0201NK.

Field Name: RT0201PS

AUTOSIZE

Indicates if the AUTOSIZE option is activated on the ALTER BUFFERPOOL command.

Field Name: QDBPASIZ

FRAMESIZE

The frame size.

Field Name: QDBPFRAM

VPOOL SIZE MIN

The minimum size of the virtual pool.

Field Name: QDBPVPMI

VPOOL SIZE MAX

The maximum size of the virtual pool.

Field Name: QDBPVPMA

SIM POOL SIZE

The number of simulated buffers allocated in the simulated buffer pool.

Field Name: QDBPSPSZ

SIM POOL SEQ THRESH

The sequential steal threshold for the simulated buffer pool, expressed as a percentage of the total simulated buffer pool size.

Field Name: QDBPSPST