The query below lists all indexes in the Db2 database.

Query

select ind.indschema as schema_name,
       ind.indname as index_name,
       ind.iid as index_id,
       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,
       case ind.uniquerule 
           when 'P' then 'Primary key'
           when 'U' then 'Unique'
           when 'D' then 'Permits Duplicate'
       end as type,
       ind.tabname as table_name,
       listagg(cols.colname, ', ')
           within group (order by cols.colname) as columns
from syscat.indexes ind
join syscat.indexcoluse cols
     on ind.indname = cols.indname
     and ind.indschema = cols.indschema
where ind.tabschema not like 'SYS%'
group by ind.indname,
         ind.indschema,
         ind.iid,
         ind.uniquerule,
         ind.indextype,
         ind.tabname
order by schema_name,
         index_name,
         index_id;

Columns

• schema_name - name of schema
• index_name - index name
• index_id - id of index (unique in table)
• index_type :
  • Block index
  • Regular index
  • Page map index for a column-organized table
  • Key sequence index for a range-clustered table
  • Clustering index
  • Text index
  • Dimension block index
  • XML path index
  • XML region index
  • Index over XML column (logical)
  • Index over XML column (physical)
• type
  • PRIMARY KEY
  • UNIQUE
• table_name - table schema and name
• columns - list of index columns separated with ","

Rows

• One row represents one index
• Scope of rows: all indexes in the database (schema)
• Ordered by schema name, index name

Sample results