SELECT TAB.TABSCHEMA || '.' || TAB.TABNAME AS TABLE_NAME
,      TAB.CONSTNAME AS CONSTRAINT_NAME
,      CASE WHEN TAB.TYPE = 'P' THEN 'PRIMARY KEY'
            ELSE 'UNIQUE'
       END AS CONSTRAINT_TYPE
,      LISTAGG(KEY.COLNAME, ', ')
              WITHIN GROUP(ORDER BY KEY.COLSEQ) AS COLUMNS
,            IND.INDNAME AS INDEX_NAME,
       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.TABCONST TAB
       JOIN SYSCAT.KEYCOLUSE KEY
         ON TAB.TABSCHEMA = KEY.TABSCHEMA
        AND TAB.TABNAME = KEY.TABNAME
        AND TAB.CONSTNAME = KEY.CONSTNAME
       JOIN SYSCAT.CONSTDEP DEP 
         ON TAB.CONSTNAME = DEP.CONSTNAME
        AND TAB.TABSCHEMA = DEP.TABSCHEMA
        AND TAB.TABNAME = DEP.TABNAME
       JOIN SYSCAT.INDEXES IND
         ON DEP.BSCHEMA = IND.INDSCHEMA
        AND DEP.BNAME = IND.INDNAME 
 WHERE TAB.TABSCHEMA NOT LIKE ('SYS%')
   AND TAB.TYPE IN ('P','U')
 GROUP BY TAB.TABSCHEMA
 ,        TAB.CONSTNAME
 ,        TAB.TABNAME
 ,        TAB.TYPE
 ,        IND.INDNAME
 ,        IND.INDEXTYPE
 ORDER BY TABLE_NAME;