SELECT IND.INDNAME AS INDEX_NAME
,      IND.TABSCHEMA CONCAT '.' CONCAT IND.TABNAME AS TABLE
,      IND.IID AS INDEX_ID
,      CASE IND.UNIQUERULE 
            WHEN 'P' THEN 'PRIMARY KEY'
            WHEN 'U' THEN 'UNIQUE'
       END AS TYPE
,      LISTAGG(COLS.COLNAME, ', ')
            WITHIN GROUP (ORDER BY COLS.COLNAME) AS COLUMNS
,      CASE IND.INDEXTYPE
            when 'BLOK' then 'Block index'
            when 'REG' then 'Regular index'
            when 'CPMA' then 'Page map index for a column-organized table'
            when 'RCT ' then 'Key sequence index for a range-clustered table'
            when 'CLUS' then 'Clustering index'
            when 'TEXT' then 'Text index'
            when 'DIM' then 'Dimension block index'
            when 'XPTH' then 'XML path index'
            when 'XRGN' then 'XML region index'
            when 'XVIL' then 'Index over XML column (logical)'
            when 'XVIP' then 'Index over XML column (physical)'
       END AS INDEX_TYPE
  FROM SYSCAT.INDEXES IND
       JOIN SYSCAT.INDEXCOLUSE COLS
         ON IND.INDNAME = COLS.INDNAME
        AND IND.INDSCHEMA = COLS.INDSCHEMA
 WHERE IND.TABSCHEMA NOT LIKE 'SYS%'
   AND IND.UNIQUERULE != 'D'
 GROUP BY IND.INDNAME
 ,        IND.TABSCHEMA
 ,        IND.IID
 ,        IND.UNIQUERULE
 ,        IND.INDEXTYPE
 ,        IND.TABNAME
 ORDER BY TABLE
 ,        INDEX_ID;