DB2 includes a helpful sql udf whose code is stated below -
 

CREATE FUNCTION SYSPROC.GET_INDEX_COLNAMES ( INDEX_SCHEMA VARCHAR(128), INDEX_NAME VARCHAR(128) ) RETURNS VARCHAR(1984) SPECIFIC GET_INDEX_COLNAMES LANGUAGE SQL READS SQL DATA BEGIN ATOMIC DECLARE INDEX_COLNAMES VARCHAR(1984) DEFAULT '';

DECLARE COL_COUNTER INT DEFAULT 1;

DECLARE NUM_OF_COLS SMALLINT;

SET NUM_OF_COLS = (SELECT COLCOUNT FROM SYSCAT.INDEXES WHERE INDSCHEMA = INDEX_SCHEMA AND INDNAME = INDEX_NAME);

WHILE COL_COUNTER <= NUM_OF_COLS DO SET INDEX_COLNAMES = INDEX_COLNAMES || (SELECT CASE COLORDER WHEN 'D' THEN '-' ELSE '+' END || COLNAME FROM SYSCAT.INDEXCOLUSE WHERE INDSCHEMA = INDEX_SCHEMA AND INDNAME = INDEX_NAME AND COLSEQ = COL_COUNTER);

SET COL_COUNTER = COL_COUNTER + 1;

END WHILE;

RETURN( INDEX_COLNAMES );


Using the above-mentioned udf, we can loop thru all catalog objects. The query below provides a helpful report on identifying any redundant indexes [like where some indexes are part of other index definitions] -

SELECT I.TABSCHEMA, I.TABNAME, SYSPROC.GET_INDEX_COLNAMES(I.INDSCHEMA, I.INDNAME) AS CONC_KEY_LIST
    FROM
         SYSCAT.TABLES AS T INNER JOIN SYSCAT.INDEXES AS I 
    ON T.TABSCHEMA = I.TABSCHEMA AND T.TABNAME = I.TABNAME
    ORDER BY I.TABSCHEMA, I.TABNAME, CONC_KEY_LIST
    WITH UR;