DB2 Version 9.7 for Linux, UNIX, and Windows

pool_index_writes - Buffer pool index writes monitor element

Indicates the number of times a buffer pool index page was physically written to disk.

Table 1. Table Function Monitoring Information
Table Function	Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics	DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics	REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document)	REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics	REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document)	REQUEST METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics	DATA OBJECT METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics	REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document)	REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics	REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document)	REQUEST METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document)	ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache	ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries	ACTIVITY METRICS BASE

Table 2. Snapshot Monitoring Information
Snapshot Level	Logical Data Grouping	Monitor Switch
Database	dbase	Buffer Pool
Table Space	tablespace	Buffer Pool
Buffer Pool	bufferpool	Buffer Pool
Application	appl	Buffer Pool

For snapshot monitoring, this counter can be reset.

Table 3. Event Monitoring Information
Event Type	Logical Data Grouping	Monitor Switch
Activities	event_activity (reported in the details_xml document)	ACTIVITY METRICS BASE
Activities	event_activitymetrics	ACTIVITY METRICS BASE
Statistics	event_scstats (reported in the metrics document)	REQUEST METRICS BASE
Statistics	event_wlstats (reported in the metrics document)	REQUEST METRICS BASE
Unit of work	Reported in the system_metrics document.	REQUEST METRICS BASE
Database	event_db	-
Tablespaces	event_tablespace	-
Connection	event_conn	-
Package cache	Reported in the activity_metrics document.	ACTIVITY METRICS BASE
Statistics	event_scmetrics*	REQUEST METRICS BASE
Statistics	event_wlmetrics*	REQUEST METRICS BASE
* When returned as part of this logical data group, this element reflects the change in value of this metric since the last statistics collection or database activation, whichever was more recent.

Usage

Like a data page, a buffer pool index page is written to disk for the following reasons:

To free a page in the buffer pool so another page can be read
To flush the buffer pool

The system does not always write a page to make room for a new one. If the page has not been updated, it can simply be replaced. This replacement is not counted for this element.

The index page can be written by an asynchronous page-cleaner agent before the buffer pool space is required. These asynchronous index page writes are included in the value of this element in addition to synchronous index page writes (see the pool_async_index_writes monitor element).

If a buffer pool index page is written to disk for a high percentage of the value of the pool_index_p_reads monitor element, you may be able to improve performance by increasing the number of buffer pool pages available for the database.

When calculating this percentage, disregard the number of physical reads required to initially fill the buffer pool. To determine the number of pages written:

Run your application (to load the buffer).
Note the value of this element.
Run your application again.
Subtract the value recorded in step 2 from the new value of this element.

In order to prevent the buffer pool from being deallocated between the runnings of your application, you should do one of the following:

Activate the database with the ACTIVATE DATABASE command.
Have an idle application connected to the database.

If all applications are updating the database, increasing the size of the buffer pool may not have much impact on performance, since most of the pages contain updated data which must be written to disk.