17

How to find all indexes available on table in db2?

sventechie
  • 1,859
  • 1
  • 22
  • 51
Rumik
  • 181
  • 1
  • 1
  • 3

7 Answers7

31
db2 "select * from syscat.indexes where tabname   = 'your table name here' \
                                  and   tabschema = 'your schema name here'"
Ingo
  • 36,037
  • 5
  • 53
  • 100
  • 3
    For my as400 db2 database, I need to query this : select * from qsys2.systables WHERE Table_schema = 'schema name' AND table_name = 'Table name' – YLombardi Apr 23 '15 at 15:22
  • 4
    @YLombardi or more exact using the qsys2.sysindexes table (for as400) – AtliB May 29 '15 at 11:56
17

You can also execute:

DESCRIBE INDEXES FOR TABLE SCHEMA.TABLE SHOW DETAIL
Ian Bjorhovde
  • 10,916
  • 1
  • 28
  • 25
  • 11
    Note that the 'describe' command will only work in the DB2 client, not through JDBC or other interfaces, since it is not standard SQL. – sventechie May 20 '13 at 20:25
1

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

describe indexes for table schemaname.tablename show detail
ProgramFOX
  • 6,131
  • 11
  • 45
  • 51
Dhanish Jose
  • 739
  • 1
  • 8
  • 19
1

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'
Abhinav Singh Maurya
  • 3,313
  • 8
  • 33
  • 51
Umang Pareek
  • 11
  • 1
1

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.

D. Kermott
  • 1,613
  • 17
  • 24
1

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.

adtanasa
  • 89
  • 4
  • The `SYSCAT` catalog views should be used on Db2 on Linux/Unix/Windows platforms, not `SYSIBM` – Paul Vernon Oct 02 '18 at 19:00
  • Db2 Warehouse on Cloud is Db2 for Linux. You should use the `SYSCAT` views not the underlying tables. https://stackoverflow.com/questions/3583963/db2-query-to-retrieve-all-table-names-for-a-given-schema/52322317#52322317 – Paul Vernon Oct 03 '18 at 14:20
  • @PaulVernon Thanks for pointing to using the catalog view instead of the underlay table. – adtanasa Oct 04 '18 at 09:17
0

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.

Vikas Jain
  • 3
  • 2