Db2 Buffer Pool Statistics Attributes

Use these attributes to create situations that determine the usage of Db2 buffer pools.

Async Page Dyn Prefetch The number of asynchronous page reads because of dynamic prefetch (Field name: QBSTDPP).

Async Page Read List Prefetch The number of asynchronous page reads because of list prefetch (Field name: QBSTLPP).

Async Page Read Dyn Prefetch The number of asynchronous page read I/O operations because of dynamic prefetch (Field name: QBSTDIO).

Async Read List Prefetch The number of asynchronous read I/O operations because of list prefetch (Field name: QBSTLIO).

Async Reads Seq Prefetch The number of asynchronous read I/Os caused by the normal sequential prefetch (both applications and utilities) (Field name: QBSTPIO). This number represents the number of I/O operations, not the number of pages read. See QBSTSPP for the number of pages read. This counter applies to only to work file prefetch.

Async Write I/O The number of asynchronous write I/O operations to disk (Field name: QBSTWIO).

Buffers Allocated The number of buffers allocated for a virtual buffer pool (Field name: QBSTVPL).

Castout Operations The number of castout I/O operations (Field name: QBSTCIO).

Current Active Buffers The number of current active (nonstealable) buffers. (Field name: QBSTCBA). Instantaneous sample of the number of buffers in the buffer pool that were active (in the nonstealable status) at the time of the request to transfer the buffer manager statistical data to the SMF record being produced. This field provides a snapshot value at statistics collection time, which means it only shows a problem if it happens at this time. The buffer pool might be too small if this is greater than 50% of the buffer pool. This field is not accumulated.

Currently Alloc Workfiles Exceeded The number of times the currently allocated work files exceeded the maximum buffer pool resources allocated to sequential processing (Field name: QBSTMAX). Usually, no more work files are created after this field is implemented. This field indicates that a sort is being done, and that the sort is limited in how many work files it can use. In general, sorts are more efficient with additional work files, but there are Db2 limits on the number of work files a transaction can have. Sometimes a transaction finds at run time that it cannot use as many work files as it had planned. You can try increasing the size of the buffer pool or altering the transaction so it requires fewer concurrent work files.

Datasets Opened The number of data sets physically opened (Field name: QBSTDSO). This value is cumulative since the start of this statistics interval. see the "Performance, monitoring and tuning" section of Administration Guide for information about physical and logical open and close.

Data Sharing Group The data sharing group name (Field name: GROUP).

Db2 Subsystem The Db2 subsystem ID (Field name: DB2ID).

Db2 Version The Db2 version (Field name: DB2VER).

Defer Write Thresh Reached The number of times the deferred write threshold was reached (Field name: QBSTDWT). When the deferred write threshold is reached, the data sets with the oldest updated pages are written asynchronously. Db2 continues writing pages until the ratio is under the threshold. before this threshold is reached, pages are written only if the number of pages queued per data set reaches a certain percent of the buffer pool size. For details, see "Buffer pool thresholds" in the "Performance, monitoring and tuning" section in Administration Guide.

Defer Write Thresh Reached The number of times the vertical deferred write threshold was reached (Field name: QBSTDWT). When the number of updated pages for a given data set exceeds the vertical deferred write threshold, deferred writes are initiated for that data set.

DM Buffer Crit Thresh Reached The number of times the data manager buffer critical threshold (DMTH) was reached (Field name: QBSTDMC). When the data manager threshold is reached, getpage requests and releases apply to rows instead of to pages. That is, when more than one row is retrieved or updated in a page, more than one getpage request and release is performed on that page. Avoid reaching this threshold, because it has a significant effect on CPU usage. This field should be zero. For details, see "Buffer pool thresholds" in the "Performance, monitoring, and tuning" section of Administration Guide.

DSG Member Name The Data Sharing Group (DSG) member name (Field name: MEMBER).

Dynamic Prefetch Requests The number of dynamic prefetch requests (Field name: QBSTDPF). This is the process that is triggered because of sequential detection. This field also counts prefetches for segmented table spaces. For information about when sequential detection is used, see the "Performance, monitoring and tuning" section of Administration Guide.

Expand Fail SOS The number of virtual buffer pool expansion failures caused by a shortage of virtual storage (Field name: QBSTXFV). Ideally, this value should be zero. if it is not, check the virtual storage allocation of the database address space (DS1DBM1 for areas that can be reduced. for example, you can reduce the size of other buffer pools.

Expansions Contract Alter The number of successful virtual buffer pool expansions or contractions because of the alter buffer pool command (Field name: QBSTVPA).

Get Page The number of GETPAGE requests (both successful and unsuccessful) and conditional requests that are successful (Field name: QBSTGET).

Getpage Fail VPOOL Full The number of times that a usable buffer could not be located in the virtual buffer pool because the virtual buffer pool was full. ideally, this value should be zero (Field name: QBSTXFL). If it is not, use the -alter buffer pool command to increase the virtual buffer pool size (vpsize).

Getpage Per Sync Read Random The number of random Getpage requests per random synchronous read I/O request (Field name: SBRGPRI1). Background and tuning information: This ration is a good indicator of read efficiency in a transaction environment. The higher the number, the better.

Getpage Per Sync Read Random 2 The number of random Getpage requests per random synchronous read I/O request (Field name: SBRGPRIO). Background and tuning information: This ration is a good indicator of read efficiency in a transaction environment. The higher the number, the better.

Getpage Sequential Access The accumulated wait time for a synchronous execution unit switch (Field name: QBSTSGT).

Hit Ratio The buffer hit ratio (Field name: BUHITRA).

HWM Buffers on SLRU The maximum number of buffers on the sequential LRU chain. (Field name: QBSTSMAX). This value is the highest value in the statistics interval.

List Prefetch Reads The number of list prefetch requests (Field name: QBSTLPF). List prefetch allows Db2 to access data pages efficiently even when the needed data pages are not contiguous. List prefetch can be used with single index access and is always used with multiple index access. It is always used to access data from the inner table during a hybrid join. Data pages are read in quantities equal to the sequential prefetch quantity (see QBSTSEQ), which depends on buffer pool size and is usually 32 pages. During bind, Db2 does not use list prefetch if the estimated number of RIDs to be processed would take more than 50% of the RID pool. During execution time, list prefetch processing terminates if Db2 detects that more than 25% of the rows in the table must be accessed. If list prefetch is terminated, IFCID 125 indicates this.

LPL inserts The number of times that one or more pages were added to the logical page list (LPL) (Field name: QBSTLPL).

LWM Buffers on SLRU The minimum number of buffers on the sequential LRU chain. (Field name: QBSTSMIN). This value is the lowest value in the statistics interval.

Max Workfiles Used Merge The maximum number of work files that were concurrently used during merge processing within this statistics period. (Field name: QBSTWFM). Ideally, each work file needs 16 buffers to allow Db2 to perform sequential prefetch for work files.

Migrated Datasets The number of times migrated data sets were encountered (Field name: QBSTMIG).

MVS System ID The MVS identifier (Field name: MVSID).

Name The buffer pool ID (Field name: QBSTPIDC).

buffers consumed = 2 * (work files already allocated)                        
buffers available = (sequential steal threshold * buffer pool size - buffers consumed)                                      
max work files allowed = buffers available /(2 * 8)

Originating System The managed system name of the agent (Field name: ORIGINNODE). It is an alphanumeric text string, with a maximum of 32 characters; for example, DB91:SYS1:DB2.

Overflow Random Getpages The number of non-sequential getpage requests that used buffers in the overflow area (Field name: QBSTAGET).

Overflow Seq Getpages The number of sequential getpage requests that used buffers in the overflow area (Field name: QBSTASGE).

Page-Ins Read I/O The number of Page-Ins required for read I/O (Field name: QBSTRPI).

Page-Ins Write I/O The number of Page-Ins required for write I/O (Field name: QBSTWPI).

Pages Destruct Read Required The number of pages for which a destructive read was requested (Field name: QBSTWDRP).

Pages In-Use Buffer pool virtual buffer pool pages in use percent (Field name: SBRBUTP).

Pages Removed DS deferred Write Destruct Read The number of pages removed from the data set deferred write queue for destructive read requests (Field name: QBSTWBVQ).

Pages Read Seq Prefetch The number of pages read because of normal sequential prefetch (Field name: QBSTSPP). A sequential prefetch request does not result in read I/O if the requested pages are found in the buffer pool.

Pages Written for Castout I/O The number of pages written for castout I/O operations (Field name: QBSTPCO).

Parallel Query Requests The total number of requests made for processing queries in parallel in this buffer pool. (Field name: QBSTPQO). This counter is applicable only for non-work file page sets and when queries are processing in parallel in Db2.

Parallel Getpage Unavailable The number of times a page requested for a query processed in parallel was unavailable because the page was not found in the buffer pool. (Field name: QBSTNGT). The agent does not wait. Instead, control returns to the agent and asynchronous prefetch I/O is triggered. If the value is close to zero, most pages are already prefetched into the buffer pool and wait time for synchronous I/O is small. This counter can be high if, for example, there is a cluster index scan and the data is not truly clustered by the index key, so the data pages are not accessed in their true order. Hence, the cluster ratio is not valid. Use the runstats utility to update it. This number is also used to determine how many sequential prefetches of one page were scheduled.

Prefetch I/O Concurr HWM The highest number of concurrent prefetch I/O streams that were allocated for supporting queries processed in parallel in this buffer pool. (Field name: QBSTNIX). This counter reflects prefetch activities for non-work file page sets. This counter is applicable only when queries are processing in parallel in Db2.

Prefetch I/O Reduced Shortage The total number of requested prefetch I/O streams that were denied because of a storage shortage in the buffer pool (Field name: QBSTJIS). For example, if 100 prefetch I/O streams are requested and only 80 are granted, then 20 is added to this counter. If this value is nonzero, you might want to consider increasing the size of the buffer pool. The ratio of QBSTJIS and QBSTPDF gives the average degree of parallel query processing that was reduced because of insufficient buffer pool space. QBSTXIS gives the highest degree of parallel query processing that was reduced for one or more queries processed in parallel. This counter is applicable only for non-work file page sets and when queries are processing in parallel in Db2.

Prefetch Quantity 1/2 The total number of occurrences when the prefetch quantity is reduced from normal to one-half of normal. (Field name: QBSTPL1). The normal size is dependent on the size of the buffer pool. This counter indicates when Db2 had to reduce sequential prefetch quantity to continue to execute concurrently with parallel query processing in the system. If this field contains a small number, it might be tolerable. This counter is applicable only when queries are processing in parallel in Db2.

Prefetch Quantity 1/4 The total number of occurrences when the prefetch quantity is reduced from one-half to one-quarter of normal. (Field name: QBSTPL2). The normal size is dependent on the size of the buffer pool. when this field contains a nonzero value, it is a more serious situation than when QBSTPL1 is nonzero. the query response for queries processed in parallel can be significantly worse when this field is nonzero. this field is applicable only when queries are processing in parallel in Db2.

Random Failed Cond Request Getpage requests - Random access - Failed cond. request (Field name: SBSTNR1).

Random Failed Cond Request 2 Getpage requests - Random access - Failed cond. request (Field name: SBSTNRG).

Random Getpage Hit SLRU The number of times that a non-sequential getpage operation touched a buffer that is on the sequential LRU chain (Field name: QBSTSRHS).

Random Getpages The number of random Getpage requests (Field name: SDGETRA1).

Random Getpages 2 The number of random Getpage requests (Field name: SDGETRAN).

Random Hit Ratio The percentage of random Getpage operations that were satisfied by a page already in a buffer pool (Field name: SBUFFRDM). If this value is low, it indicates that page residency in the buffer pool is too low, therefore the buffer pool may be too small.

Read I/O DASD Cache Hit The number of synchronous read I/O operations that resulted in disk cache hits, and that did not use zHyperLink processing (Field name: QBSTSIOC).

Read I/O Sequential Access The number of synchronous buffer reads performed for statement (Field name: QW0316NB).

Recall Timeouts The number of recall timeouts (Field name: QBSTRTO).

Reduced Parallel Queries The total number of times during this statistics interval that Db2 could not allocate the requested number of buffer pages to allow a parallel group to run to the planned degree (Field name: QBSTPQF). This is caused by a storage shortage in the buffer pool. If this value is significant, increase the size of the buffer pool. This counter is applicable only for non-work file page sets and when queries are processing in parallel in Db2.

Seq Failed Cond Request The number of conditional sequential getpage requests that failed because the page was not in the buffer pool. (Field name: QBSTNSG).

Seq Hit Ratio The percentage of sequential Getpage operations that were satisfied by a page already in a buffer pool (Field name: SBUFFSEQ).

Seq Prefetch Disabled Buffers The number of times sequential prefetch was disabled because buffers were not available (Field name: QBSTSPD). In other words, this is the number of times the sequential prefetch threshold is reached. See the "Performance, monitoring and tuning" section of Administration Guide for more information on buffer pool thresholds. Ideally, this value should be zero. If this value is high, the buffer pool size or a sequential threshold might be too small.

Seq Prefetch Disabled Read Engine The number of times sequential prefetch was disabled because of an unavailable read engine (Field name: QBSTREE). The value in this field should be close to zero.

Seq Prefetch Not Scheduled Quantity The number of times sequential prefetch was not scheduled because the prefetch quantity was zero. (Field name: QBSTWKPD). Prefetch checks the prefetch quantity. Normally the quantity should be one to eight pages. When the quantity is zero, the value in this field is incremented. A high number in this field implies that the buffer pool is too small.

Sequential Prefetch The number of sequential prefetch requests (Field name: QBSTSEQ). This counter is incremented for each prefetch request. Each request might result in an I/O read. If it results in an I/O read, up to 32 pages can be read for SQL, and up to 64 pages for utilities. A request does not result in an i/o if all pages to be prefetched are already in the buffer pool. Sequential detection is not included in QBSTSEQ and is separately recorded in QBSTDPF.

Sort Merges The total number of sort/merges for Db2 sort activities (Field name: QBSTWFR).This counter reflects how many sort/merges were requested for db2 to determine the number of work files permitted to support each sort/merge.

Sync Read I/O Number of synchronous read I/O operations performed for both applications and utilities (Field name: QBSTRIO).

Sync Read NonSeq Overflow The number of synchronous read I/O operations for non-sequential getpage requests that used buffers in the overflow area. (Field name: QBSTASYN).

Sync Read Seq Overflow The number of synchronous read I/O operations for sequential getpage requests that used buffers in the overflow area. (Field name: QBSTASSE).

Sync reads Random access The number of random synchronous read I/O requests (Field name: SDSTRA1).

Sync reads Random access 2 The number of random synchronous read I/O requests (Field name: SDSTRAN).

Times SLRU EQ VPSEQT The number of times that the length of the sequential LRU chain reached the sequential steal threshold (VPSEQT) (Field name: QBSTHST). This value is the highest value in the statistics interval.

Update Intents The number of times update intents were requested against pages in the buffer pool (Field name: QBSTSWS).

Workfiles Sort Merge The total number of work files that were requested for all sort/merge processing (Field name: QBSTWFT). This counter and QBSTWFR can be used to determine the average number of work files (runs) requested on a single sort/merge. For Db2 to perform efficient prefetch for work files, each work file should have at least 16 dedicated buffers. Work files used during sort phase processing or other non-sort-related processing are not included in this statistics counter.

Workfile Reject Sort Merge The total number of work files that were rejected during sort/merge because of insufficient buffer resources (Field name: QBSTWFD). This counter and QBSTWFF can be used to determine the average number of work files that cannot be honored at each sort/merge because of insufficient buffer pool space. To improve sort performance, increase the buffer pool size.

zHyperLink Read I/Os The number successful read I/O operations that used zHyperLink processing (Field name: QBSTSYIO).