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
- 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.