DB2 10.5 for Linux, UNIX, and Windows

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