The below query can get you the index for a specific table:
select indschema, indname, definer, tabschema, tabname, colnames from syscat.indexes where tabname = 'TABLE' order by indname asc
Even the order of which the indexes is declared can make a difference to a query:
if COL2 will be used in alot of queries it is better to place COL2 column 1st in the index.
Other queries of use:
- Query to get the PRIMARY/FOREIGN keys:
SELECT * FROM SYSCAT.KEYCOLUSE A, SYSCAT.TABCONST B WHERE A.CONSTNAME=B.CONSTNAME AND A.TABNAME='TABLE' AND A.TABSCHEMA='SCHEMA' order by A.COLSEQ;