trying to emulate broken data

william giannelli

trying to emulate broken data

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

Scenario:

I followed below steps to create an orphan pointer in INDEX.

  1. I have created a Table with Columns A,B,C,D,E. Created a primary index on Column A.
  2. Inserted 150 unique records into the table. At this point Table has 150 records and Index has 150 keys.
  3. Taken a Full Image copy of both index and table.
  4. 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.
  5. 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.
  6. Tried different SQLs to see if DB2 reports the data corruption or not.
  7. SELECT COUNT(*) from tableA; result=145
  8. 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.
  9. 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.

 

Thanks

Bill

Walter Janißen

AW: trying to emulate broken data
(in response to william giannelli)
Hi William

I think you first have to take a look at the access path: Was it a tablespace scan, then the index doesn’t matter. Was it an index-only-access, then the table doesn’t matter. Did you try:

SELECT * FROM TABLEA WHERE A = 146?

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: william giannelli [mailto:[login to unmask email]
Gesendet: Dienstag, 7. März 2017 15:44
An: [login to unmask email]
Betreff: [DB2-L] - trying to emulate broken data


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

Scenario:

I followed below steps to create an orphan pointer in INDEX.
1. I have created a Table with Columns A,B,C,D,E. Created a primary index on Column A.
2. Inserted 150 unique records into the table. At this point Table has 150 records and Index has 150 keys.
3. Taken a Full Image copy of both index and table.
4. 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.
5. 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.
6. Tried different SQLs to see if DB2 reports the data corruption or not.
7. SELECT COUNT(*) from tableA; result=145
8. 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.
9. 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.



Thanks

Bill

-----End Original Message-----
Attachments

  • image001.png (2.6k)

william giannelli

RE: AW: trying to emulate broken data
(in response to Walter Janißen)

Hi Walter,

Thanks for your response!

Makes sense, we will try that right away.

Thanks again

Bill

Sam Baugh

trying to emulate broken data
(in response to william giannelli)
perhaps "*Select * From TABLEA where A=146*" did a tablespace scan?

On Tue, Mar 7, 2017 at 8:44 AM, william giannelli <[login to unmask email]>
wrote:

> 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*
>
> *Scenario:*
>
> *I followed below steps to create an orphan pointer in INDEX.*
>
> 1. *I have created a Table with Columns A,B,C,D,E. Created a primary
> index on Column A.*
> 2. *Inserted 150 unique records into the table. At this point Table
> has 150 records and Index has 150 keys.*
> 3. *Taken a Full Image copy of both index and table. *
> 4. *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.*
> 5. *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.*
> 6. *Tried different SQLs to see if DB2 reports the data corruption or
> not.*
> 7. *SELECT COUNT(*) from tableA; result=145*
> 8. *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.*
> 9. *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.*
>
>
>
> *Thanks*
>
> *Bill*
>
> -----End Original Message-----
>

william giannelli

RE: trying to emulate broken data
(in response to Sam Baugh)

Hi Sam,

Thanks for the response!

We will test trying to force the use of the index.

Thanks!

Bill

Chris Tee

trying to emulate broken data
(in response to william giannelli)
Bill


Step 8 would be index only so returns 1 row but step 9 has to access the data because of the SELECT * and, probably due to the size of the table or the stats not being up to date, DB2 has decided that a scan is more efficient so returns no rows. If the table was larger, or perhaps making the table VOLATILE, to force index access, you should get the 00C90202.


regards


Chris


________________________________
From: william giannelli <[login to unmask email]>
Sent: 07 March 2017 14:44
To: [login to unmask email]
Subject: [DB2-L] - trying to emulate broken data


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

Scenario:

I followed below steps to create an orphan pointer in INDEX.

1. I have created a Table with Columns A,B,C,D,E. Created a primary index on Column A.
2. Inserted 150 unique records into the table. At this point Table has 150 records and Index has 150 keys.
3. Taken a Full Image copy of both index and table.
4. 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.
5. 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.
6. Tried different SQLs to see if DB2 reports the data corruption or not.
7. SELECT COUNT(*) from tableA; result=145
8. 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.
9. 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.



Thanks

Bill

-----End Original Message-----

william giannelli

RE: trying to emulate broken data
(in response to william giannelli)

We checked and the access path is Index scan not tablespace scan or index-only.

Bill

william giannelli

RE: trying to emulate broken data
(in response to Chris Tee)

Thank you Chris!

Bill

Phil Grainger

AW: trying to emulate broken data
(in response to Walter Janißen)
SQL often doesn't know about this type of error. To do so, it would have to look in table AND index

If you'd restored the TABLE instead of the index and used an access path that used the index only to find the data row is missing, THEN you would get your error

Sent from my iPhone

On 7 Mar 2017, at 15:02, Walter Jani&#223;en <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Hi William

I think you first have to take a look at the access path: Was it a tablespace scan, then the index doesn't matter. Was it an index-only-access, then the table doesn't matter. Did you try:

SELECT * FROM TABLEA WHERE A = 146?

Kind regards
Walter Jani?en [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 D?sseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Gesch?ftsf?hrung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: D?sseldorf, Handelsregister: Amtsgericht D?sseldorf HRB 37996

Von: william giannelli [mailto:[login to unmask email]
Gesendet: Dienstag, 7. M?rz 2017 15:44
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - trying to emulate broken data


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

Scenario:

I followed below steps to create an orphan pointer in INDEX.
1. I have created a Table with Columns A,B,C,D,E. Created a primary index on Column A.
2. Inserted 150 unique records into the table. At this point Table has 150 records and Index has 150 keys.
3. Taken a Full Image copy of both index and table.
4. 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.
5. 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.
6. Tried different SQLs to see if DB2 reports the data corruption or not.
7. SELECT COUNT(*) from tableA; result=145
8. 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.
9. 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.



Thanks

Bill

-----End Original Message-----

________________________________
Attachment Links: image001.png (3 k)<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D7987&d=CwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=IaeJRyRUujHnZnAitTNPsrr07-117edFn_l-WbHpRrw&s=5B0ENK3Oqnexg6cTnRefonpMdu2PRJozvGNvapjpiVI&e=>
Site Links: View post online<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_st_-3Fpost-3D180373-26anc-3Dp180373-23p180373&d=CwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=IaeJRyRUujHnZnAitTNPsrr07-117edFn_l-WbHpRrw&s=WE8ME5J9AW19O6hpUnkcoUyqxFU1_Bz_lIeWtH7Q6ik&e=> View mailing list online<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_si_-3Ftopic-3D19&d=CwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=IaeJRyRUujHnZnAitTNPsrr07-117edFn_l-WbHpRrw&s=irfu5R1Ni0DKOyoyewH6ffLNoLkuLYNOsoqO7_p_Wkw&e=> Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_us_to_&d=CwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=IaeJRyRUujHnZnAitTNPsrr07-117edFn_l-WbHpRrw&s=U1WMSsNgdZROCEyaV50dQxzoetDETe914yxSK_4uHIs&e=>

This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>

** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
---> Anaheim, California, April 30 - May 04, 2017 <---
http://www.idug.org/na <https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_na&d=CwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=IaeJRyRUujHnZnAitTNPsrr07-117edFn_l-WbHpRrw&s=6PgcH85RFkvGy_qjLVYVYBwM-IQaNLyYWNqvSbvhDV0&e=>

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_cm_ld_fid-3D2&d=CwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=IaeJRyRUujHnZnAitTNPsrr07-117edFn_l-WbHpRrw&s=c4_s0jlR2U7UP2EmQvcMxYz_z5zIPFRtdd4lWMAzzkk&e=>

________________________________
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.