There is problem in the REORG INDEXES error handling code that
could result in a mismatch between the table and index if REORG
INDEXES fails in a small window due to limited resources at the
beginning of its processing. To hit this problem you need to
run REORG INDEXES with the ALLOW NO ACCESS option, without
the CLEANUP ONLY option and you need to have it fail in one of
two places:
- when allocating memory for the REORG INDEXES progress monitor
- when trying to get the extent movement lock for the tablespace
You will see db2diag.log messages similar to the following:
2012-02-08-11.23.43.398565-300 I32056E526 LEVEL:
Warning
PID : 9121 TID : 46912979855680PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-15 APPID: *LOCAL.db2inst1.120208162340
AUTHID : DB2INST1
EDUID : 25 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, relation data serv, sqlrreorg_indexes,
probe:300
DATA #1 : String, 96 bytes
Starting Offline Reorg Indexes on:
Table T1 (poolID 2 : objectID 4)
with reorg flags 0x04000010
2012-02-08-11.23.43.400329-300 E32583E532 LEVEL:
Warning
PID : 9121 TID : 46912979855680PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-15 APPID: *LOCAL.db2inst1.120208162340
AUTHID : DB2INST1
EDUID : 25 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, relation data serv, sqlrreorg_indexes,
probe:400
MESSAGE : ADM9501W BEGIN index reorganization on table
"DB2INST1.T1" (ID "4")
and table space "USERSPACE1" (ID "2").
2012-02-08-11.30.30.006153-300 I35204E500 LEVEL: Severe
PID : 9121 TID : 46912979855680PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-15 APPID: *LOCAL.db2inst1.120208162340
AUTHID : DB2INST1
EDUID : 25 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, relation data serv, sqlrreorg_index_obj,
probe:800
DATA #2 : Hexdump, 4 bytes
0x00002AAAC77F0560 : 0200 0F8B
....
2012-02-08-11.30.30.008525-300 E35705E690 LEVEL:
Warning
PID : 9121 TID : 46912979855680PROC :
db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-15 APPID: *LOCAL.db2inst1.120208162340
AUTHID : DB2INST1
EDUID : 25 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, relation data serv, sqlrreorg_indexes,
probe:1000
MESSAGE : ADM9504W Index reorganization on table "DB2INST1.T1"
(ID "4") and
table space "USERSPACE1" (ID "2") failed on this node
with SQLCODE
"-956" reason code "". To resolve this problem,
re-submit the REORG
INDEXES command on the failing node(s).
Please note that these probe points can be reported even if we
failed outside of the problematic timing window. If the problem
is hit, then at this point the index object descriptor will
indicate an error while page zero of the index object will not.
As a result, the index is not maintained on undo, and the index
can become out-of-sync with the table. This could result in a
number of different symptoms. The following are two examples:
1) Row not found in table during data fetch from index. A
message with the following text may appear in the db2diag.log:
FUNCTION: DB2 UDB, data management, sqldDataFetch, probe:4623
MESSAGE : Row not found on data fetch from index!
2) Row not found in the index during the update or delete of a
row in the table. A message with the following text may appear
in the db2diag.log:
FUNCTION: DB2 UDB, index manager, procT2Leaf2Del, probe:7
RETCODE : ZRC=0x8709002C=-2029453268=SQLI_NOKEY "Key not found
within node"
DIA8541C The index key could not be found, the value
was "". |
