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