How will you find out all the indexes which are built in a particular DB2 table?

The DB2 indexes are used to increase the query performance and speed up the query result. There can be multiple indexes built on a single table and DB2 optimizer chooses different indexes based on the predicate used in the WHERE clause to fetch the query result.

In order to find out all the indexes which are built on a particular table we will use the DB2 system table SYSIBM.SYSINDEXES. This table records all the details related to indexes. Following SQL query can be used on this table to get the desired result.

Example

SELECT NAME, TBNAME FROM SYSIBM.SYSINDEXES
   WHERE TBNAME = ‘’

The NAME column indicates the name of the index and TBNAME column indicates the name of the table.