CODEWITHSUNDEEP
indexingdb2
Rumik
Rumik

Reputation: 181

How can I find all indexes available on a table in DB2

How to find all indexes available on table in db2?

Upvotes: 17

Views: 114057

Answers (7)

adtanasa
adtanasa

Reputation: 89

For checking the indexes of a table on IBM Db2 on Cloud (previously DashDb) the following query should do it:

SELECT * FROM SYSCAT.INDEXES WHERE TABNAME = 'my_tablename' AND TABSCHEMA = 'my_table_schema'

You can use also check by index name:

SELECT COUNT(*) FROM SYSCAT.INDEXES WHERE TABNAME = 'my_tablename' AND TABSCHEMA = 'my_table_schema' AND INDNAME='index_name'

The same result can be achieved by using SYSIBM.SYSINDEXES. However, this table is not referenced directly on the product documentation page.

SELECT COUNT(*) FROM SYSIBM.SYSINDEXES WHERE TBNAME = 'my_tablename' AND TBCREATOR = 'my_table_schema' AND NAME='my_index_name'

See SYSCAT.INDEXES catalog view.

Upvotes: 1

D. Kermott
D. Kermott

Reputation: 1673

This depends upon which version of DB2 you are using. We have v7r1m0 and the following query works quite well.

WITH IndexCTE (Schema, Table, Unique, Name, Type, Columns) AS
   (SELECT i.table_schema, i.Table_Name, i.Is_Unique, 
           s.Index_Name, s.Index_Type, s.column_names
    FROM qsys2.SysIndexes i
    INNER JOIN qsys2.SysTableIndexStat s
    ON i.table_schema = s.table_schema
    and i.table_name = s.table_name
    and i.index_name = s.index_name)
SELECT * 
FROM IndexCTE 
WHERE schema = 'LIBDEK' 
AND   table = 'ECOMROUT'

If you're not familiar with CTE's they are worth getting to know. Our AS400 naming conventions are awful so I've been using CTE's to normalize field names. I ended up making a library of CTE's and have it automatically append to the top of all my queries.

Upvotes: 1

Umang Pareek
Umang Pareek

Reputation: 11

To see all indexes :- 

select * from user_objects
where object_type='INDEX'

To see index and its columns on table :

select * from USER_IND_COLUMNS where TABLE_NAME='my_table'

Upvotes: 1

Dhanish Jose
Dhanish Jose

Reputation: 747

You can get the details of indexes with the below command.

describe indexes for table schemaname.tablename show detail

Upvotes: 1

Ingo
Ingo

Reputation: 36339

db2 "select * from syscat.indexes where tabname   = 'your table name here' \
                                  and   tabschema = 'your schema name here'"

Upvotes: 33

Vikas Jain
Vikas Jain

Reputation: 3

One more way is to generate the DDL of the table. It will give you the complete description of table including index on it.

Just right click on table and click on generate DDL/Scripts.

Works on most of the database.

Upvotes: 0

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11042

You can also execute:

DESCRIBE INDEXES FOR TABLE SCHEMA.TABLE SHOW DETAIL

Upvotes: 19

Related Questions