Uooo Uooo - 3 months ago 16
SQL Question

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

Answer

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