BP_WRITE_IO administrative view - Retrieve bufferpool write performance information

The BP_WRITE_IO administrative view returns bufferpool write performance information per bufferpool.

Important: The BP_WRITE_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_WRITE_IO administrative view
  • CONTROL privilege on the BP_WRITE_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 writes and average write time for all bufferpools on all database partitions of the currently connected database. 
SELECT SUBSTR(BP_NAME, 1, 15) AS BP_NAME, TOTAL_WRITES, 
   AVERAGE_WRITE_TIME_MS, DBPARTITIONNUM 
   FROM SYSIBMADM.BP_WRITE_IO ORDER BY DBPARTITIONNUM
The following is an example of output for this query. 
BP_NAME         TOTAL_WRITES     AVERAGE_WRITE_TIME_MS DBPARTITIONNUM
--------------- ------------...- --------------------- --------------
IBMDEFAULTBP                  11                     5              0
IBMSYSTEMBP4K                  0                     -              0
IBMSYSTEMBP8K                  0                     -              0
IBMSYSTEMBP16K                 0                     -              0
IBMSYSTEMBP32K                 0                     -              0
IBMDEFAULTBP                   0                     -              1
IBMSYSTEMBP4K                  0                     -              1
IBMSYSTEMBP8K                  0                     -              1
IBMDEFAULTBP                   0                     -              2
IBMSYSTEMBP4K                  0                     -              2
IBMSYSTEMBP8K                  0                     -              2
                                                                     
  11 record(s) selected.

Information returned

Table 1. Information returned by the BP_WRITE_IO administrative view
Column name Data type Description or corresponding monitor element
SNAPSHOT_TIMESTAMP TIMESTAMP The date and time the report was generated.
BP_NAME VARCHAR(128) bp_name - Buffer pool name
TOTAL_WRITES BIGINT Total writes.
AVERAGE_WRITE_TIME_MS BIGINT Average write time in milliseconds.
TOTAL_ASYNC_WRITES BIGINT Total asynchronous writes.
PERCENT_WRITES_ASYNC BIGINT Percent of writes that are asynchronous.
AVERAGE_ASYNC_WRITE_TIME_MS BIGINT Average asynchronous write time in milliseconds.
TOTAL_SYNC_WRITES BIGINT Total synchronous writes.
AVERAGE_SYNC_WRITE_TIME_MS BIGINT Average synchronous write time in milliseconds.
DBPARTITIONNUM SMALLINT dbpartitionnum - Database partition number monitor element
MEMBER SMALLINT member - Database member monitor element