In the first part of this series on the DISPLAY command, we focused on using DISPLAY to monitor details about you database objects. In today's second installment of this series, we will look into using DISPLAY to monitor your DB2 buffer pools.
The DISPLAY BUFFERPOOL command can be issued to display the current status and allocation information for each buffer pool. For example:
We can see by reviewing these results that BP0 has been assigned 4,000 pages, all of which have been allocated. We also know that the buffers are not page fixed. The output also shows us the current settings for each of the sequential steal and deferred write thresholds.
For additional information on buffer pools you can specify the DETAIL parameter. Using DETAIL(INTERVAL) produces buffer pool usage information since the last execution of DISPLAY BUFFERPOOL.
To report on buffer pool usage since the pool was activated, specify DETAIL(*). In each case, DB2 will return detailed information on buffer-pool usage such as the number of GETPAGEs, prefetch usage, and synchronous reads. The detailed data returned after executing this command can be used for rudimentary buffer pool tuning. We can see such detail in the example above.
For example, you can monitor the read efficiency of each buffer pool using the following formula:
Finally, you can gather even more information about your buffer pools using the LIST and LSTATS parameters. The LIST parameter lists the open table spaces and indexes within the specified buffer pools; the LSTATS parameter lists statistics for the table spaces and indexes reported by LIST. Statistical information is reset each time DISPLAY with LSTATS is issued, so the statistics are as of the last time LSTATS was issued.
The DISPLAY BUFFERPOOL command can be issued to display the current status and allocation information for each buffer pool. For example:
DSNB401I =DB2Q BUFFERPOOL NAME BP0, BUFFERPOOL ID 0, USE COUNT 202
DSNB402I =DB2Q BUFFER POOL SIZE = 4000 BUFFERS AUTOSIZE = NO
ALLOCATED = 4000 TO BE DELETED = 0
IN-USE/UPDATED = 0 BUFFERS ACTIVE = 4000
DSNB406I =DB2Q PGFIX ATTRIBUTE -
CURRENT = NO
PENDING = NO
PAGE STEALING METHOD = LRU
DSNB404I =DB2Q THRESHOLDS -
VP SEQUENTIAL = 50
DEFERRED WRITE = 15 VERTICAL DEFERRED WRT = 5, 0
PARALLEL SEQUENTIAL =0 ASSISTING PARALLEL SEQT = 0
DSNB409I =DB2Q INCREMENTAL STATISTICS SINCE 11:20:17 DEC 31, 2011
DSNB411I =DB2Q RANDOM GETPAGE = 6116897 SYNC READ I/O (R) = 37632
SEQ. GETPAGE = 799445 SYNC READ I/O (S) = 10602
DMTH HIT = 0 PAGE-INS REQUIRED = 0
DSNB412I =DB2Q SEQUENTIAL PREFETCH -
REQUESTS = 11926 PREFETCH I/O = 11861
PAGES READ = 753753
DSNB413I =DB2Q LIST PREFETCH -
REQUESTS = 0 PREFETCH I/O = 0
PAGES READ = 0
For additional information on buffer pools you can specify the DETAIL parameter. Using DETAIL(INTERVAL) produces buffer pool usage information since the last execution of DISPLAY BUFFERPOOL.
To report on buffer pool usage since the pool was activated, specify DETAIL(*). In each case, DB2 will return detailed information on buffer-pool usage such as the number of GETPAGEs, prefetch usage, and synchronous reads. The detailed data returned after executing this command can be used for rudimentary buffer pool tuning. We can see such detail in the example above.
For example, you can monitor the read efficiency of each buffer pool using the following formula:
(Total GETPAGEs) / [ (SEQUENTIAL PREFETCH) +
(DYNAMIC PREFETCH) +
(SYNCHRONOUS READ)
]
A higher read efficiency value is better than a lower one because it indicates that pages, once read into the buffer pool, are used more frequently. Additionally, if buffer pool I/O is consistently high, you might consider adding pages to the buffer pool to handle more data.(DYNAMIC PREFETCH) +
(SYNCHRONOUS READ)
]
Finally, you can gather even more information about your buffer pools using the LIST and LSTATS parameters. The LIST parameter lists the open table spaces and indexes within the specified buffer pools; the LSTATS parameter lists statistics for the table spaces and indexes reported by LIST. Statistical information is reset each time DISPLAY with LSTATS is issued, so the statistics are as of the last time LSTATS was issued.
