DB2 Version 9.7 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.

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

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

SYSMON
SYSCTRL
SYSMAINT
SYSADM

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 example is a sample 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	snapshot_timestamp - Snapshot timestamp monitor element
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	The database partition from which the data for the row was retrieved.