--------------------------------------------------------------------------------
-- SQL-Table-Index-Metrics.sql - Table/Index metrics since DB2 start/connection
-- DB2 9.7 FP 5 and later
--------------------------------------------------------------------------------
-- Number of INSERT/UPDATE/DELETE/SELECT and Tablescans,
-- overflows and page reorgs since first DB Connection
SELECT *
FROM   TABLE(MON_GET_TABLE('','',-2)) AS T 
;
-- Number of INDEX_SCANS, INDEX_ONLY_SCANS, KEY_UPDATES.
-- PSEUDO_DELETES, Splits, PSEUDO_EMPTY_PAGES, EMPTY_PAGES_REUSED
SELECT T.*, S.INDNAME 
       --S.COLNAMES, S.FIRSTKEYCARD, S.FULLKEYCARD
FROM   TABLE(MON_GET_INDEX('','', -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
;