IFCID 002 - Buffer Pool Activity
This topic shows detailed information about "Record Trace - IFCID 002 - Buffer Pool Activity".
This block shows buffer pool activity at thread level.
For details on buffer pool activities, refer to the documentation of Performance Expert Buffer Pool Analyzer.
This report has the same layout as IFCID 002 - Miscellaneous.
Record trace - IFCID 002 - Buffer Pool Activity
The field labels shown in the following sample layout of "Record Trace - IFCID 002 - Buffer Pool Activity" are described in the following section.
BUFFER POOL ACTIVITY
BUFFER POOL ID : 0 FLAGS : X'80'
CURRENT ACTIVE BUFFERS : 51 GETPAGE REQUESTS : 3690
BUFFER UPDATES : 848 UNAVAILABLE BUFFER-VPOOL FULL : 0
GETPAGE REQUESTS-SEQUENTIAL : 655 PAGES WRITTEN : 578
NUMBER OF DATA SET OPENS : 75 SYNCHRONOUS READS : 336
SYNCHRONOUS WRITES : 200 BUFFERS ALLOCATED-VPOOL : 5000
SYNCHRONOUS READS-SEQUENTIAL : 18 ASYNCHRONOUS WRITES : 200
DFHSM MIGRATED DATA SETS : 0 SEQUENTIAL PREFETCH RE-QUESTS : 4
HORIZONTAL DEFERRED WRITE THRESHOLD : 0 DFHSM RECALL TIMEOUTS : 0
SEQUENTIAL PREFETCH READS : 4 VERTICAL DEFERRED WRITE THRESHOLD : 0
VPOOL EXPANSION OR CONTRACT : 0 PAGES READ VIA SEQUEN-TIAL PREFETCH : 91
DATA MANAGER BUF CRITICAL THRESHOLD : 0 VPOOL OR HPOOL EXPANSION FAILURE : 0
LIST PREFETCH REQUESTS : 20 CONCURRENT PREFETCH I/O STREAMS-HWM : 0
LIST PREFETCH READS : 20 PAGE-INS REQUIRED FOR WRITE : 0
PREFETCH I/O STREAMS REDUCTION : 0 PAGES READ VIA LIST PREFETCH : 114
MAX WORKFILES CONCURRENTLY USED : 2 PARALLEL QUERY REQUESTS : 0
DYNAMIC PREFETCH REQUESTS : 39 MERGE PASSES REQUESTED : 1
DYNAMIC PREFETCH READS : 12 MERGE PASS DEGRADED-LOW BUFFER : 0
PAGES READ VIA DYNAMIC PREFETCH : 175 WORKFILE REQUEST REJECT-ED-LOW BUFFER : 0
WORKFILE REQUESTED-ALL MERGE PASS : 2 WORKFILE NOT CREATED-NO BUFFER : 0
PREFETCH QUANTITY REDUCED TO HALF : 0 PREFETCH DISABLED-NO BUFFER : 0
WORKFILE PREFETCH NOT SCHEDULED : 0 PREFETCH QUANTITY RE-DUCED TO QUARTER : 0
PREFETCH DISABLED-NO READ ENGINE : 0 WORKFILE PAGES TO DE-STRUCT : 0
FAILED COND SEQ&RDM GETPAGE REQUEST : 0 PAGE-INS REQUIRED FOR READ : 716
WORKFILE PAGES NOT WRITTEN : 0 FAILED COND SEQ GETPAGE REQUEST : 0
MINIMUM BUFFERS ON SLRU (LWM) : 386 PAGES ADDED TO LPL : 0
MAXIMUM BUFFERS ON SLRU (HWM) : 386 LENGTH OF SLRU = VPSEQT : 0
IN-MEM OVL RND REQUESTS : 100 IN-MEM OVL SEQ REQUESTS : 10
IN-MEM OVL RND SYNC READS : 200 IN-MEM OVL SEQ SYNC READS : 20
RANDOM GETPAGE BUFFER HIT : 14 - BUFFER POOL ID
The buffer pool ID.
Field Name: QBSTPID
- FLAGS
The flag byte shows if more QBST data is following or if this is the last of the QBST repeating groups.
Field Name: QBSTFLG
- CURRENT ACTIVE BUFFERS
The total number of currently active (nonstealable) buffers. This field is an instantaneous sample of the number of buffers in the buffer pool that were updated or in use at the time this monitor data was requested. Because this field gives a snapshot value at statistics collection time, it only shows a problem if it happens at this time.
Background and Tuning Information
The buffer pool might be too small if the percentage of active pages in the buffer pool is beyond the deferred write threshold (DWQT).
Field Name: QBSTCBA
- GETPAGE REQUESTS
The number of Getpage requests including conditional and unconditional requests.
Field Name: QBSTGET
- BUFFER UPDATES
The number of times buffer updates were requested against pages in the buffer pool.
Background and Tuning Information
The ratio of Buffer Updates to Pages Written (QBSTPWS) suggests a high level of efficiency as it increases, because more updates are being externalized per physical write.
Buffer updates per pages written depends strongly on the type of application. For example, a batch program that processes a table in skip sequential mode with a high row update frequency in a dedicated environment can achieve very good update efficiency. In contrast, update efficiency tends to be lower for transaction processing applications, because transaction processing tends to be random.
The following can influence the number of updates per page:- Number of rows per page
- A small PCTFREE value gathers more rows on the same page. However, at the same time this can impact concurrency.
- Buffer pool size and deferred write thresholds
- Increase DWQT and VDWQT or the size of the buffer pool. This causes DB2 to let page updates accumulate in the buffer pool. Therefore, the probability that more updates per page get captured increases. This effect is less significant if the buffer pool is concurrently used by several transactions, it also depends on the type of transaction.
Field Name: QBSTSWS
This is an exception field.
- UNAVAILABLE BUFFER-VPOOL FULL
The number of times a usable buffer could not be located in the virtual buffer pool because the virtual buffer pool was full.
Background and Tuning Information
Ideally, this value should be 0. Any other value indicates that the buffer pool is underallocated. In this case, use the ALTER BUFFERPOOL command to increase the virtual buffer pool size until this value remains at 0.
Field Name: QBSTXFL
This is an exception field.
- GETPAGE REQUESTS-SEQUENTIAL
The number of Getpage requests issued by sequential access requesters.
Field Name: QBSTSGT
- PAGES WRITTEN
The number of pages in the buffer pool written to DASD.
Background and Tuning Information
Consider the ratio of Pages Written per write I/O. The number of write I/O operations includes Asynchronous Writes (QBSTWIO) and Synchronous Writes (QBSTIMW).
The ratio of pages per write I/O suggests a high level of efficiency as the ratio increases, because more pages are being externalized per physical write.
The following factors impact the ratio of pages written per write I/O:- Checkpoint frequency
-
At checkpoint time, I/Os are scheduled to write all updated pages
on the deferred write queue to DASD. If this occurs too frequently,
the deferred write queue does not grow large enough to achieve a high
ratio of pages written per write I/O.
The checkpoint frequency depends on the number of logs written between two consecutive checkpoints. This number is set at installation time; see the field CHECKPOINT FREQ of installation panel DSNTIPN.
- Frequency of active log switch
- DB2 takes a system checkpoint each time the active log is switched. High frequency of active log switches causes the problem described under checkpoint frequency.
- Buffer pool size and deferred write thresholds
- The deferred write thresholds (VDWQT and DWQT) are a function of buffer pool size. If the buffer pool size is decreased, these thresholds are reached more frequently, causing I/Os to be scheduled more often to write some of the pages on the deferred write queue to DASD. This prevents the deferred write queue from growing large enough to achieve a high ratio of pages written per write I/O.
- Number of data sets, and the spread of updated pages across them
-
The efficiency of write I/O also depends on the number of data sets
associated with the buffer pool and spread of updated pages across
them. Because of the nature of batch processing, the ratio of pages
written to write I/Os can be expected to be higher than that
expected
for transaction type workloads.
To determine update efficiency, use also the value in the Buffer Updates field (QBSTSWS) to check the number of buffer updates per page written.
Field Name: QBSTPWS
This is an exception field.
- NUMBER OF DATA SET OPENS
The number of data sets physically opened successfully. This value is cumulative from the start of the DB2 statistics interval.
Field Name: QBSTDSO
This is an exception field.
- SYNCHRONOUS READS
The number of synchronous read I/O operations performed by DB2 for applications and utilities.
Background and Tuning Information
This number includes both Synchronous Reads Sequential Access Only (QBSTSIO) and synchronous read operations for non-sequential access.
You can use this value and the value of Synchronous Reads Sequential Access Only to calculate the number of Non-Sequential Synchronous Reads.
Check the buffer pool hit ratio if the number of non-sequential synchronous reads is larger than expected.
Field Name: QBSTRIO
This is an exception field.
- SYNCHRONOUS WRITES
The total number of immediate writes.
Immediate writes occur when:- An immediate write threshold (IWTH) is reached
- No deferred write engines are available
- More than two checkpoints pass without a page being written.
Sometimes DB2 uses synchronous writes even when the IWTH is not exceeded. As an example, when more than two checkpoints pass without a page being written. This type of situation does not indicate a buffer shortage.
Background and Tuning Information
A small number of immediate writes can be expected. Synchronous writes occur if there are too many checkpoints and/or the buffer pool is too small.
If a large number of synchronous writes occur, monitor the
DM Critical Threshold Reached
(QBSTDMC)
field.
Reaching Immediate Write Threshold (IWTH-97.5%) implies that the Data
Management Threshold (DMTH-95%) has been crossed.
You can ignore the value in the immediate write field when
DM Critical Threshold Reached
is zero. Otherwise consider increasing the size of the buffer pool.
You can use the ALTER BUFFERPOOL command. 
Check also the System Event Checkpoint field (QWSDCKPT) in the Subsystem Services block to see whether the frequency of DB2 checkpoints should be reduced. To do this, increase the value of ZPARM LOGLOAD.
Field Name: QBSTIMW
This is an exception field.
- BUFFERS ALLOCATED-VPOOL
The number of buffers allocated for a virtual buffer pool.
Note:In DB2 10, the buffer pool size can increase continuously
by up to 25% for each DB2 restart.
In DB2 11, the AUTOSIZE option of the ALTER BUFFERPOOL
command can limit the range
within VPSIZEMIN and VPSIZEMAX.Background and Tuning Information
You should monitor the buffer pool hit ratio field to find the optimum size of the buffer pool. Usually the buffer pool hit ratio is improved by increasing the size of the buffer pool. However, paging the buffer pool storage impacts DB2 performance if the virtual buffer pool is too large.
Page-ins Required for Read I/O (QBSTRPI) and Page-ins Required for Write I/O (QBSTWPI) are useful when determining whether paging affects the performance of a certain buffer pool. The Resource Measurement Facility (RMF) also provides reports on MVS paging activity:- Storage Paging
-
When the virtual buffer pool is extended into expanded storage,
MVS storage paging activity occurs.
If a large buffer pool size
results in excessive storage paging, consider
allocating more real storage to the LPAR.
- Paging to Auxiliary Storage
- If the virtual buffer pool size requirements exceed the central storage and expanded storage available, the oldest buffer pool pages migrate to auxiliary paging storage. When these pages are accessed subsequently, I/O must bring them back into real storage. This should be avoided. You could have a smaller buffer pool and let DB2 do the I/O rather than use MVS paging with its I/O CPU overhead. This is a situation that you (as the system programmer) should monitor.
You can use the ALTER BUFFERPOOL command to alter the size of the
virtual buffer pool. Changing the size of the virtual buffer pool implicitly changes the buffer pool thresholds. See the Deferred Write Threshold Reached field (QBSTDWT).
Field Name: QBSTVPL
- SYNCHRONOUS READS-SEQUENTIAL
The number of synchronous read I/O requests issued by sequential access requesters.
Background and Tuning Information
Sequential synchronous read I/Os can occur because:- Prefetch is disabled (QBSTSPD).
- Prefetch pages could have been stolen from the buffer pool before the Getpage request is issued for those pages. Subsequently the pages are reread synchronously. A negative buffer pool hit ratio can indicate the same problem.
- The pages requested are not consecutive: DB2 estimated the selected range of pages to be so small that prefetch would make no sense. See also Sequential Prefetch Requested (QBSTSEQ).
Field Name: QBSTSIO
This is an exception field.
- ASYNCHRONOUS WRITES
The number of asynchronous write I/O operations performed by media manager to a direct access storage device.
Field Name: QBSTWIO
This is an exception field.
- DFHSM MIGRATED DATA SETS
The number of times migrated data sets were encountered.
Field Name: QBSTMIG
This is an exception field.
- SEQUENTIAL PREFETCH REQUESTS
- The number of sequential prefetch requests. This counter is incremented
for each PREFETCH request (which can result in an
I/O read).
If the prefetch
results in an I/O read, up to 64 pages may be read for SQL, and up
to 128 pages for utilities. A request does not result in an I/O read if
all pages to be prefetched are already in the buffer pool.
This counter does not include sequential detection, which is recorded in the Dynamic Prefetch - Requested field.
Background and Tuning Information
Sequential prefetch reads a sequential set of pages. It allows CP and I/O operations to be overlapped. DB2 determines at BIND time whether sequential prefetch is used or not.
Sequential prefetch is generally used for a table space scan. The number of prefetch requests by itself is not a good indicator for efficiency of prefetching:- At run time not every prefetch request results in read I/O: the Sequential Prefetch Reads field (QBSTPIO) shows the number of read I/O operations caused by sequential prefetch. The Prefetch Disabled No Buffer (QBSTSPD) and Prefetch Disabled No Read Engine fields (QBSTREE) show the number of times prefetch was disabled because buffers and read engines had not been available.
- Check the value in the buffer pool hit ratio. A negative value indicates that prefetched pages are stolen from the buffer pool before they are read. The pages are subsequently reread synchronously. There will be also a large value in the Synchronous Reads Total (QBSTRIO) field.
- Decreasing the size of the buffer pool can reduce the prefetch quantity, leading to a larger number of prefetch requests. See also the Sequential Prefetch Pages Read field (QBSTSPP).
Field Name: QBSTSEQ
This is an exception field.
- HORIZONTAL DEFERRED WRITE THRESHOLD
The number of times the deferred write threshold (DWTH) was reached.
This threshold is a percentage of the virtual buffer pool that might be occupied by unavailable pages, including both updated pages and pages in use. DB2 checks this threshold when an update to a page is completed. If the percentage of unavailable pages in the virtual buffer pool exceeds the threshold, write operations are scheduled for enough data sets (up to 128 pages per data set) to reduce the number of unavailable buffers to 10% below the threshold.
Background and Tuning Information
The default value for this threshold is 30%.
You can change that to any
value from 0% to 90% by using the DWQT
option on the ALTER
BUFFERPOOL command. The deferred write thresholds, DWQT and VDWQT, are specified as a percentage, their absolute value depends on the size of the virtual buffer pool.
Consider the following aspects when changing the deferred write thresholds:- Optimize the ratio of pages written per write I/O
-
The ratio can be monitored using the
Pages Written
(QBSTPWS) field.
When the buffer pool is relatively small, the default thresholds could prevent the deferred write queue from growing large enough to achieve a high ratio of pages written per write I/O. Raising these thresholds will, in this instance, reduce the I/O write frequency, increasing the number of pages written per I/O.
- Distribute I/O evenly over time
- If a virtual buffer pool is very large, it is unlikely that the default values of either DWQT or VDWQT will ever be reached. In this case, write I/Os tend to occur in surges, triggered by DB2 checkpoints. Lowering the VDWQT and the DWQT could improve performance by distributing the write I/Os more evenly over time.
- Impact on other buffer pool thresholds
- Increasing DWQT and VDWQT allows updated pages to use a larger portion of the virtual buffer pool. Large DWQT and VDWQT can have a significant effect on the other thresholds. For example, in work load where pages are frequently updated, and the updated pages exceed the size of the virtual buffer pool, setting both DWQT and VDWQT to 90% would probably cause frequent threshold-reached events for sequential prefetch (and possibly the data management and immediate write).
Field Name: QBSTDWT
This is an exception field.
- DFHSM RECALL TIMEOUTS
The number of recall timeouts.
Field Name: QBSTRTO
- SEQUENTIAL PREFETCH READS
The number of asynchronous read I/O operations due to normal sequential prefetch (applications and utilities).
Background and Tuning Information
Prefetch Read I/O is not activated if one of the following conditions applies:- All pages in the prefetch range are already in the buffer pool.
- Prefetch is disabled (QBSTSPD).
Field Name: QBSTPIO
This is an exception field.
- VERTICAL DEFERRED WRITE THRESHOLD
The number of times the vertical deferred write threshold (VDWQT) was reached. 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.
Field Name: QBSTDWV
This is an exception field.
- VPOOL EXPANSION OR CONTRACT
The number of successful virtual buffer pool expansions or contractions due to the ALTER BUFFERPOOL command. An increase in this counter indicates that buffer-pool-related system parameters have been changed.
Field Name: QBSTVPA
This is an exception field.
- PAGES READ VIA SEQUENTIAL PREFETCH
The total number of pages read due to a normal sequential prefetch. A sequential prefetch request does not result in a read I/O if all the desired pages are found in the buffer pool.
Background and Tuning Information
For requests issued by application programs, the number of pages
per READ I/O primarily depends on the page size and the size of
the buffer pool.
Normally sixty-four 4 KB pages (or eight 32 KB pages) is
the maximum
prefetch quantity for table space scans, whether data or index.
Utilities use a prefetch quantity of up to 64 pages. The number of pages per READ I/O can be lower because:- Pages within the prefetch range may already be in the buffer pool.
- Not enough pages are available because of a buffer shortage.
- A prefetch quantity of 8 pages or less is used for work files.
- A good performing buffer pool being so large that most of the pages, which had otherwise to be prefetched, are cached in the buffer pool. In this case, the buffer pool hit ratio should be high.
- A buffer shortage condition, reducing the efficiency of sequential prefetch. This could mean, for example, work-file prefetch quantity reduction from 8 to 4 to 2, as the number of available buffers shrinks. In this case, you should consider tuning the buffer pool.
Field Name: QBSTSPP
This is an exception field.
- DATA MANAGER BUF CRITICAL THRESHOLD
The number of times the data manager critical threshold (DMTH-95%) was reached.
This field shows how many times a page was immediately released because the data management threshold was reached.
The threshold is checked before a page is read or updated. If the threshold has not been exceeded, DB2 accesses the page in the virtual buffer pool once for each page, no matter how many rows are retrieved or updated in that page. If the threshold has been exceeded, Getpage requests and RELEASEs apply to rows instead of pages. That is, if more than one row is retrieved or updated in a page, more than one Getpage request and RELEASE is performed on that page.
Background and Tuning Information
Avoid reaching this threshold wherever possible because it significantly affects CPU usage. Set virtual buffer pool sizes large enough or reduce the workload on the buffer pool.
Field Name: QBSTDMC
This is an exception field.
- VPOOL EXPANSION FAILURE
The total number of virtual buffer pool expansion failures due to the lack of virtual storage space.
Background and Tuning Information
Ideally, this value should be 0. If it is not, check the virtual storage allocation of the DB2 database address space for areas that can be reduced. For example, you can reduce the size of other buffer pools.
Field Name: QBSTXFV
This is an exception field.
- LIST PREFETCH REQUESTS
The number of list prefetch requests.
List prefetch allows DB2 to access data pages efficiently even when the required data pages are not contiguous. It allows CP and I/O operations to be overlapped.
Background and Tuning Information
DB2 determines at BIND time whether sequential prefetch is used. List prefetch is chosen as follows:- Usually with a single index that has a cluster ratio lower than 80%.
- Sometimes on a single index with a high cluster ratio, if the estimated amount of data to be accessed is too small to make sequential prefetch efficient.
- Always to access data by multiple index access.
- Always to access data from the inner table during a hybrid join.
Data pages are read in quantities equal to the sequential prefetch
quantity (QBSTSEQ),
which depends on buffer pool size and is usually 64 pages. Field Name: QBSTLPF
This is an exception field.
- CONCURRENT PREFETCH I/O STREAMS-HWM
The highest number of concurrent prefetch I/O streams allocated to support a parallel I/O or CP query in this buffer pool. It reflects prefetch activities for non-workfile page sets.
This number only applies to query I/O and CP parallelism.
Field Name: QBSTXIS
This is an exception field.
- LIST PREFETCH READS
The number of asynchronous read I/O operations caused by the list prefetch.
The number of pages read is shown by the List Prefetch Pages Read (QBSTLPP) field.
Background and Tuning Information
Prefetch Read I/O is not activated if one of the following conditions apply:- All pages in the prefetch range are already in the buffer pool.
- Prefetch is disabled (Prefetch Disabled No Read Engine - QBSTREE).
Field Name: QBSTLIO
This is an exception field.
- PAGE-INS REQUIRED FOR WRITE
The number of page-ins required for a write I/O.
Field Name: QBSTWPI
- PREFETCH I/O STREAMS REDUCTION
The total number of requested prefetch I/O streams that were denied because of a lack of buffer pool storage space.
It only applies to query I/O and CP parallelism.
For example, if 100 prefetch I/O streams are requested and only 80 are granted, then 20 is added to the number in this field.
Background and Tuning Information
Consider increasing the size of the buffer pool if this value is not 0.
The ratio of this field and the Reduced parallel query requests field gives the average degree of parallel query processing that was reduced because of insufficient buffer pool space. The Prefetch I/O streams - Concurrent streams - high-water mark field gives the highest degree of parallel query processing that was reduced for one or more queries processed in parallel.
The number in this field reflects the prefetch activities for non-workfile page sets.
Field Name: QBSTJIS
This is an exception field.
- PAGES READ VIA LIST PREFETCH
The number of pages read via list prefetch.
Field Name: QBSTLPP
- MAX WORKFILES CONCURRENTLY USED
The maximum number of work files concurrently used during merge processing within this statistics period.
Ideally, each work file needs 16 buffers to allow DB2 to perform a sequential prefetch for work files.
Field Name: QBSTWFM
This is an exception field.
- PARALLEL QUERY REQUESTS
The total number of requests made for parallel query support in this buffer pool. This field only applies to non-workfile page sets in query I/O and CP parallelism.
Field Name: QBSTPQO
- DYNAMIC PREFETCH REQUESTS
The number of dynamic prefetch requests. Dynamic prefetch is the process that is triggered because of sequential detection. If the prefetch request results in an I/O read, up to 32 advancing pages can be read at a time.
Background and Tuning Information
Dynamic prefetch reads a sequential set of pages. It allows CP and I/O operations to be overlapped. If DB2 does not choose prefetch at bind time it can sometimes use it at execution time. The method is called sequential detection.
The number of prefetch requests by itself is not a good indicator for efficiency of prefetching because:- At run time not every prefetch request results in read I/O: the Dynamic Prefetch Reads field shows the number of read I/O operations caused by dynamic prefetch. The Prefetch Disabled No Buffer (QBSTSPD) and Prefetch Disabled No Read Engine (QBSTREE) fields show the number of times prefetch was disabled because buffers and read engines had not been available.
- Prefetch pages can be stolen from the buffer pool before they are read. This is indicated by a negative buffer pool hit ratio. The pages are subsequently reread synchronously. This will also cause an unexpectedly large value for total synchronous reads (QBSTRIO).
Decreasing the size of the buffer pool can reduce the prefetch quantity (QBSTDPP), leading to a larger number of prefetch requests.
Field Name: QBSTDPF
This is an exception field.
- MERGE PASSES REQUESTED
The total number of merge passes for DB2 sort activities. This value reflects how many merge passes were requested for DB2 to determine the number of work files permitted to support each merge pass.
Field Name: QBSTWFR
- DYNAMIC PREFETCH READS
The number of asynchronous read I/Os because of dynamic prefetch. The number of pages read is recorded in the Dynamic Prefetch Pages Read field.
Background and Tuning Information
A prefetch request does not result in an I/O if one of the following conditions apply:- All pages to be prefetched are already in the buffer pool.
- The prefetch is canceled.
Field Name: QBSTDIO
This is an exception field.
- MERGE PASS DEGRADED-LOW BUFFER
The number of times that a merge pass was not efficiently performed due to a shortage of space in the buffer pool. The number in this field is incremented for each merge pass where the maximum number of work files allowed is less than the number of work-files requested.
Background and Tuning Information
The maximum number of work files allowed is calculated as follows:- Buffers consumed = 2 * (work files already allocated)
- Buffers available = (sequential steal threshold * buffer pool size - buffers consumed)
- Maximum work files allowed = buffers available / (2 * 8)
The default for the sequential steal threshold is 0.8.
Ideally, the number in this field should be 0. Otherwise, it indicates a shortage of buffer pool space or that there are too many concurrent work files. For example, there could be a number of concurrently open cursors that require sorting. Consider increasing the buffer pool size using the ALTER BUFFERPOOL command.
Field Name: QBSTWFF
This is an exception field.
- PAGES READ VIA DYNAMIC PREFETCH
The number of pages read because of dynamic prefetch. Dynamic prefetch is the process that is triggered by sequential detection.
Background and Tuning Information
The ratio of Dynamic Prefetch Pages Read to Dynamic Prefetch Reads is between 0 and 32.
DB2 can fetch up to 32 pages per prefetch.
The number of pages per READ I/O can be lower because:- Pages within the prefetch range are already in the buffer pool.
- Not as many pages are available due to a buffer shortage.
- A good performing buffer pool being large enough to contain pages that would otherwise be prefetched. This is indicated by a high buffer pool hit ratio.
- A buffer shortage condition, which reduces the efficiency of dynamic prefetch. In this instance the buffer pool hit ratio will be low. Consider tuning the buffer pool.
Field Name: QBSTDPP
This is an exception field.
- WORKFILE REQUEST REJECTED-LOW BUFFER
The total number of work files that were rejected during all merge passes because of insufficient buffer resources.
Background and Tuning Information
This field and the degraded low buffers field determine the average number of work files that cannot be honored at each merge pass because of insufficient buffer pool space.
Ideally, the number in this field should be 0. Otherwise, it indicates a shortage of buffer pool space or that there are too many concurrent work files. For example, there could be a number of concurrently open cursors that require sorting. Consider increasing the size of the buffer pool using the ALTER BUFFERPOOL command.
Note that, when there are many concurrent sorts or large sorts, it is a good idea to dedicate a separate buffer pool for sort work files. This will greatly facilitate work-file performance tuning.
Field Name: QBSTWFD
This is an exception field.
- WORKFILE REQUESTED-ALL MERGE PASS
The total number of work files requested for all merge passes.
This field and the Merge Passes Requested field determine the average number of work files requested in a single merge pass.
For DB2 to perform an efficient prefetch for work files, each workfile 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 number.
Field Name: QBSTWFT
- WORKFILE NOT CREATED-NO BUFFER
This field is only applicable if DB2 is running under MVS/XA.
The number of times a work file could not be created due to insufficient buffer resources. It indicates that a sort is in progress and limited in regard to the number of work files it can use.
Background and Tuning Information
Ideally, this should be 0. Otherwise, it indicates a shortage of buffer pool space or that there are many concurrent work files. For example, there could be a number of open cursors that require sorting.
Generally, sorts are performed more efficiently with additional work files, but there are internal DB2 limits on the number of work files a transaction can have. It is possible that at run time a transaction cannot use as many work files as it had planned. You can control this by increasing the buffer pool size (ALTER BUFFERPOOL), or changing the transaction so it requires fewer concurrent work files.
Field Name: QBSTMAX
This is an exception field.
- PREFETCH QUANTITY REDUCED TO HALF
The total number of times prefetch quantity is reduced from normal to 50% of normal. The normal size depends on the page size of the buffer pool.
This field only applies to query I/O and CP parallelism.
Background and Tuning Information
The number in this field indicates when DB2 had to reduce the sequential prefetch quantity to continue executing concurrently with parallel queries in the system. If the number is small, it may be tolerable.
Field Name: QBSTPL1
This is an exception field.
- PREFETCH DISABLED-NO BUFFER
The total number of times sequential prefetch was disabled because buffers were not available.
Field Name: QBSTSPD
This is an exception field.
- WORKFILE PREFETCH NOT SCHEDULED
The number of times a sequential prefetch was not scheduled for a work file because the dynamic prefetch quantity is zero.
Background and Tuning Information
The work-file prefetch checks the dynamic prefetch quantity (normally 1 to 8 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.
Ideally, the number in this field should be 0. Otherwise, it indicates a shortage of buffer pool space or that there are many concurrent work files. For example, there could be a number of concurrently open cursors that require sorting.
Consider increasing the size of the buffer pool or allocating a buffer pool specifically for DSNDB07 usage. This can be especially effective with high-use query systems whose reports make extensive use of sort activity.
Field Name: QBSTWKPD
This is an exception field.
- PREFETCH QUANTITY REDUCED TO QUARTER
The total number of times prefetch quantity is reduced from 50% to 25% of normal. The normal size depends on the page size of the buffer pool.
This field only applies to query I/O and CP parallelism.
Background and Tuning Information
The query response for parallel queries can be significantly degraded if the value in this field is not 0.
Field Name: QBSTPL2
This is an exception field.
- PREFETCH DISABLED-NO READ ENGINE
The total number of times a prefetch is disabled because of an unavailable read engine.
Background and Tuning Information
Because there are 600 read engines, a maximum of 600 concurrent prefetch operations can be processed at a time. When this maximum is reached, prefetching is disabled and this count is incremented. The value in this field should be close to 0.
Field Name: QBSTREE
This is an exception field.
- WORKFILE PAGES TO DESTRUCT
The number of pages for which destructive read was requested.
Field Name: QBSTWDRP
- FAILED COND SEQ&RDM GETPAGE REQUEST
The number of sequential and random Getpage requests which failed because the page was not in the buffer pool. Failed conditional requests do not initiate I/O operations.
Field Name: QBSTNGT
- PAGE-INS REQUIRED FOR READ
The number of page-ins required for a read I/O.
Note:A non-zero value can be accepted
if a buffer pool expansion via ALTER BPSIZE occurs.
In other situations, a non-zero value indicates
a shortage of real storage relative to the buffer pool size.Field Name: QBSTRPI
This is an exception field.
- WORKFILE PAGES NOT WRITTEN
The number of pages dequeued from VDWQ for destructive read requests.
Field Name: QBSTWBVQ
- FAILED COND SEQ GETPAGE REQUEST
The number of conditional sequential Getpage requests which failed because the page was not in the buffer pool. Failed conditional requests do not initiate I/O operations.
Field Name: QBSTNSG
- MINIMUM BUFFERS ON SLRU (LWM)
The minimum number of buffers on the sequential least-recently-used (SLRU) chain in the last statistical period. This is the low-water mark (LWM) within an interval.
Field Name: QBSTSMIN
- PAGES ADDED TO LPL
The number of times that one or more pages were added to the logical page list (LPL).
Field Name: QBSTLPL
- MAXIMUM BUFFERS ON SLRU (HWM)
The maximum number of buffers on the sequential least-recently-used (SLRU) chain in the last statistical period. This is the high-water mark (HWM) within an interval.
Field Name: QBSTSMAX
- LENGTH OF SLRU = VPSEQT
The number of times when the length of the sequential least-recently-used (SLRU) chain equals the sequential steal threshold VPSEQT.
Field Name: QBSTHST
- IN-MEM OVL RND REQUESTS
- The
number of non-sequential GETPAGE requests using
overflowed buffers.If this counter has a non-zero value,
the buffer pool size should be increased.
Field Name: QBSTAGET
This is an exception field.
- IN-MEM OVL SEQ REQUESTS
- The number
of sequential GETPAGE requests using overflowed buffers.If this counter has a non-zero value,
the buffer pool size should be increased.
Field Name: QBSTASGE
This is an exception field.
- IN-MEM OVL RND SYNC READS
- The number of synchronous read I/O operations
for non-sequential GETPAGE requests using overflowed buffers.If this counter has a non-zero value,
the buffer pool size should be increased.
Field Name: QBSTASYN
This is an exception field.
- IN-MEM OVL SEQ SYNC READS
- The number of synchronous read I/O operations
for sequential GETPAGE requests using overflowed buffers.If this counter has a non-zero value,
the buffer pool size should be increased.
Field Name: QBSTASSE
This is an exception field.
- RANDOM GETPAGE BUFFER HIT
The number of times that the random Getpage request has a buffer hit and the buffer is on the least-recently-used (SLRU) chain.
Field Name: QBSTRHS