How to find out all the indexes for a DB2 table TAB1?

To find out all the indexes built on the DB2 table TAB1 we can use the DB2 system table SYSIBM.SYSINDEXES. The SYSINDEXES database has one row for every index present in DB2. We can find indexes built on a particular table using the below SQL query.

SELECT NAME, UNIQUERULE, CLUSTERING
   FROM SYSIBM.SYSINDEXES WHERE TBNAME=’TAB1’

The column UNIQUERULE in the SELECT statement returns ‘P’ for primary index and ‘U’ for alternate index. The CLUSTERING column will be returned as ‘YES’ for clustered index and ‘NO’ for non-clustered index.

Mandalika

Published on 12-Sep-2020 14:41:44