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.

Important: The BP_READ_IO administrative view is deprecated and has been replaced by the MON_BP_UTILIZATION administrative view - Retrieve metrics for bufferpools.

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
  • DBADM authority
  • SQLADM authority
In addition, to access snapshot monitor data, one of the following authorities is also required:
  • SYSMON
  • SYSCTRL
  • SYSMAINT
  • SYSADM

Default PUBLIC privilege

In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.

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 is an example of 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 Date and time the report was generated.
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 dbpartitionnum - Database partition number monitor element
MEMBER SMALLINT member - Database member monitor element