When index contains JSON_VAL function in its definition like
below:
CREATE INDEX MYSCHEMA.TIMESTAMP_IDX ON MYSCHEMA.TABLE
(JSON_VAL(DATA, 'timestamp', 'ts:na') ASC)
the index may end up being be inconsistent with table data.
The problem is not detected during index creation.
Issue can be seen during DML operations manipulating data like
DELETE.
Once problem is detected, following is logged to db2diag.log:
2017-09-20-17.00.36.308064-360 E53428337E794 LEVEL: Error
PID : 5541 TID : 140491517585152 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYSCHEMA
APPHDL : 0-20852 APPID: *LOCAL.db2inst1.170920230037
AUTHID : DB2INST1 HOSTNAME: node01
EDUID : 43828 EDUNAME: db2agent (MYSCHEMA) 0
FUNCTION: DB2 UDB, data management, sqldKeyDelete, probe:1582
MESSAGE : ADM5595E An index data inconsistency is detected on
table
"MYSCHEMA". "TABLE". Please run "INSPECT CHECK TABLE NAME
"TABLE"
SCHEMA "MYSCHEMA" INDEXDATA RESULTS KEEP "TABLE"_resfile.out" on
the
node that failed, and then contact DB2 support team to report
the
problem.
Running INSPECT on problematic table reveals
sqldDumpKeyDataMismatch:
2017-09-22-09.33.16.990344-360 I5333E914 LEVEL:
Error
PID : 5541 TID : 140494130636544 PROC :
db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB :
MYSCHEMA
APPHDL : 0-36142 APPID:
*LOCAL.db2inst1.170922150329
AUTHID : DB2INST1 HOSTNAME: node01
EDUID : 49260 EDUNAME: db2agent (MYSCHEMA) 0
FUNCTION: DB2 UDB, data management, sqldDumpKeyDataMismatch,
probe:581
DATA #1 : String, 25 bytes
Table(2:6)=MYSCHEMA.TABLE
DATA #2 : String, 44 bytes
Rid found in dmsWA - recid:x0000BF0400000000
DATA #3 : String, 9 bytes
slot no.:
DATA #4 : unsigned integer, 4 bytes
0
DATA #5 : String, 11 bytes
slot count:
DATA #6 : unsigned integer, 2 bytes
22
DATA #7 : String, 30 bytes
Rid found in dmsWA - pageSlot:
DATA #8 : Hexdump, 8 bytes
0x00007FC731352110 : BF04 0000 0000 0000
........
Note that indexes recreation does not solve the problem.
Reason is that failure in in JSON_VAL function code which
produces incorrect results during its processing.
As already mentioned, error is not detected during index
creation, therefore index recreation creates incorrect data as
well. |
