MON_GET_INDEX table function - get index metrics

The MON_GET_INDEX table function returns metrics for one or more indexes.

Syntax

The schema is SYSPROC.

Table function parameters

tabschema

An input argument of type VARCHAR(128) that specifies a valid table schema name in the same database as the one currently connected to when calling this function. If the argument is NULL or an empty string, metrics are retrieved for indexes of tables in all schemas in the database. If the argument is specified, metrics are only returned for indexes for tables in the specified schema.

tabname

An input argument of type VARCHAR(128) that specifies a valid table name in the same database as the one currently connected to when calling this function. Metrics are returned for all indexes on the specified table. If the argument is null or an empty string, metrics are retrieved for all indexes for all tables in the database.

member

An input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database when calling this function. Specify a -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 authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Example

Identify the most frequently used indexes on the DMEXT002.TABLE1 table, since the last database activation:

  SELECT VARCHAR(S.INDSCHEMA, 10) AS INDSCHEMA, 
    VARCHAR(S.INDNAME, 10) AS INDNAME, 
    T.DATA_PARTITION_ID, 
    T.MEMBER, 
    T.INDEX_SCANS, 
    T.INDEX_ONLY_SCANS 
  FROM TABLE(MON_GET_INDEX('DMEXT002','TABLE1', -2)) as T, SYSCAT.INDEXES AS S 
  WHERE T.TABSCHEMA = S.TABSCHEMA AND 
    T.TABNAME = S.TABNAME AND 
    T.IID = S.IID   
  ORDER BY INDEX_SCANS DESC

The following is an example of output from this query.

      INDSCHEMA  INDNAME    DATA_PARTITION_ID MEMBER         INDEX_SCANS          INDEX_ONLY_SCANS
      ---------- ---------- ----------------- -------------- -------------------- --------------------
      DMEXT002   INDEX3                     -              0                    1                    1
      DMEXT002   INDEX4                     -              0                    1                    0
      DMEXT002   INDEX1                     -              0                    0                    0
      DMEXT002   INDEX2                     -              0                    0                    0
      DMEXT002   INDEX5                     -              0                    0                    0
      DMEXT002   INDEX6                     -              0                    0                    0

        6 record(s) selected.

Usage notes

The MON_GET_INDEX table function returns one row of data per index, and per database member. If partitioned indexes are being used, one row is returned for each index partition per database member. No aggregation across database members is performed. However, aggregation can be achieved through SQL queries as shown in the previous example.

Metrics will only be returned for indexes on tables that have been accessed since the database was activated. All counters represent data since the current database activation. For example, the pseudo_empty_pages counter is the number of pages that have been identified as pseudo empty since the database was activated. it is not the current number of pseudo empty pages in the index.

The metrics returned by this function are collected at two levels. Some metrics are always collected, while the collection of other metrics is controlled at the database level by using the mon_obj_metrics configuration parameter. Refer to the descriptions provided in the following table to determine if any settings must be active for data to be collected for a particular metric.

Information returned

Table 1. Information returned for MON_GET_INDEX
Column Name	Data Type	Description or corresponding monitor element
TABSCHEMA	VARCHAR(128)	table_schema - Table schema name
TABNAME	VARCHAR(128)	table_name - Table name
IID	SMALLINT	iid - Index identifier
MEMBER	SMALLINT	member- Database member
DATA_PARTITION_ID	INTEGER	data_partition_id - Data partition identifier
NLEAF	BIGINT	nleaf - Number of leaf pages
NLEVELS	SMALLINT	nlevels - Number of index levels
INDEX_SCANS	BIGINT	index_scans - Index scans
INDEX_ONLY_SCANS	BIGINT	index_only_scans - Index-only scans
KEY_UPDATES	BIGINT	key_updates - Key updates
INCLUDE_COL_UPDATES	BIGINT	include_col_updates - Include column updates
PSEUDO_DELETES	BIGINT	pseudo_deletes - Pseudo deletes
DEL_KEYS_CLEANED	BIGINT	del_keys_cleaned - Pseudo deleted keys cleaned
ROOT_NODE_SPLITS	BIGINT	root_node_splits - Root node splits
INT_NODE_SPLITS	BIGINT	int_node_splits - Intermediate node splits
BOUNDARY_LEAF_NODE_SPLITS	BIGINT	boundary_leaf_node_splits - Boundary leaf node splits
NONBOUNDARY_LEAF_NODE_SPLITS	BIGINT	nonboundary_leaf_node_splits - Non-boundary leaf node splits
PAGE_ALLOCATIONS	BIGINT	page_allocations - Page allocations
PSEUDO_EMPTY_PAGES	BIGINT	pseudo_empty_pages - Pseudo empty pages
EMPTY_PAGES_REUSED	BIGINT	empty_pages_reused - Empty pages reused
EMPTY_PAGES_DELETED	BIGINT	empty_pages_deleted - Empty pages deleted
PAGES_MERGED	BIGINT	pages_merged - Pages merged
OBJECT_INDEX_L_READS	BIGINT	object_index_l_reads - Buffer pool index logical reads for an index
OBJECT_INDEX_P_READS	BIGINT	object_index_p_reads - Buffer pool index physical reads for an index
OBJECT_INDEX_GBP_L_READS	BIGINT	object_index_gbp_l_reads - Group buffer pool index logical reads for an index
OBJECT_INDEX_GBP_P_READS	BIGINT	object_index_gbp_p_reads - Group buffer pool index physical reads for an index
OBJECT_INDEX_GBP_INVALID_PAGES	BIGINT	object_index_gbp_invalid_pages - Group buffer pool invalid index pages for an index
OBJECT_INDEX_LBP_PAGES_FOUND	BIGINT	object_index_lbp_pages_found - Local buffer pool index pages found for an index
OBJECT_INDEX_GBP_INDEP_PAGES_FOUND_IN_LBP	BIGINT	object_index_gbp_indep_pages_found_in_lbp - Group buffer pool independent index pages found in local buffer pool
INDEX_JUMP_SCANS	BIGINT	index_jump_scans - Index jump scans
OBJECT_INDEX_CACHING_TIER_L_READS	BIGINT	Reserved for future use.
OBJECT_INDEX_CACHING_TIER_PAGES_FOUND	BIGINT	Reserved for future use.
OBJECT_INDEX_CACHING_TIER_GBP_INVALID_PAGES	BIGINT	Reserved for future use.
OBJECT_INDEX_CACHING_TIER_GBP_INDEP_PAGES_FOUND	BIGINT	Reserved for future use.
TENANT_ID	BIGINT	tenant_id - Tenant identifier monitor element