Recover TORBA produced referential violations

Bob Jeandron

Recover TORBA produced referential violations
A batch job deleted approx. 20000 rows by mistake. We
recovered the TBSPs to a Point in time directly before
the batch job ran, not a quiesce point. This produced
about 100 foreign key violations, no parent key. The
violations could have existed before all of this
happened, but I don't believe in coincidence. Did
this occur because a parent key was updated and the
child table update did not complete? Which makes
sense, but I foolishly believed that this would be one
Unit of Recovery which would be rolled back in the
recovery process.

How can recover TORBA cause referential violations?

Thanks in advance.




__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail

---------------------------------------------------------------------------------
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

Scott Hodgin

Re: Recover TORBA produced referential violations
(in response to Bob Jeandron)
Assuming you only recovered the child table TORBA, this could occur because
before you restored you had already deleted parent and child rows, then you
restored the child table back before the parent/child deletes so now there
are no parent rows.

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

-----Original Message-----
From: Bob Jeandron [mailto:[login to unmask email]
Sent: Thursday, January 13, 2005 9:15 AM
To: [login to unmask email]
Subject: [DB2-L] Recover TORBA produced referential violations

A batch job deleted approx. 20000 rows by mistake. We
recovered the TBSPs to a Point in time directly before
the batch job ran, not a quiesce point. This produced
about 100 foreign key violations, no parent key. The
violations could have existed before all of this
happened, but I don't believe in coincidence. Did
this occur because a parent key was updated and the
child table update did not complete? Which makes
sense, but I foolishly believed that this would be one
Unit of Recovery which would be rolled back in the
recovery process.

How can recover TORBA cause referential violations?

Thanks in advance.




__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail

----------------------------------------------------------------------------
-----
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

---------------------------------------------------------------------------------
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

Phil Grainger

Re: Recover TORBA produced referential violations
(in response to Scott Hodgin)
How do you know that the RBA you chose is not IN THE MIDDLE of a UNIT OF
WORK affecting this table?


Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Bob Jeandron
Sent: 13 January 2005 14:15
To: [login to unmask email]
Subject: [DB2-L] Recover TORBA produced referential violations

A batch job deleted approx. 20000 rows by mistake. We recovered the
TBSPs to a Point in time directly before the batch job ran, not a
quiesce point. This produced about 100 foreign key violations, no
parent key. The violations could have existed before all of this
happened, but I don't believe in coincidence. Did this occur because a
parent key was updated and the child table update did not complete?
Which makes sense, but I foolishly believed that this would be one Unit
of Recovery which would be rolled back in the recovery process.

How can recover TORBA cause referential violations?

Thanks in advance.




__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail

------------------------------------------------------------------------
---------
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

---------------------------------------------------------------------------------
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

Andy Lankester

Re: Recover TORBA produced referential violations
(in response to Phil Grainger)
Recover TORBA does exactly what it says. It takes no account of in-flight
UoWs. That is why the only valid toRBAs are a QUIESCE point (common to all
spaces in the RI 'set') or an RBA (range) for which there were no active
UoWs with changes to any of the tablespaces in the RI set.

Third party products DO offer recover options which will ignore in-flights
in a recover to an arbitrary RBA but restrict subsequent TORBA recovery
options, at least using IBM RECOVER.

Andy Lankester
CDB Software

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Bob Jeandron
Sent: 13 January 2005 14:15
To: [login to unmask email]
Subject: [DB2-L] Recover TORBA produced referential violations


A batch job deleted approx. 20000 rows by mistake. We recovered the TBSPs
to a Point in time directly before the batch job ran, not a quiesce point.
This produced about 100 foreign key violations, no parent key. The
violations could have existed before all of this happened, but I don't
believe in coincidence. Did this occur because a parent key was updated and
the child table update did not complete? Which makes sense, but I foolishly
believed that this would be one Unit of Recovery which would be rolled back
in the recovery process.

How can recover TORBA cause referential violations?

Thanks in advance.


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.6.11 - Release Date: 12/01/2005


---------------------------------------------------------------------------------
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

Isaac Yassin

Re: Recover TORBA produced referential violations
(in response to Andy Lankester)
No 3rd party tools, not even IBM tool.

Just go to the code corner at IDUG site and download my REXX for analyzing
SYSUMMRY data.
It will help you finding a "good" point to recover to.

Isaac Yassin

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Grainger, Phil
Sent: Thursday, January 13, 2005 4:25 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Recover TORBA produced referential violations

How do you know that the RBA you chose is not IN THE MIDDLE of a UNIT OF
WORK affecting this table?


Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Bob Jeandron
Sent: 13 January 2005 14:15
To: [login to unmask email]
Subject: [DB2-L] Recover TORBA produced referential violations

A batch job deleted approx. 20000 rows by mistake. We recovered the TBSPs
to a Point in time directly before the batch job ran, not a quiesce point.
This produced about 100 foreign key violations, no parent key. The
violations could have existed before all of this happened, but I don't
believe in coincidence. Did this occur because a parent key was updated and
the child table update did not complete?
Which makes sense, but I foolishly believed that this would be one Unit of
Recovery which would be rolled back in the recovery process.

How can recover TORBA cause referential violations?

Thanks in advance.




__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail

------------------------------------------------------------------------
---------
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

----------------------------------------------------------------------------
-----
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

---------------------------------------------------------------------------------
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

Bob Jeandron

Re: Recover TORBA produced referential violations
(in response to Isaac Yassin)
Sorry, forgot to mention that all tables in application were recovered to
the same point in time by the same job.

---------------------------------------------------------------------------------
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