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';