Buffer Pool Detail

This panel provides detailed information about the current activity in the specified Db2 buffer pool.

If a buffer pool is defined but currently not used by Db2, the value 0 is displayed for all fields 0 except for the field VP Sizew. If the buffer pool is used by Db2, the accumulated counts are displayed.

________________ ZBP      VTM     O2       V550./I DC11 01/15/16 17:00:13   2  
> Help PF1               Back PF3               Up PF7           Down PF8       
>                                                                              
> *-BUFFER POOL    B-GROUP BUFFER POOL    C-BUFFER POOL SNAPSHOT   H-HISTORICAL 
=============================================================================== 
>                            BUFFER POOL DETAIL                                 
                                                                                
 BP    0                                                                        
+ Collection Interval:  REALTIME                         Start:  06/25 16:55:03 
+ Report Interval:       5 min                             End:  06/25 17:00:13 
+                                                                               
+ Virtual Buffer Pool Size=      5000    VPOOL Frame Size            =      4K  
+ VPOOL Buffers Allocated =      5000    Minimum VPOOL Size          =       0  
+ VPOOL Buffers in Use    =       130    Maximum VPOOL Size          =       0  
+ VPOOL Buffers to be Del =         0    Auto Size                   =       N  
+ Use Count               =       216    Page Fix                    =       N  
+ VP Sequential Thresh    =       80%                                           
+ Deferred Write Thresh   =       30%    Vert Deferred Write Thresh  =      5%  
+ VP Parallel Seq Thresh  =       50%    Sysplex Parallel Thresh     =      0%  
+                                                                               
+ Getpages per Sync I/O   = 453195.61    Pages Written per Write I/O =    7.79  
+ Prefetch per I/O        =    511.92    Pages Read per Prefetch     =     .05  
+ Seq Prefetch per I/O    =      2.99    Pages Read per Seq Prefetch =   18.65  
+ List Prefetch per I/O   =      1.33    Pages Read per List Prefetch=    5.03  
+ Dyn Prefetch per I/O    =    726.59    Pages Read per Dyn Prefetch =     .02  
+ Max Concur Prefetch     =        56    Workfile Maximum            =       7  
+ BP Hit % - Random       =     99.9%    Virtual Page Steal Method   =     LRU  
+ BP Hit % - Sequential   =     99.9%    Random Getpage Buffer Hit   =     337                                       
+ LWM Buffers on SLRU     =       316    HWM Buffers on SLRU         =     316                                                                       
                                                                              
+                                    TOTAL  INTERVAL  /SECOND  /THREAD  /COMMIT 
+                                 QUANTITY  QUANTITY  (  310)  (    3)  (    0) 
+                                 --------  --------  -------  -------  ------- 
+ Getpage Requests                 144935K        30      .09    10.00      .00 
+  Getpage Requests - Sequential   144758K         0      .00      .00      .00 
+   Getpage Req - Seq Ovfl         144758K         0      .00      .00      .00 *DB2 12
+  Getpage Requests - Random        176553        30      .09    10.00      .00 
+   Getpage Req - Random Ovfl      144935K         0      .00      .00      .00 *DB2 12
+ Getpage Failed - VPOOL Full            0         0      .00      .00      .00 
+ Getpage Failed - Cond Request          0         0      .00      .00      .00 
+ Getpage Failed - Cond SeqReq           0         0      .00      .00      .00 
+                                                                               
+ Sync Read I/O Operations           18592        11      .03     3.66      .00 
+  Sync Read I/O - Sequential          337         0      .00      .00      .00 
+   Sync Read I/O - Seq Ovfl           337         0      .00      .00      .00 *DB2 12
+  Sync Read I/O - Random            18255        11      .03     3.66      .00 
+   Sync Read I/O - Random Ovfl      18255         0      .00      .00      .00 *DB2 12
+ Pages Read via Seq Prefetch        14569         0      .00      .00      .00 
+ Seq Prefetch I/O Operations          261         0      .00      .00      .00 
+ Sequential Prefetch Requests         781         0      .00      .00      .00 
+ Pages Read via List Prefetch         181         0      .00      .00      .00 
+ List Prefetch I/O Operations          27         0      .00      .00      .00 
+ List Prefetch Requests                36         0      .00      .00      .00 
+ Pages Read via Dyn Prefetch        10447         0      .00      .00      .00 
+ Dyn Prefetch I/O Operations          683         0      .00      .00      .00 
+ Dyn Prefetch Requests             496260         0      .00      .00      .00 
+ Prefetch Failed - No Buffer            0         0      .00      .00      .00 
+ Prefetch Failed - No Engine            0         0      .00      .00      .00 
+                                                                               

+ Parallel Group Requests                6         0      .00      .00      .00 
+ Prefetch I/O Streams Reduced           0         0      .00      .00      .00 
+ Parallelism Downgraded                 0         0      .00      .00      .00 
+ Prefetch Quan Reduced to 1/2      337946         0      .00      .00      .00 
+ Prefetch Quan Reduced to 1/4           0         0      .00      .00      .00 
+ Pages Added to LPL                     0         0      .00      .00      .00 
+ Length of SLRU=VPSEQT                  0         0      .00      .00      .00 
+ Pages Updated                     870402        11      .03     3.66      .00 
+ Pages Written                      21848         0      .00      .00      .00 
+ Page-in Required for Write I/O       109         0      .00      .00      .00 
+ Write I/O Operations                 893         0      .00      .00      .00 
+ Immediate (Sync) Writes             1911         0      .00      .00      .00 
+                                                                               
+ Vert Defer Wrt Thresh Reached        203         0      .00      .00      .00 
+ Deferred Write Thresh Reached          0         0      .00      .00      .00 
+ Data Manager Thresh Reached            0         0      .00      .00      .00 
+                                                                               
+ Successful VPOOL Expand/Contr          0         0      .00      .00      .00 
+ VPOOL or HPOOL Expand Failed           0         0      .00      .00      .00 
+                                                                               
+ Successful Dataset Opens             190         0      .00      .00      .00 
+ DFHSM Recall                           0         0      .00      .00      .00 
+ DFHSM Recall Timeouts                  0         0      .00      .00      .00 
+                                                                               
+ Sort Merge Passes                      1         0      .00      .00      .00 
+ Sort/Merge Workfile Requests           7         0      .00      .00      .00 
+ Sort/Merge Workfile Req Denied         0         0      .00      .00      .00 
+ Sort Merge Pass - Buff Short           0         0      .00      .00      .00 
+ Workfile Prefetch Disabled             0         0      .00      .00      .00 
+ Workfile Create Failed-No Buff         0         0      .00      .00      .00
+ Destructive Read Requests           2162         0      .00      .00      .00
+ Destructive Read Page Dequeue          1         0      .00      .00      .00
===============================================================================

Highlighting

OMEGAMONĀ® for Db2 PE highlights some fields in this panel to draw your attention to their current status.
Table 1. Highlighted fields in Buffer Pool Detail panel
Field Exception Reason
Number of Pages in Use BMTH The percentage of pages in use has reached the predetermined threshold.

Navigation

For additional information about
  • buffer pool contents, select option A-BUFFER POOL at the top of the panel.
  • pageset information about the selected buffer pool, select option C-Buffer Pool Snapshot. For more information, see the description of panel Buffer Pool Snapshot Open Pagesets.
  • Near-term history activity, select option H-HISTORICAL at the top of the panel.
  • other topics, use the PF keys.

Fields

Collection Interval
Shows the REALTIME to indicate that you are looking at the realtime version of this panel and not at the corresponding near-term history panel. The collection interval and the report interval are the same in this panel.
Start
The start time of the report interval currently displayed.
Report Interval
The time in the last cycle (for example, between two presses of the Enter key).
End
The end time of the current report interval is displayed.
Virtual Buffer Pool Size
The number of buffers that are allocated for an active virtual buffer pool.
VPOOL Frame Size (QDBPFRAM)
For the Frame Size attribute, you can specify the values 4K, 1M, or 2G.

This applies to Db2 11 and higher.

VPOOL Buffers Allocated
The number of virtual buffer pool pages that are allocated at the end of the interval.
Minimum VPOOL Size (QDBPVPMI)
The VPSIZEMIN attribute.

This applies to Db2 11 and higher.

VPOOL Buffer in Use
The number of virtual buffer pool pages that are used at the end of the interval. This value is a snapshot value of the current number of non-stealable buffers. A buffer is called non-stealable, if one of the following conditions apply:
  • It has an outstanding GETPAGE. This means that someone is currently looking at this page.
  • It is updated, however, it is not yet written to DASD.
Maximum VPOOL Size (QDBPVPMA)
The VPSIZEMAX attribute.

This applies to Db2 11 and higher.

VPOOL Buffers to be Del
The number of pages that can be deleted from an active virtual buffer pool as a result of pool contraction.
Auto Size
Determines whether Auto Size is used. You can specify Y (YES) or N (NO).
Use Count
The number of open tablespaces or indexspaces in this buffer pool.
Page Fix
Determines whether a page is fixed in real storage when it is first used. You can specify Y (YES) or N (NO).
VP Sequential Thresh
Sequential Steal threshold for the virtual buffer pool VPSEQT. The percentage of the virtual buffer pool that can be occupied by sequentially accessed pages. If set to zero, prefetch is disabled.
Deferred Write Thresh
Deferred Write threshold for the virtual buffer pool DWQT. Write operations are scheduled when the percentage of unavailable pages in the virtual buffer pool exceeds this threshold to decrease the unavailable pages to 10% below the threshold.
Vert Deferred Write Thresh
Vertical Deferred Write threshold for the virtual buffer pool VDWQT. The percentage of the buffer pool that might be occupied by updated pages from a single data set.
VP Parallel Seq Thresh
Parallel I/O sequential threshold VPPSEQT. This threshold determines how much of the virtual buffer pool might be used for parallel I/O operations. It is expressed as a percentage of VPSEQT. If set to zero, I/O parallelism is disabled.
Sysplex Parallel Thresh
Virtual buffer pool assisting parallel sequential threshold. This threshold determines how much of the virtual buffer pool might support parallel I/O operations from another Db2 in a data sharing group.
Getpages per Sync I/O
The ratio of Getpage requests to Read I/Os.
Pages Written per Write I/O
The ratio of pages written to physical I/Os.
Prefetch per I/O
The ratio of Prefetch requests to physical I/Os.
Pages Read per Prefetch
The ratio of pages read by Prefetch processing to total Prefetch requests.
Seq Prefetch per I/O
The ratio of pages read by Sequential Prefetch to Sequential Prefetch I/O.
Pages Read per Seq Prefetch
The ratio of pages read by Sequential Prefetch to Sequential Prefetch requests.
List Prefetch per I/O
The ratio of List Prefetch requests to List Prefetch I/Os.
Pages Read per List Prefetch
The ratio of pages read by List Prefetch to List Prefetch requests.
Dyn Prefetch per I/O
The ratio of Dynamic Prefetch request to Dynamic Prefetch I/Os.
Pages Read per Dyn Prefetch
The ratio of pages read by Dynamic Prefetch to Dynamic Prefetch request.
Max Concur Prefetch I/O
The highest number of concurrent prefetch I/O streams that were allocated to support I/O parallelism.
Workfile Maximum
The maximum number of work files that are allocated during sort/merge processing during the current statistics period.
BP Hit % - Random
The percentage of times that Db2 performed a Getpage operation with a random request and the page was already in the buffer pool. A DASD read was not required.
BP Hit % - Sequential
The percentage of times that Db2 performed a Getpage operation with a sequential request and the page was already in the buffer pool. A DASD read was not required.
Virtual Page Steal Method
Determines when and whether performance-critical objects in buffer pools are removed from buffer pools when the space is needed by other objects. Valid values:
LRU
Least Recently Used objects are removed first.
FIFO
Oldest objects are removed first (First-In-First-Out).
NONE
Objects are not removed from buffer pool (no page stealing). This setting provides the highest availability for business-critical objects.
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. Db2 11 and later. (Field name: QBSTRHS)
LWM Buffers on SLRU
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. Db2 11 and later. (Field name: QBSTSMIN)
HWM Buffers on SLRU
The maximum 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. Db2 11 and later. (Field name: QBSTSMAX)
For each of the following fields, the following statistics values are provided:
TOTAL QUANTITY
Total quantity, which reflects the amount of activities since Db2 was started.
INTERVAL QUANTITY
Interval quantity, which reflects the amount of activities during the last cycle.
/SECOND
Rate per second during the last cycle. The number under /SECOND (in parentheses) is the number of seconds in the interval.
/THREAD
Rate per thread during the last cycle. The number under /THREAD (in parentheses) is the number of Create Threads during the interval.
/COMMIT
Rate per Commit during the last cycle. The number under /COMMIT (in parentheses) is the number of commit requests (including abort requests) during the interval.
Getpage Requests
The number of Getpage requests for the buffer pool.
Getpage Requests - Sequential
The number of Getpage requests issued by sequential access requestors.
Getpage Requests - Random
The number of Getpage requests issued by random access requestors.
Getpage Requests - Seq Ovfl
The number of sequential GETPAGE requests using overflowed buffers. Db2 12 and later.
Getpage Requests - Random Ovfl
The number of non-sequential GETPAGE requests using overflowed buffers. Db2 12 and later.
Getpage Failed - VPOOL Full
The number of Getpage failures due to unavailable buffers because the virtual buffer pool is full.
Getpage Failed - Cond Request
The number of conditional getpage requests that cannot be satisfied for this buffer pool. A conditional getpage is used with parallel I/O processing only.
Getpage Failed - Cond SeqReq
The number of conditional sequential getpage requests that failed because the page is not in the buffer pool.
This applies to Db2 9 or higher.
Sync Read I/O Operations
Synchronous read operations done by Db2.
Sync Read I/Os - Sequential
The number of synchronous Read I/O operations performed by sequential requests.
Sync Read I/Os - Random
The number of synchronous Read I/O operations performed by random requests.
Sync Read I/Os - Seq Ovfl
The number of synchronous Read I/O operations performed for sequential GETPAGE requests using overflowed buffers. Db2 12 and later.
Sync Read I/Os - Random Ovfl
The number of synchronous Read I/O operations performed for non-sequential GETPAGE requests using overflowed buffers. Db2 12 and later.
Pages Read via Seq Prefetch
The pages that are read as a result of sequential prefetch requests.
Seq Prefetch I/O Operations
The number of asynchronous read I/Os that are caused by sequential prefetch.
Sequential Prefetch Requests
Sequential prefetch requests for the buffer pool.
Pages Read via List Prefetch
The pages read as a result of list prefetch requests.
List Prefetch I/O Operations
The number of asynchronous read I/Os caused by list prefetch.
List Prefetch Requests
The List prefetch requests for the buffer pool.
Pages Read via Dyn Prefetch
The pages read as a result of dynamic prefetch requests. Dynamic prefetch is triggered because of sequential detection.
Dyn Prefetch I/O Operations
The number of asynchronous read I/Os caused by dynamic prefetch.
Dyn Prefetch Requests
The number of dynamic prefetch requests for the buffer pool.
Prefetch Failed - No Buffer
Failures of prefetch because the sequential prefetch threshold (SPTH) is reached.
Prefetch Failed - No Engine
Failures of prefetch because the maximum number of concurrent prefetches is reached. You cannot change this maximum value.
Parallel Group Requests
The number of requests made for processing queries in parallel.
Prefetch I/O Streams Reduced
The number of requested prefetch I/O streams that are denied because of a buffer pool shortage. This applies only for non-work file pagesets for queries that are processed in parallel.
Parallelism Downgraded
The number of times the requested number of buffers to allow a parallel group to run to the planned degree cannot be allocated because of a buffer pool shortage.
Prefetch Quan Reduced to 1/2
The number of times the sequential prefetch quantity is reduced from normal to one-half of normal. This is done to continue to allow execution concurrently with parallel I/O.
Prefetch Quan Reduced to 1/4
The number of times the sequential prefetch quantity is reduced from one-half to one-quarter of normal.
Pages Added to LPL
The number of times one or more pages are added to logical page lists (LPL). (Field name: QBSTLPL)
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. Db2 11 and later. (Field name: QBSTHST)
Pages Updated
The number of updates to pages in the buffer pool.
Pages Written
The number of buffer pool pages written to DASD.
Page-in Required for Write I/O
The number of page-ins that are required for write I/O operations.
Write I/O Operations
The number of Write operations performed by media manager for both synchronous and asynchronous I/O.
Immediate (Sync) Writes
Immediate writes to DASD. This value is incremented when 97.5% of the buffer pool pages are used. In addition, Db2 might flag buffer pool pages for immediate write during checkpoint processing and when a database is stopped.
Consider the value of Data Manager Threshold Reached (DMTH) when interpreting this value. If DMTH is zero, this value is probably insignificant.
Vert Defer Wrt Threshold Reached
The number of times the vertical write threshold is hit.
Deferred Write Threshold Reached
The Deferred Write Threshold (DWTH) is reached. This occurs in the following situations:
  • If Db2 uses 50% of the buffer pool.
  • If a data set has updated 10% of the buffer pool pages or 64 pages. At this threshold, Db2 forces writes to free pool space.
Data Manager Threshold Reached
Data manager threshold (DMTH) is reached. This occurs when Db2 uses 95% of the buffer pool minimum value and begins to operate at the row level instead of the page level. When this occurs, CPU usage increases considerably.
Successful VPOOL Expand/Contract
The number of successful virtual pool expansions or contractions because of the ALTER BUFFERPOOL command.
VPOOL Expand Failed
The number of virtual buffer pool expansion failures.
Successful Dataset Opens
The number of data set open operations that are successful.
DFHSM Recall
Attempts to access data sets that are migrated by DFHSM.
DFHSM Recall Timeouts
Unsuccessful attempts to recall data sets because the timeout threshold (DSNZPARM RECALLD) is exceeded.
Sort Merge Passes
The number of merge passes for Db2 sort/merge processing.
Sort/Merge Workfile Requests
The number of work files requested during sort/merge processing.
Sort/Merge Workfile Requests Denied
The number of requests for work files that are denied during merge processing because of insufficient buffer resources.
Sort Merge Pass - Buff Short
The number of times that sort/merge cannot efficiently perform because of insufficient buffer resources. This field is incremented when the number of work files allowed is less than the number of work files requested.
Workfile Prefetch Disabled
The number of times a workfile prefetch is not scheduled because the prefetch quantity is zero.
Workfile Create Failed - No Buff
The number of times a work file is not created because of insufficient buffers (MVS/XA only).
Destructive Read Requests
The number of pages that are requested for destructive read processing.
Destructive Read Page Dequeue
The number of pages that are dequeued for destructive read processing.