How can I check if an index is invalid in DB2?

To check if an index is invalid in DB2, you can follow these steps:

Log into the DB2 database.

Execute the following command to view the index information of the table:

SHOW INDEXES FROM table_name;

Replace `table_name` with the name of the table you want to check.

Inspect each row in the result set. If the status of the index is “INVALID”, it means the index is no longer valid. If the status is “VALID”, it means the index is valid.

Additionally, you can also retrieve the status information of the index through querying the system directory view.

SELECT INDNAME, VALID FROM SYSCAT.INDEXES WHERE TABNAME = 'table_name';

Replace `table_name` with the name of the table you want to check.

In the result set, if the value in the `VALID` column is ‘Y’, it indicates the index is valid; if it is ‘N’, it indicates the index is invalid.

By using the methods above, you can check the index status of a specific table and determine which indexes are invalid and need to be rebuilt or repaired.