How to find out all the indexes for a DB2 table TAB1?

To find out all the indexes built on the DB2 table TAB1 we can use the DB2 system table SYSIBM.SYSINDEXES. The SYSINDEXES database has one row for every index present in DB2. We can find indexes built on a particular table using the below SQL query.

SELECT NAME, UNIQUERULE, CLUSTERING
   FROM SYSIBM.SYSINDEXES WHERE TBNAME=’TAB1’

The column UNIQUERULE in the SELECT statement returns ‘P’ for primary index and ‘U’ for alternate index. The CLUSTERING column will be returned as ‘YES’ for clustered index and ‘NO’ for non-clustered index.

Mandalika

Published on 12-Sep-2020 14:41:44

• Related Questions & Answers
• How to find all the foreign keys of a DB2 table TAB1?
• How will you find out all the indexes which are built in a particular DB2 table?
• How to find the primary key of a DB2 table TAB1?
• How to delete a DB2 table TAB1?
• How to get the list of all COBOL-DB2 programs using a DB2 table TAB1?
• Write a DB2 query to find out all the duplicate INVOICE_ID in ORDERS DB2 table?
• How to create an ALIAS TAB2 for DB2 table TAB1?
• How to do a full & incremental MERGECOPY for a DB2 table TAB1?
• How to add a row compression to a DB2 table TAB1?
• How to image copy the entire DB2 table TAB1 into a dataset?
• How to add a new column Address in the above DB2 table TAB1?
• How will you add a constraint on above DB2 table TAB1 for ages between 3 to 16 years?
• How can you revert all the DB2 table changes done in a COBOL-DB2 program?
• How to create a table TAB2 having same attributes & columns as for table TAB1
• How to create a DB2 table TAB1 with 4 columns, Student ID, Enrollment ID, Name and Age?