Get columns of index on DB2

Question

How can I get the columns, which an index of a table uses, in DB2?

I tried:

DESCRIBE INDEXES FOR TABLE 'MYTABLE' SHOW DETAIL;

But I get the error message

ILLEGAL SYMBOL "INDEXES". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: PROCEDURE PROC. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.16.53

Ideally I want information of all indexes a table uses with their corresponding columns.

I am using DB2 for z/OS V9.1

Solution 1

You can use this query to show the indexes and their columns of your tables:

SELECT IX.tbname, 
       KEY.ixname, 
       KEY.colname 
FROM   sysibm.syskeys KEY 
       JOIN sysibm.sysindexes IX 
         ON KEY.ixname = IX.name 
WHERE  IX.tbname IN ( 'SOMETABLE', 'ANOTHERTABLE' ) 
ORDER  BY IX.tbname, 
          KEY.ixname, 
          KEY.colname; 

Solution 2

SELECT * FROM SYSIBM.SYSKEYS WHERE IXNAME IN 
(SELECT NAME FROM SYSIBM.SYSINDEXES WHERE TBNAME = 'your_table_name') 

I have tested it, it is giving us all the columns which are used in indexes.

Solution 3

You can use below query also. it works fine if syskeys table is missing

SELECT * FROM SYSIBM.SYSINDEXCOLUSE where INDNAME IN (SELECT NAME FROM SYSIBM.SYSINDEXES si where si.TBNAME ='your_table_Name' ) ORDER BY INDNAME, COLSEQ