sqlindexingdb2

Get columns of index on DB2

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

  • 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;