We are setting up an alert for a broken data message reason code ‘00C90202’.
But when we do the following we do not get the error message:
Summary of the problem: DB2 not reporting/detecting broken data condition
I followed below steps to create an orphan pointer in INDEX.
- I have created a Table with Columns A,B,C,D,E. Created a primary index on Column A.
- Inserted 150 unique records into the table. At this point Table has 150 records and Index has 150 keys.
- Taken a Full Image copy of both index and table.
- Deleted 5 rows from the table where Key A=146,147,148,149,150. At this point table has 145 rows and Index has 145 keys.
- Stopped the index space and recovered the index space to full image copy taken in point number 3. Started the indexspace after repairing the levelId in RW mode. Now the table has 145 rows, Index has 150 Keys.
- Tried different SQLs to see if DB2 reports the data corruption or not.
- SELECT COUNT(*) from tableA; result=145
- SELECT COUNT(*) from TABLEA WHERE A=146; RESULT=1-> Whereas data doesn’t exist in table for key 146 but an entry is there in Index.
- Select * From TABLEA where A=146; result-> no rows found. Our Expectation is it should have thrown ‘00C90202’ reason code for inconsistent data, but the SQL executed successfully with SQLCODE=0 and the result is”NO ROWS FOUND”.
Please help us understand why DB2 did not report when there is a data corruption/broken pointer condition.