DB2 v8 z/os - Possible data corruption?

Scott Hodgin

DB2 v8 z/os - Possible data corruption?
Hi list,



I have not opened an issue with IBM on this yet, but I was wondering if
anyone could shed some light on a problem we encountered after upgrading our
prod systems to v8 this passed weekend.



As far as I know, everything has been fine on a given table containing about
711,000 rows as of Saturday morning full backup.



On Monday night, we had a load resume yes run and issued RC4 saying resume
yes was specified for an empty tablespace (no one noticed this rc4).

Tuesday night, a delete blew up with some 'inconsistancy' errors.

I recovered the tablespace to Saturday morning's fullcopy and rebuilt the
indexes

Select count indicated a reasonable row total

I tried reorging the table - 0 rows unloaded and 0 rows loaded.



I again recovered the table to Saturday mornings backup.

Ran dsntiaul which unloaded the correct number of rows

Ran load replace on table with the dsntiaul input

The correct number of rows were loaded

Ran reorg - 0 rows unloaded and 0 rows loaded





i completely dropped the entire tablespace and recreated it along with the 1
table and indexes and authorities.

Ran load replace with the previous dsntiaul unloaded rows from above

Ran reorg and everything was fine.









This seems to be the only table in this situation (so far)





Can anyone explain what could have happened?



Scott Hodgin, Database Administrator

South Carolina Farm Bureau Insurance Company

[login to unmask email] <mailto:[login to unmask email]>




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Avram Friedman

Re: DB2 v8 z/os - Possible data corruption?
(in response to Scott Hodgin)
Just some thoughts


If you suspect data coruption I would suggest being agressive in terms of problem reporting.
For a problem collecting diagnostic information is always advised. In this case for example
Backing up the catalog at various states of the problem
Dumping the physical objects associated with the problem table spaces and indexes
SYSCOPY information on both the problem table spaces and indexes

Do you use third party utilities?
Do you do online REORG with or without fast switch?
Were there non terminated utilities at the time of the V8 upgrade.
Do you have any products that use a shadow copy copy of the catalog? Was the shadow catalog converted?

Guess mode:
Third party online reorg started but failed to complete properly prior to the DB2 V8 upgrade. The third party data store about DB2 objects and the DB2 catalog do not match. For example the 'REORG' utility is processing the "I" copy but DB2 is using the "J" copy.
End Guess mode:

Best wishes.

"Hodgin, Scott" <[login to unmask email]> wrote:
Hi list,

I have not opened an issue with IBM on this yet, but I was wondering if anyone could shed some light on a problem we encountered after upgrading our prod systems to v8 this passed weekend.

As far as I know, everything has been fine on a given table containing about 711,000 rows as of Saturday morning full backup.

On Monday night, we had a load resume yes run and issued RC4 saying resume yes was specified for an empty tablespace (no one noticed this rc4).
Tuesday night, a delete blew up with some 'inconsistancy' errors.
I recovered the tablespace to Saturday morning's fullcopy and rebuilt the indexes
Select count indicated a reasonable row total
I tried reorging the table - 0 rows unloaded and 0 rows loaded.

I again recovered the table to Saturday mornings backup.
Ran dsntiaul which unloaded the correct number of rows
Ran load replace on table with the dsntiaul input
The correct number of rows were loaded
Ran reorg - 0 rows unloaded and 0 rows loaded


i completely dropped the entire tablespace and recreated it along with the 1 table and indexes and authorities.
Ran load replace with the previous dsntiaul unloaded rows from above
Ran reorg and everything was fine.





This seems to be the only table in this situation (so far)


Can anyone explain what could have happened?

Scott Hodgin, Database Administrator
South Carolina Farm Bureau Insurance Company
[login to unmask email]

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



Avram Friedman
(877)311-0480 Voice Mail
[login to unmask email]
Http://www.IBMsysProg.com




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Bell

Re: DB2 v8 z/os - Possible data corruption?
(in response to Avram Friedman)
1. open a PMR
2. DSN1PRNT for the image copy that you used for the recovery (5-10 pages to
get the header page and some data pages).
3. DSN1PRNT for the image copy after the complete rebuild.
4. somehow run REPAIR DBD DIAGNOSE DATABASE dbname against what was in the
catalog on Sat.

Guess mode
one of three (or more) scenarios could cause this
1. DBD didn't have a matching obid for the table in the DBD in
DSNDB01.DBD01. This would cause DB2 to treat the rows as if the table had
been dropped so it needed to remove the data. If you are brave, you can try
REPAIR DBD REBUILD DATABASE dbname and see if it fixes it.
2. less likely - online reorg had failed and it was looking for the J001
dataset not the I001. since the dataset didn't exist, no data. I don't
remember how you tell which dataset name is active.
3. even less likely - some kind of embedded end of file or IO error that was
detected as end-of-file in the old dataset. I think the first RECOVER
should have deleted/redefined the physical dataset unless you specified
reuse or used DSN1COPY for the recovery.

The biggest issue to me is that LOAD and REORG seemed to be using different
definitions for the table or datasets which implies a code failure in DB2.

Mike Bell
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Hodgin, Scott
Sent: Wednesday, December 14, 2005 5:49 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 v8 z/os - Possible data corruption?

Hi list,



I have not opened an issue with IBM on this yet, but I was wondering if
anyone could shed some light on a problem we encountered after upgrading our
prod systems to v8 this passed weekend.



As far as I know, everything has been fine on a given table containing about
711,000 rows as of Saturday morning full backup.



On Monday night, we had a load resume yes run and issued RC4 saying resume
yes was specified for an empty tablespace (no one noticed this rc4).

Tuesday night, a delete blew up with some 'inconsistancy' errors.

I recovered the tablespace to Saturday morning's fullcopy and rebuilt the
indexes

Select count indicated a reasonable row total

I tried reorging the table - 0 rows unloaded and 0 rows loaded.



I again recovered the table to Saturday mornings backup.

Ran dsntiaul which unloaded the correct number of rows

Ran load replace on table with the dsntiaul input

The correct number of rows were loaded

Ran reorg - 0 rows unloaded and 0 rows loaded





i completely dropped the entire tablespace and recreated it along with the 1
table and indexes and authorities.

Ran load replace with the previous dsntiaul unloaded rows from above

Ran reorg and everything was fine.









This seems to be the only table in this situation (so far)





Can anyone explain what could have happened?



Scott Hodgin, Database Administrator

South Carolina Farm Bureau Insurance Company

[login to unmask email]



----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm