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

How to find all indexe开发者_Python百科s available on table in db2?

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

You can also execute:

DESCRIBE INDEXES FOR TABLE SCHEMA.TABLE SHOW DETAIL

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

describe indexes for table schemaname.tablename show detail

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'

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.

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.

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.