Buffer pool write operations (BSTATDR)

BUFFER UPDATES

Number of page updates (QBSTSWS)

In the DB2 Statistics Overview Report--averages, this value is the average number of rows updated in system pages per CREATE THREAD during this statistics interval. This counter is incremented each time a page is updated in the pool. After a page is updated, it is in use until committed or rolled back. If the same page is updated multiple times, each update intent is counted. Also, this count includes not only updates to data pages but also work file pages, so sort activity increases this count. In a data sharing environment, updated pages might cause buffer invalidation in other member DB2s.

Tuning Tip: This number reflects update activity. Ideally, DB2 writes updated pages asynchronously at a system checkpoint or through deferred write. The goal is to avoid degrading to synchronous writes.

PAGES WRITTEN

Number of pages written (QBSTPWS)

This counter is incremented with the number of updated pages externalized to DASD. The goal is to see the lowest number of pages written compared to updated pages. This situation indicates that multiple updates are occurring in memory, which is desirable over writes to DASD.

Tuning Tip: A number of factors affect this ratio including the amount of pages re-used, the buffer pool size, concurrent access, and application design. Buffer pool problems in DB2 generally tend to show up in read inefficiency. Unless page writes are totally random, tuning should be focused on read efficiency. DB2 normally handles write activity appropriately.

BUFF UPDATE/PAGE WRITTEN

Ratio of the number of page updates to the number of pages written (QBSTSWS/QBSTPWS)

SYNCHRONOUS WRITES

Number of immediate writes (QBSTIMW)

This counter is incremented when a buffer pool reaches 97.5% full of non-stealable buffers. DB2 then attempts to schedule all writes synchronously rather than asynchronously. This time is directly charged to all applications that are updating and seriously degrades performance.

Tuning Tip: This number should be zero. If non-zero, consider increasing the size of the buffer pool or ALTERing the heaviest updated page set to a different buffer pool.

ASYNCHRONOUS WRITES

Number of asynchronous write I/Os (QBSTWIO)

In the DB2 Statistics Overview Report—averages, this value is the average number of write I/Os issued per CREATE THREAD during this statistics interval. This number is incremented each time DB2 schedules asynchronous writes through the media manager (VSAM) to DASD. This process generally occurs at system checkpoints or deferred write thresholds.

Tuning Tip: The number of asynchronous writes should far exceed any synchronous write activity.

PAGES WRITTEN/WRITE I/O

Number of pages written from the buffer pool to DASD for synchronous or asynchronous write I/O

HORIZ DEF WRITE THRESH

Number of times deferred write threshold reached (QBSTDWT)

This counter is updated when there are more than 50% (DWQT default value) of updated pages in a virtual buffer pool waiting to be externalized. DB2 starts asynchronously writing the updated pages until the number drops below the threshold.

Tuning Tip: This situation can occur during heavy update periods, and by itself is not a sign of serious performance degradation. However, if this number is reached often, it could point to the need to enlarge the virtual pool or hiperpool. The 50% default is modifiable by issuing an -ALTER BUFFERPOOL command for the DWQT threshold.

VERTI DEF WRITE THRESH

Number of times vertical deferred write threshold reached (QBSTDWV)

This counter is incremented when a single page set takes up more space than the user-defined vertical deferred write threshold (by default 10%). The goal is to prevent any single data set assigned to a buffer pool from holding on to updated buffers at the expense of the other page sets occupying the pool. Each time this occurs, DB2 writes these buffers to DASD with chained write I/O.

Tuning Tip: The DBA and system tuner need to know the characteristics of the buffer pool and the page sets assigned to it. If a single page set is assigned to a buffer pool, the vertical deferred write threshold can be raised. The goal is to prevent unnecessary DASD I/O caused by a too-low threshold. In general, if this threshold is reached often, the virtual buffer pool size or vertical deferred write threshold should be increased.

DM CRITICAL THRESHOLD

Number of times Data Manager Buffer Critical reached (QBSTDMC)

This counter is incremented when a buffer pool contains 95% or more of nonstealable pages. Sequential prefetch was turned off at 90%. At 95%, DB2 parses rows instead of 4K pages. This situation becomes evident because more than one GETPAGE can be issued for the same page.

Tuning Tip: This situation is a sign of serious performance stress on the buffer pool. The choice of fixes are to increase the virtual pool size or to allow fewer updates during a specified time period. At 95%, the CPU overhead to read and write rows becomes extreme and is very noticeable to users.

WRITE ENGINE NOT AVAIL

Number of times write engine not available (QBSTWEE)

This counter is incremented when 300 write engines are activated in a DB2 subsystem and another is needed for asynchronous write I/O.

This counter is obsolete beginning with DB2 8.1.

Tuning Tip: This situation should occur rarely and the number should be close to zero. Because the 300 write engine limit is hard coded in DB2, the only solution available to you is to spread out the workload over a longer period of time so that data externalization does not require more than 300 engines

PAGE INS REQ FOR WRITE

Number of page-ins required for write I/O (QBSTWPI)

This reflects the number of times DB2 had to page in updated pages before writing them to DASD because these pages had been paged out to MVS auxiliary storage.

Tuning Tip: In general, any serious paging in a virtual pool is a sign of memory overallocation. Because DB2 will to try to write data asynchronously, paging will be less serious than a page-in required for read activity. Nevertheless, if the page-in rate is greater than roughly five pages per second, it can be a sign of serious memory overallocation.

SYNC HPOOL WRITE

Number of pages moved synchronously from the virtual buffer pool to the hiperpool (QBSTHWR)

ASYNC HPOOL WRITE

Number of pages moved asynchronously from the virtual buffer pool to the hiperpool (QBSTHWA)

HPOOL WRITE FAILED

Number of pages for which a synchronous or asynchronous write request failed due to a shortage of expanded storage ( QBSTHWF)

ASYN DA MVR HPOOL WRIT-S

Number of pages moved successfully from the virtual buffer pool to the hiperpool by using the asynchronous data mover facility ( QBSTAWA)

ASYN DA MVR HPOOL WRIT-F

Number of times a write request, using the asynchronous mover facility, failed due to the backing-expanded storage being stolen or some other error ( QBSTAWF)