Query below lists table indexes.

Query

select  tabschema concat '.' concat tabname as table,
    iid as index_id,
    case uniquerule 
        when 'P' then 'Primary key'
        when 'U' then 'Unique'
        when 'D' then 'Nonunique'
    end as  type,
    indname as index_name,
    replace(substring(colnames,2,length(colnames)),'+',',') as columns, 
    case indextype 
        when 'BLOK' then 'Block index'
        when 'CLUS' then 'Clustering index'
        when 'DIM' then 'Dimension block index'
        when 'REG' then 'Regular 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 
where indschema not like 'SYS%'
order by table, index_id

Columns

• table - name of table index is defined for
• index_id - id of index (unique in table)
• type
  • Primary key
  • Unique
  • Nonunique
• index_name - index name
• columns - list of index columns separated with "+"
• index_type - index type:
  • Block index
  • Clustering index
  • Dimension block index
  • Regular index
  • XML path index
  • XML region index
  • Index over XML column (logical)
  • Index over XML column (physical)

Rows

• One row represents represents index
• Scope of rows: all indexes (unique and nonunique) in databases
• Ordered by schema, table name, index id

Sample results