The causes to mark index invalid, and how to recover the indexes.

 

Question

I found some indexes marked invalid after rollforward, or a HADR standby database become switched to primary.

Why the indexes are marked as invalid, and how I can recover the invalid indexes.

Cause

Db2 does not write index build or rebuild log records by default, rather Db2 marks the index as invalid so that recovery task can trigger to rebuild the index.

Answer

"Index rebuilding" means index creation, index reorg, classic table reorg, and rebuilding index in this article.
 

Indexes can be marked as invalid when:

• Index rebuilding is not logged ( LOGINDEXBUILD=NO), then index rebuilding replayed.
• Index rebuilding is logged ( LOGINDEXBUILD=YES), then index rebuilding replayed but INDEXREC is set as ACCESS_NO_REDO or RESTART_NO_REDO.

• Failed offline reorg, or such failed offline reorg is replayed.
• db2dart runs with /MI option.

Indexes can be rebuilt when:

• There are one or more indexes marked invalid when a database restarts. i,e. Run RESTART DB with INDEXREC=RESTART
• Someone access an index marked as invalid.
• Run classic REORG TABLE command
• Run REORG INDEXES or REORG INDEX command
• Run CREATE INDEX statement
• Run LOAD command, and it uses REBUILD option as INDEXING MODE
• Run IMPORT or LOAD command with REPLACE option
• Run TRAUNCATE TABLE statement

We can list all the indexes marked as invalid by the following query:

db2 "select tabschema, tabname, indname, datapartitionid, index_partitioning from table(sysproc.admin_get_index_info('','','')) as t where index_requires_rebuild='Y'"

We can recover the indexes marked as invalid by ether of the following ways.
 

a) Run RESTART DATABASE command (in case db cfg INDEXREC is set as RESTART)
The restart database command rebuild all the indexes set as INDEX_REQUIRES_REBUILD=Y

db2 restart database <database_name>

b) Run reorg indexes

db2 reorg indexes all on table <table_name> allow no access

Note: 

You can set LOGINDEXBUILD as YES to prevent indexes to be marked as invalid.

So, this setting is highly recommended in HADR databases.

Example:

db2 connect to <database_name>
db2 update db cfg using LOGINDEXBUILD YES

 

https://www.ibm.com/support/pages/db2-causes-mark-index-invalid-and-how-recover-indexes

[Db2] The causes to mark index invalid, and how to recover the indexes.