本文总结了DB2中索引失效的几种常见原因,如何找出所有失效的索引以及判断某个索引是否失效,如何重建失效的索引,以及如何监控索引创建/重建的进度
索引失效的原因
1.) 如果logindexbuild是off(默认选项),那么创建、重建索引的操作不会记录到日志中,之后如果有rollforward操作时,这些索引就会被标记为无效。(例如在t1时刻创建了表并做了backup,在t2时刻创建/重建了索引,那么之后rollforward到t2时,索引就会失效)2.) load使用了 INDEXING MODE DEFERRED选项
3.) db2dart显示地将索引标记为无效了。db2dart有一个选项/MI,只需要输入表ID、表空间ID,即可将该表上的索引标记为无效。
4.) 之前索引重建/reorg失败了。 Reorg操作如果因为某些原因失败,比如临时表空间不足,则由可能导致索引重建失败,变为失效状态
查看索引是否失效
下面这条SQL可以查看所有 表模式名 "E105Q5A"下所有表上所有需要重建的索引
db2 "SELECT substr(TABNAME,1,20) as TABNAME, substr(INDSCHEMA,1,20) as INDSCHEMA, substr(INDNAME,1,20) as INDNAME, INDEX_REQUIRES_REBUILD FROM TABLE(sysproc.admin_get_index_info('','E105Q5A','')) AS t where INDEX_REQUIRES_REBUILD= 'Y' "
TABNAME INDSCHEMA INDNAME INDEX_REQUIRES_REBUILD
-------------------- -------------------- -------------------- ----------------------
T1 E105Q5A IDX1 Y
T1 E105Q5A IDX2 Y
2 record(s) selected.
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0054905.html
如何重建失效的索引:
1.) 所有对表的访问都被导致失效索引被自动重建2.) 若indexrec设置为RESTART(默认选项),发出restart database命令也可会自动重建
监控索引重建:
首先诊断日志会在开始和结束阶段各打印一条消息2017-03-14-13.03.55.339517+480 E201703A552 LEVEL: Warning
PID : 37093388 TID : 3343 PROC : db2sysc 0
INSTANCE: e105q5a NODE : 000 DB : SAMPLE
APPHDL : 0-9 APPID: *LOCAL.e105q5a.170314035841
AUTHID : E105Q5A HOSTNAME: db2b
EDUID : 3343 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, data management, sqldLoadTCB, probe:10797
MESSAGE : ADM5572I One or more indexes on table "E105Q5A .T1" are marked
invalid and require rebuilding.
2017-03-14-13.03.55.354589+480 E202256A507 LEVEL: Warning
PID : 37093388 TID : 3343 PROC : db2sysc 0
INSTANCE: e105q5a NODE : 000 DB : SAMPLE
APPHDL : 0-9 APPID: *LOCAL.e105q5a.170314035841
AUTHID : E105Q5A HOSTNAME: db2b
EDUID : 3343 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, data management, sqldBeginIndexCreate, probe:1
MESSAGE : ADM5540W Rebuilding "2" indexes on table "E105Q5A .T1".
2017-03-14-13.03.55.356059+480 E202764A566 LEVEL: Warning
PID : 37093388 TID : 3343 PROC : db2sysc 0
INSTANCE: e105q5a NODE : 000 DB : SAMPLE
APPHDL : 0-9 APPID: *LOCAL.e105q5a.170314035841
AUTHID : E105Q5A HOSTNAME: db2b
EDUID : 3343 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, data management, sqldIndexCreate, probe:1
MESSAGE : ADM5541W Rebuilding index with IID "1" in object with ID "13" and
table space ID "3" on table "E105Q5A .T1".
2017-03-14-13.03.55.358700+480 E203331A566 LEVEL: Warning
PID : 37093388 TID : 3343 PROC : db2sysc 0
INSTANCE: e105q5a NODE : 000 DB : SAMPLE
APPHDL : 0-9 APPID: *LOCAL.e105q5a.170314035841
AUTHID : E105Q5A HOSTNAME: db2b
EDUID : 3343 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, data management, sqldIndexCreate, probe:1
MESSAGE : ADM5541W Rebuilding index with IID "2" in object with ID "13" and
table space ID "3" on table "E105Q5A .T1".
2017-03-14-13.03.55.367301+480 E203898A515 LEVEL: Warning
PID : 37093388 TID : 3343 PROC : db2sysc 0
INSTANCE: e105q5a NODE : 000 DB : SAMPLE
APPHDL : 0-9 APPID: *LOCAL.e105q5a.170314035841
AUTHID : E105Q5A HOSTNAME: db2b
EDUID : 3343 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, data management, sqldEndIndexCreate, probe:1
MESSAGE : ADM5542W Indexes on table "E105Q5A .T1" are successfully rebuilt.
实时监控重建进度的话,可以参考下面的链接:
http://www-01.ibm.com/support/docview.wss?uid=swg21984248