MON_GET_INDEX table function - get index metrics

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

Syntax

Read syntax diagramSkip visual syntax diagramMON_GET_INDEX(tabschema, tabname,member)

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