You can use the SYSPROC.MON_GET_INDEX () table function to determine unused,
inefficient indexes in the database. The SQL statement is listed as follows:
SELECT VARCHAR (INDX.INDSCHEMA, 10) AS INDSCHEMA, VARCHAR (INDX.INDNAME, 30) AS INDNAME, MONGETINDEX.INDEX_SCANS, MONGETINDEX.INDEX_ONLY_SCANS FROM TABLE (MON_GET_INDEX ('','', -2)) AS MONGETINDEX, SYSCAT.INDEXES AS INDX WHERE MONGETINDEX.TABSCHEMA = INDX.TABSCHEMA AND MONGETINDEX.TABNAME = INDX.TABNAME AND MONGETINDEX.IID = INDX.IID AND MONGETINDEX.INDEX_SCANS = 0 AND MONGETINDEX.INDEX_ONLY_SCANS = 0 AND INDX.INDSCHEMA <> 'SYSIBM';