DB2 Version 9.7 for Linux, UNIX, and Windows

BP_READ_IO administrative view - Retrieve bufferpool read performance information

The BP_READ_IO administrative view returns bufferpool read performance information. This view can be used to look at each bufferpool to see how effective the prefetchers are.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

SELECT privilege on the BP_READ_IO administrative view
CONTROL privilege on the BP_READ_IO administrative view
DATAACCESS authority

In addition, to access snapshot monitor data, one of the following authorities is also required:

SYSMON
SYSCTRL
SYSMAINT
SYSADM

Example

Retrieve total physical reads and average read time for all bufferpools on all partitions of the currently connected database.

SELECT SUBSTR(BP_NAME, 1, 15) AS BP_NAME, TOTAL_PHYSICAL_READS, 
   AVERAGE_READ_TIME_MS, DBPARTITIONNUM
   FROM SYSIBMADM.BP_READ_IO ORDER BY DBPARTITIONNUM

The following example is a sample output for this query.

BP_NAME         TOTAL_PHYSICAL_READS AVERAGE_READ_TIME_MS DBPARTITIONNUM
--------------- -------------------- -------------------- --------------
IBMDEFAULTBP                     811                    4              0
IBMSYSTEMBP4K                      0                    -              0
IBMSYSTEMBP8K                      0                    -              0
IBMSYSTEMBP16K                     0                    -              0
IBMDEFAULTBP                      34                    0              1
IBMSYSTEMBP4K                      0                    -              1
IBMSYSTEMBP8K                      0                    -              1
IBMDEFAULTBP                      34                    0              2
IBMSYSTEMBP4K                      0                    -              2
IBMSYSTEMBP8K                      0                    -              2
                                                                        
   10 record(s) selected.

Information returned

Table 1. Information returned by the BP_READ_IO administrative view
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp monitor element
BP_NAME	VARCHAR(128)	bp_name - Buffer pool name
TOTAL_PHYSICAL_READS	BIGINT	Total physical reads.
AVERAGE_READ_TIME_MS	BIGINT	Average read time in milliseconds.
TOTAL_ASYNC_READS	BIGINT	Total asynchronous reads.
AVERAGE_ASYNC_READ_TIME_MS	BIGINT	Average asynchronous read time in milliseconds.
TOTAL_SYNC_READS	BIGINT	Total synchronous reads.
AVERAGE_SYNC_READ_TIME_MS	BIGINT	Average synchronous read time in milliseconds.
PERCENT_SYNC_READS	DECIMAL(5,2)	Percentage of pages read synchronously without prefetching. If many of the applications are reading data synchronously without prefetching then the 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, then the prefetching might degrade performance.
DBPARTITIONNUM	SMALLINT	The database partition from which the data was retrieved for this row.