The MON_GET_INDEX table function returns metrics for one or more indexes.
EXECUTE privilege on the MON_GET_INDEX function.
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 example is a sample 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.
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 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.
Metrics are always enabled. It is not necessary to turn on any system monitor switches to access table metrics through this function.