When the MDC table is populated with LOAD utility, the LOAD puts
upper-case key values in the same index block of as lower-case
key values.This happens in case insensitive database.
Following scenario leads to the problem:
db2 "create db wsdb collate using
UCA500R1_LEN_AN_CX_EX_FX_HX_NX_S2"
db2 "connect to wsdb"
db2 "create tablespace ts1 extentsize 2"
db2 "create table t1 (i1 int, c2 char(1), i3 int, c4 char(250))
organize by (i1,c2) in ts1"
echo "1,Y,1,SpaceWaster" >> dataFile
echo "1,y,2,SpaceWaster" >> dataFile
db2 "load from dataFile of del messages /dev/null insert into
t1"
db2 "create table t2 (i1 int) in ts1"
db2 "insert into t2 values (1)"
Next, during DELETE(UPDATE) operation on the MDC table following
error might be returned:
db2 "delete from t1 where c4 = 'SpaceWaster' and i1 in (select
i1 from t2)"
DB21034E The command was processed as an SQL statement because
it was not a valid Command Line Processor command.
During SQL processing it returned:
SQL0901N The SQL statement failed because of a non-severe
system error.
Subsequent SQL statements can be processed. (Reason "Key data
mismatch encountered during key delete".) SQLSTATE=58004
The error occurs when the block index you are trying to
DELETE(UPDATE) from has the key stored with a lower-case instead
of an upper-case, and we are trying to update the block index
using an upper-case key value instead of a lower-case key value.
Once you hit the issue, following entries are logged in
db2diag.log
2014-02-05-06.22.11.206277-300 I26837E538 LEVEL: Severe
PID : 624 TID : 46912946301248PROC :
db2sysc
INSTANCE: bastarma NODE : 000 DB : WSDB
APPHDL : 0-89 APPID:
*LOCAL.bastarma.140205110041
AUTHID : BASTARMA
EDUID : 16 EDUNAME: db2agent (WSDB)
FUNCTION: DB2 UDB, index manager, sqlischd, probe:973
RETCODE : ZRC=0x8709002C=-2029453268=SQLI_NOKEY "Key not found
within node"
DIA8541C The index key could not be found, the value
was "".
2014-02-05-06.22.11.572715-300 I35084E537 LEVEL: Severe
PID : 624 TID : 46912946301248PROC :
db2sysc
INSTANCE: bastarma NODE : 000 DB : WSDB
APPHDL : 0-89 APPID:
*LOCAL.bastarma.140205110041
AUTHID : BASTARMA
EDUID : 16 EDUNAME: db2agent (WSDB)
FUNCTION: DB2 UDB, index manager, sqlischd, probe:99
RETCODE : ZRC=0x8709002C=-2029453268=SQLI_NOKEY "Key not found
within node"
DIA8541C The index key could not be found, the value
was "".
2014-02-05-06.22.11.572830-300 I35622E502 LEVEL: Severe
PID : 624 TID : 46912946301248PROC :
db2sysc
INSTANCE: bastarma NODE : 000 DB : WSDB
APPHDL : 0-89 APPID:
*LOCAL.bastarma.140205110041
AUTHID : BASTARMA
EDUID : 16 EDUNAME: db2agent (WSDB)
FUNCTION: DB2 UDB, index manager, sqlischd, probe:99
MESSAGE : Index object = {TBSPACEID=<3>; OBJECTID=<4>} Parent
object =
{TBSPACEID=<3>; OBJECTID=<4>}
Further FODC_IndexError is executed, and the trap file contain
stack with following functions (limited to meaningful part of
the stack):
...
pthread_kill
sqloDumpEDU
sqlischdDumpData
sqlischd
sqlidelk
sqldKeyDelete
sqldRowDelete
sqlridel
sqlriExecThread
sqlrievl
sqlriSectInvoke
... |
To avoid hitting the problem, the workaround is to normalize any
character data in the dimension columns for any MDC tables, or
to use import instead of load.
If you have already hit the problem, then then you need to
recreate your MDC table to fix the problem. |
