Indexing

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:
INDEX (COL1,COL2)

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;