MON_GET_PAGE_ACCESS_INFO table function - Get buffer pool page waiting information

The MON_GET_PAGE_ACCESS_INFO table function returns information about bufferpool pages that are being waited on for a specified table. This is only applicable to Db2® pureScale® instances.

Syntax

The schema is SYSPROC.

Table function parameters

tabschema: An input argument of type VARCHAR(128) that specifies the table schema to query. If the argument is null or an empty string, information for all schemas is returned.
tabname: An input argument of type VARCHAR(128) that specifies the table name to query. If the argument is null or an empty string, information for all tables is returned.
member: An input argument of type INTEGER that specifies a valid member number in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all active database members. If the null value is specified, -1 is set implicitly.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the table function
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Example

This example returns page reclaim counts for all tables in schema BASETAB on the currently connected member. It shows that applications are waiting for pages for table TABLE1 (an example of what could cause this situation is updating different rows on the same page from two different members).

SELECT SUBSTR(TABNAME,1,8) AS NAME,
       SUBSTR(OBJTYPE,1,5) AS TYPE,
       PAGE_RECLAIMS_X AS PGRCX,
       PAGE_RECLAIMS_S AS PGRCS,
       SPACEMAPPAGE_PAGE_RECLAIMS_X AS SMPPGRCX,
       SPACEMAPPAGE_PAGE_RECLAIMS_S AS SMPPGRCS
   FROM TABLE( MON_GET_PAGE_ACCESS_INFO('BASETAB', NULL, NULL) ) AS WAITMETRICS

   ORDER BY NAME;

The following is an example of output from this query.

NAME     TYPE  PGRCX   PGRCS  SMPPGRCX  SMPPGRCS
-------- ----- ------  -----  --------  --------
TABLE1   TABLE  12641    320        72        17
TABLE1   INDEX   5042     78         7         0
TABLE2   TABLE    420     12         9         0 
TABLE2   INDEX      7      0         0         0

Usage notes

The MON_GET_PAGE_ACCESS_INFO table function returns one or two rows of data for each table per member that it gathers data from. One row of data shows information for INDEX pages. The second row shows information for DATA pages. No aggregation across members is performed.

Information returned

Table 1. Information returned for MON_GET_PAGE_ACCESS_INFO
Column name	Data type	Description or corresponding monitor element
TABSCHEMA	VARCHAR(128)	table_schema - Table schema name
TABNAME	VARCHAR(128)	table_name - Table name
OBJTYPE	VARCHAR(128)	Type of object. One of: INDEX TABLE
MEMBER	SMALLINT	member - Database member
PAGE_RECLAIMS_X	BIGINT	page_reclaims_x - Page reclaims exclusive access
PAGE_RECLAIMS_S	BIGINT	page_reclaims_s - Page reclaims shared access
SPACEMAPPAGE_PAGE_RECLAIMS_X	BIGINT	spacemappage_page_reclaims_x - Space map page reclaims exclusive access
SPACEMAPPAGE_PAGE_RECLAIMS_S	BIGINT	spacemappage_page_reclaims_s - Space map page reclaims shared access
PAGE_RECLAIMS_INITIATED_X	BIGINT	page_reclaims_initiated_x - Page reclaims initiated exclusive access
PAGE_RECLAIMS_INITIATED_S	BIGINT	page_reclaims_initiated_s - Page reclaims initiated shared access
SPACEMAPPAGE_PAGE_RECLAIMS_INITIATED_X	BIGINT	spacemappage_page_reclaims_initiated_x - Space map page reclaims initiated exclusive access monitor element
SPACEMAPPAGE_PAGE_RECLAIMS_INITIATED_S	BIGINT	spacemappage_page_reclaims_initiated_s - Space map page reclaims initiated shared access monitor element
RECLAIM_WAIT_TIME	BIGINT	reclaim_wait_time - Reclaim wait time
SPACEMAPPAGE_RECLAIM_WAIT_TIME	BIGINT	spacemappage_reclaim_wait_time - Space map page reclaim wait time
DATA_PARTITION_ID	INTEGER	data_partition_id - Data partition identifier
IID	SMALLINT	iid - Index identifier This monitor element only returns a value when the OBJTYPE is INDEX and the index is a nonpartitioned type on a partitioned table. Otherwise, the value of this monitor element is NULL.