Forward log recovery for a dropped table

Aldo Tomassi

Forward log recovery for a dropped table
Hi all,

I'm in trouble in recovering log records for a dropped table in production
environment (Db2 V7). This is the scenario:

- 01.50 pm: full image copy of the tablespace (TEVTB075);

- 02.56 pm: drop table (VTTAB075) and create table (same name, different
OBID);

- In the meantime, a CICS transaction inserted 6 records into table
VTTAB075;

- DSN1COPY (parm OBIDXLAT) to copy the Image copy data set on tablespace
TEVTB075, table VTTAB075;



At this point in time, table VTTAB075 contains the original 143976 records.

My problem is: how can I recover (or, better, "can I recover?") the 6
missing log records? I do not have any other log tool but Platinum Log
Analyzer: I've run a log scan (start time 01.50 pm/end time 02.56 pm, table
filter) but PT LA didn't detect any change. Did I miss anything?



An extract from Administration Guide follows:

Important: After you complete this step you have essentially recovered the
table space to the point in time of the last image copy was taken. If you
want use log records to perform forward recovery on the table space, you
must use IBM's DB2 UDB Log Analysis Tool for z/OS at this point in the
recovery procedure.



Thanks in advance

Aldo Tomassi



Mainframe DB2 DBA

___________________________________________________

e-mail: [login to unmask email]




______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: Forward log recovery for a dropped table
(in response to Aldo Tomassi)
Hi Aldo



Please call CA Tech Support ASAP



We seem to have done this quite a few times recently for people (there
must be a "DROP TABLE" virus affecting DBAs or something!



There is a procedure we can walk through with you to get at those log
records from the old table



As there are only 6, this should be a simple task to fix



Phil Grainger

CA

Senior Principal Product Manager

Phone: +44 (0)1753 577 733

Mobile: +44 (0)7970 125 752

eMail: [login to unmask email]



Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL



CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Tomassi, Aldo
Sent: 27 November 2008 09:30
To: [login to unmask email]
Subject: [DB2-L] Forward log recovery for a dropped table



Hi all,

I'm in trouble in recovering log records for a dropped table in
production environment (Db2 V7). This is the scenario:

- 01.50 pm: full image copy of the tablespace (TEVTB075);

- 02.56 pm: drop table (VTTAB075) and create table (same name, different
OBID);

- In the meantime, a CICS transaction inserted 6 records into table
VTTAB075;

- DSN1COPY (parm OBIDXLAT) to copy the Image copy data set on tablespace
TEVTB075, table VTTAB075;



At this point in time, table VTTAB075 contains the original 143976
records.

My problem is: how can I recover (or, better, "can I recover?") the 6
missing log records? I do not have any other log tool but Platinum Log
Analyzer: I've run a log scan (start time 01.50 pm/end time 02.56 pm,
table filter) but PT LA didn't detect any change. Did I miss anything?



An extract from Administration Guide follows:

Important: After you complete this step you have essentially recovered
the table space to the point in time of the last image copy was taken.
If you want use log records to perform forward recovery on the table
space, you must use IBM's DB2 UDB Log Analysis Tool for z/OS at this
point in the recovery procedure.



Thanks in advance

Aldo Tomassi



Mainframe DB2 DBA

___________________________________________________

e-mail: [login to unmask email]





________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
< http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more. If you have not yet signed
up for Basic Membership in IDUG, available at no cost, click on Member
Services < http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Leslie

Re: Forward log recovery for a dropped table
(in response to Phil Grainger)
Hi Aldo,

CA LA should do the trick.

Just to confirm have you run a REPORT RECOVER on the VTTAB075 object?

Should show the UR for the period of the 6 records and this should then mean
that LA should be able to find the records and produce re-do SQL for you.

Have you tried widening the times slightly? But I would do the REPORT
utility first and see what times I am dealing with, not saying you are
wrong, I just like to confirm things.

Leslie



_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Tomassi, Aldo
Sent: 27 November 2008 09:30
To: [login to unmask email]
Subject: [DB2-L] Forward log recovery for a dropped table



Hi all,

I'm in trouble in recovering log records for a dropped table in production
environment (Db2 V7). This is the scenario:

- 01.50 pm: full image copy of the tablespace (TEVTB075);

- 02.56 pm: drop table (VTTAB075) and create table (same name, different
OBID);

- In the meantime, a CICS transaction inserted 6 records into table
VTTAB075;

- DSN1COPY (parm OBIDXLAT) to copy the Image copy data set on tablespace
TEVTB075, table VTTAB075;



At this point in time, table VTTAB075 contains the original 143976 records.

My problem is: how can I recover (or, better, "can I recover?") the 6
missing log records? I do not have any other log tool but Platinum Log
Analyzer: I've run a log scan (start time 01.50 pm/end time 02.56 pm, table
filter) but PT LA didn't detect any change. Did I miss anything?



An extract from Administration Guide follows:

Important: After you complete this step you have essentially recovered the
table space to the point in time of the last image copy was taken. If you
want use log records to perform forward recovery on the table space, you
must use IBM's DB2 UDB Log Analysis Tool for z/OS at this point in the
recovery procedure.



Thanks in advance

Aldo Tomassi



Mainframe DB2 DBA

___________________________________________________

e-mail: [login to unmask email]




_____

< http://idug.org/lsNA > IDUG 2009 - North America * May 11-15, 2009 *
Denver, CO, USA

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at IDUG.ORG
< http://www.idug.org/lsidug > under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more. If you have not yet signed up
for Basic Membership in IDUG, available at no cost, click on Member Services
< http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Steen Rasmussen

Re: Forward log recovery for a dropped table
(in response to Leslie)
If the OBID is not the same you will have to use DDL FILE MAPPING since
the log only has the internal ID's.

The DDL file must hold the table DDL and then you map the NEW OBID with
the original prior to the drop.



Steen Rasmussen
CA

Sr Engineering Services Architect

IBM Certified Database Associate - DB2 9 Fundamentals

IBM Certified Database Administrator - DB2 9 DBA for z/OS





From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Grainger, Phil
Sent: Thursday, November 27, 2008 4:51 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Forward log recovery for a dropped table
Importance: High



Hi Aldo



Please call CA Tech Support ASAP



We seem to have done this quite a few times recently for people (there
must be a "DROP TABLE" virus affecting DBAs or something!



There is a procedure we can walk through with you to get at those log
records from the old table



As there are only 6, this should be a simple task to fix



Phil Grainger

CA

Senior Principal Product Manager

Phone: +44 (0)1753 577 733

Mobile: +44 (0)7970 125 752

eMail: [login to unmask email]



Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL



CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Tomassi, Aldo
Sent: 27 November 2008 09:30
To: [login to unmask email]
Subject: [DB2-L] Forward log recovery for a dropped table



Hi all,

I'm in trouble in recovering log records for a dropped table in
production environment (Db2 V7). This is the scenario:

- 01.50 pm: full image copy of the tablespace (TEVTB075);

- 02.56 pm: drop table (VTTAB075) and create table (same name, different
OBID);

- In the meantime, a CICS transaction inserted 6 records into table
VTTAB075;

- DSN1COPY (parm OBIDXLAT) to copy the Image copy data set on tablespace
TEVTB075, table VTTAB075;



At this point in time, table VTTAB075 contains the original 143976
records.

My problem is: how can I recover (or, better, "can I recover?") the 6
missing log records? I do not have any other log tool but Platinum Log
Analyzer: I've run a log scan (start time 01.50 pm/end time 02.56 pm,
table filter) but PT LA didn't detect any change. Did I miss anything?



An extract from Administration Guide follows:

Important: After you complete this step you have essentially recovered
the table space to the point in time of the last image copy was taken.
If you want use log records to perform forward recovery on the table
space, you must use IBM's DB2 UDB Log Analysis Tool for z/OS at this
point in the recovery procedure.



Thanks in advance

Aldo Tomassi



Mainframe DB2 DBA

___________________________________________________

e-mail: [login to unmask email]





________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
< http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more. If you have not yet signed
up for Basic Membership in IDUG, available at no cost, click on Member
Services < http://www.idug.org/lsms >



________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
< http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more. If you have not yet signed
up for Basic Membership in IDUG, available at no cost, click on Member
Services < http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Peter Vanroose

Re: Forward log recovery for a dropped table
(in response to Steen Rasmussen)
Or you could have (re)created the table to have the same OBID as the dropped
one, by using the OBID option of CREATE TABLE.

-- Peter Vanroose.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms