DB2 Version 9.7 for Linux, UNIX, and Windows

MON_BP_UTILIZATION - Retrieve metrics for bufferpools

The MON_BP_UTILIZATION administrative view returns key monitoring metrics, including hit ratios and average read and write times, for all buffer pools and all database partitions in the currently connected database. It provides information that is critical for performance monitoring, because it helps you check how efficiently you are using your buffer pools.

Note: If your database was created in Version 9.7 before Fix Pack 1, to run this routine you must have already run the db2updv97 command. If your database was created before Version 9.7 , it is not necessary to run the db2updv97 command (because the catalog update is automatically taken care of by the database migration). If you downgrade to Version 9.7 , this routine will no longer work.

Authorization

One of the following authorizations is required:

SELECT privilege on the MON_BP_UTILIZATION administrative view
CONTROL privilege on the MON_BP_UTILIZATION administrative view

Information returned

Table 1. Information returned by the MON_BP_UTILIZATION administrative view
Column name	Data type	Description or Monitor element
BP_NAME	VARCHAR(128)	bp_name - Buffer pool name
MEMBER	SMALLINT	member- Database member
DATA_PHYSICAL_READS	BIGINT	Indicates the number of data pages read from the table space containers (physical) for temporary as well as regular and large table spaces. This is calculated as (`pool_data_p_reads` + `pool_temp_data_p_reads`) where `pool_data_p_reads` and `pool_temp_data_p_reads` represent the following monitor elements: pool_data_p_reads - Buffer pool data physical reads pool_temp_data_p_reads - Buffer pool temporary data physical reads
DATA_HIT_RATIO_ PERCENT	DECIMAL(5,2)	Data hit ratio, that is, the percentage of time that the database manager did not need to load a page from disk to service a data page request.
INDEX_PHYSICAL_READS	BIGINT	Indicates the number of index pages read from the table space containers (physical) for temporary as well as regular and large table spaces. This is calculated as (`pool_index_p_reads` + `pool_temp_index_p_reads`) where `pool_index_p_reads` + `pool_temp_index_p_reads` represent the following monitor elements: pool_index_p_reads - Buffer pool index physical reads pool_temp_index_p_reads - Buffer pool temporary index physical reads
INDEX_HIT_RATIO_ PERCENT	DECIMAL(5,2)	Index hit ratio, that is, the percentage of time that the database manager did not need to load a page from disk to service an index data page request.
XDA_PHYSICAL_READS	BIGINT	Indicates the number of data pages for XML storage objects (XDAs) read from the table space containers (physical) for temporary as well as regular and large table spaces. This is calculated as (`pool_xda_p_reads` + `pool_temp_xda_p_reads`) where `pool_xda_p_reads` and `pool_temp_xda_p_reads` represent the following monitor elements: pool_xda_p_reads - Buffer pool XDA data physical reads pool_temp_xda_p_reads - Buffer pool temporary XDA data physical reads
XDA_HIT_RATIO_PERCENT	DECIMAL(5,2)	Auxiliary storage objects hit ratio, that is, the percentage of time that the database manager did not need to load a page from disk to service a data page request for XML storage objects (XDAs).
TOTAL_PHYSICAL_READS	BIGINT	Indicates the number of data pages, index pages, and data pages for XML storage objects (XDAs) read from the table space containers (physical) for temporary as well as regular and large table spaces. This is calculated as (`pool_data_p_reads` + `pool_temp_data_p_reads` + `pool_index_p_reads` + `pool_temp_index_p_reads` + `pool_xda_p_reads` + `pool_temp_xda_p_reads`) where `pool_data_p_reads`, `pool_temp_data_p_reads`, `pool_index_p_reads`, `pool_temp_index_p_reads`, `pool_xda_p_reads` and `pool_temp_xda_p_reads` represent the following monitor elements: pool_data_p_reads - Buffer pool data physical reads pool_temp_data_p_reads - Buffer pool temporary data physical reads pool_index_p_reads - Buffer pool index physical reads pool_temp_index_p_reads - Buffer pool temporary index physical reads pool_xda_p_reads - Buffer pool XDA data physical reads pool_temp_xda_p_reads - Buffer pool temporary XDA data physical reads
AVG_PHYSICAL_ READ_TIME	BIGINT	Average time, in milliseconds, spent reading pages from the table space containers (physical) for all types of table spaces. If the sum of physical reads is greater than zero, this is calculated as `pool_read_time` / (`pool_data_p_reads` + `pool_temp_data_p_reads` + `pool_index_p_reads` + `pool_temp_index_p_reads` + `pool_xda_p_reads` + `pool_temp_xda_p_reads`) where `pool_read_time`, `pool_data_p_reads`, `pool_temp_data_p_reads`, `pool_index_p_reads`, `pool_temp_index_p_reads`, `pool_xda_p_reads` and `pool_temp_xda_p_reads` represent the following monitor elements: pool_read_time - Total buffer pool physical read time pool_data_p_reads - Buffer pool data physical reads pool_temp_data_p_reads - Buffer pool temporary data physical reads pool_index_p_reads - Buffer pool index physical reads pool_temp_index_p_reads - Buffer pool temporary index physical reads pool_xda_p_reads - Buffer pool XDA data physical reads pool_temp_xda_p_reads - Buffer pool temporary XDA data physical reads If the sum of physical reads is not greater than zero, NULL is returned.
PREFETCH_RATIO_ PERCENT	DECIMAL(5,2)	Percentage of pages read asynchronously (with prefetching). If many applications are reading data synchronously without prefetching, your system might not be tuned optimally.
ASYNC_NOT_READ_ PERCENT	DECIMAL(5,2)	Percentage of pages read asynchronously from disk, but never accessed by a query. If too many pages are read asynchronously from disk into the bufferpool, but no query ever accesses those pages, the prefetching might degrade performance. If the sum of asynchronous reads is greater than zero, this is calculated as `unread_prefetch_pages` / (`pool_async_data_reads` + `pool_async_index_reads` + `pool_async_xda_reads`) where `unread_prefetch_pages`, `pool_async_data_reads`, `pool_async_index_reads` and `pool_async_xda_reads` represent the following monitor elements: unread_prefetch_pages - Unread prefetch pages pool_async_data_reads - Buffer pool asynchronous data reads pool_async_index_reads - Buffer pool asynchronous index reads pool_async_xda_reads - Buffer pool asynchronous XDA data reads If the sum of asynchronous reads is not greater than zero, NULL is returned.
TOTAL_WRITES	BIGINT	The number of times a data, index, or data page for an XML storage object (XDA) was physically written to disk. This is calculated as (`pool_data_writes` + `pool_index_writes` + `pool_xda_writes`) where `pool_data_writes`, `pool_index_writes`, and `pool_xda_writes` represent the following monitor elements: pool_data_writes - Buffer pool data writes pool_index_writes - Buffer pool index writes pool_xda_writes - Buffer pool XDA data writes
AVG_WRITE_TIME	BIGINT	Average time, in milliseconds, spent physically writing pages from the buffer pool to disk. If the sum of write operations is greater than zero, this is calculated as `pool_write_time` / (`pool_data_writes` + `pool_index_writes` + `pool_xda_writes`) where `pool_write_time`, `pool_data_writes`, `pool_index_writes`, and `pool_xda_writes` represent the following monitor elements: pool_write_time - Total buffer pool physical write time pool_data_writes - Buffer pool data writes pool_index_writes - Buffer pool index writes pool_xda_writes - Buffer pool XDA data writes If the sum of write operations is not greater than zero, NULL is returned.
SYNC_WRITES_PERCENT	DECIMAL(5,2)	Percentage of write operations that are synchronous.
AVG_SYNC_READ_TIME	BIGINT	Average time, in milliseconds, spent synchronous reading from bufferpool. This is calculated as (`pool_read_time` - `pool_async_read_time`) / (`total_physical_reads` - `total_async_reads`) If `pool_read_time` - `pool_async_read_time` is zero, NULL is returned.
AVG_ASYNC_READ_TIME	BIGINT	Average time, in milliseconds, spent asynchronous reading from bufferpool. This is calculated as `pool_async_read_time` / `total_async_reads` If `pool_async_read_time` is zero, NULL is returned.
AVG_SYNC_WRITE_TIME	BIGINT	Average time, in milliseconds, spent synchronous writing from bufferpool. This is calculated as (`pool_write_time` - `pool_async_write_time`) / (`total_writes` - `total_async_writes`) If `pool_write_time` - `pool_async_write_time` is zero, NULL is returned.
AVG_ASYNC_WRITE_TIME	BIGINT	Average time, in milliseconds, spent asynchronous writing from bufferpool. This is calculated as `pool_async_write_time` / `total_async_writes` If `pool_async_write_time` is zero, NULL is returned.