CODEWITHSUNDEEP
sqlindexingdb2
Uooo
Uooo

Reputation: 6334

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

Upvotes: 2

Views: 21961

Answers (5)

Achim Schmitz
Achim Schmitz

Reputation: 538

The answer from Uooo is not strictly correct. You need to add the CREATOR to the JOIN. Here's the version that I used to get the same sort of info for a project of ours.

SELECT IX.TBNAME, 
       IX.TBCREATOR,
       IX.NAME, 
       IX.CREATOR,
       KEY.COLNAME, 
       KEY.COLSEQ
FROM   SYSIBM.SYSKEYS KEY 
       JOIN SYSIBM.SYSINDEXES IX 
         ON (KEY.IXNAME = IX.NAME AND KEY.IXCREATOR = IX.CREATOR)
WHERE IX.TBNAME IN ('EXPLAIN_TABLE',  
                    'STATISTIC_TABLE',
                    'STATEMENT_TABLE',
                    'DSN_FILTER_TABLE',
                    'PLAN_TABLE', 'DSN_PREDICAT_TABLE') 
  AND IX.CREATOR = 'myschema'
ORDER  BY IX.TBNAME, 
          IX.NAME, 
          KEY.COLSEQ;

Note also that the CREATOR for the table and for the index need not necessarily be the same. That's why I output those as well as the names.

Upvotes: 0

Pushpraj
Pushpraj

Reputation: 11

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

Upvotes: 1

DGeorge
DGeorge

Reputation: 1

I had an issue with using "KEY" as a table alias. Also, if you have multiple schemas with the same table name, use the following:

SELECT IX.TABLE_SCHEMA, IX.TABLE_NAME, IX.INDEX_NAME, KY.ORDINAL_POSITION, KY.COLUMN_NAME
  FROM SYSKEYS KY 
  JOIN SYSINDEXES IX ON (KY.INDEX_NAME = IX.INDEX_NAME AND KY.INDEX_SCHEMA = IX.INDEX_SCHEMA)
 WHERE IX.TBNAME = 'table-name' AND IX.TABLE_SCHEMA = 'table-schema'
 ORDER BY IX.TABLE_SCHEMA, IX.TABLE_NAME, IX.INDEX_NAME, KY.ORDINAL_POSITION
   FOR READ ONLY WITH UR

Upvotes: -1

Saravana Kumar
Saravana Kumar

Reputation: 11

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.

Upvotes: 1

Uooo
Uooo

Reputation: 6334

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;

Upvotes: 7

Related Questions