Get columns of index on DB2

db2, indexing, sql

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;

Problem

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

Original source