IBM DB2 Recovery Expert for Multiplatforms V2 Usage Guide

198 IBM DB2 Recovery Expert for Multiplatforms V2 Usage Guide

As described in “Log analysis against table with LOB data in different table

space” on page 118, log analysis cannot produce redo SQL for tables with LOB

data in separate table space. This implies that for such tables you can only

recover the table from the backup image. You cannot create, apply, and redo

transactions to bring them up to a point-in-time after the backup was taken.

7.2.4 Dropped table recovery options without DB2 Recovery Expert

Using standard DB2 functions, you can recover a dropped table if your table

space has DROPPED TABLE RECOVERY enabled. (For more information, see

Administration Guide: Implementation V8, SC09-4820.) However, you have to

restore the entire table space, not a single table. This restriction can be an issue

when you have multiple tables per table space. Using DB2 RE, with or without

Dropped table recovery ON, you can still recover a single table until the last

committed transaction.

7.3 Recovering a dropped table space

This section describes the steps to recover a dropped table space.

7.3.1 Scenario description

A System Managed Space (SMS) table space SMSDAT1 has been dropped in

our database. We need to recover this table space completely. In other words,

we need to recover it to the last committed SQL against the table space prior to

the drop table space operation. SQL are run against tables, so this would be the

last committed transactions for one of the tables in the table space.

Tip: We recommend that you enable DROPPED TABLE RECOVERY for your table

spaces, because it causes an entry to be written to the recovery history file

when a table is dropped. Otherwise, if a table is dropped accidentally, it is

much more difficult to determine exactly when it was dropped.

To enable DROPPED TABLE RECOVERY for a table space, you can use the

command:

db2 alter tablespace <table space> dropped table recovery on

When DROPPED TABLE RECOVERY is enabled, this command gives you the time

when an object was dropped:

db2 list history dropped table all for <database name>

Chapter 7. Recovering dropped objects 199

Scenario time line

Table 7-2 shows the time line for this scenario.

Table 7-2 Scenario time line

Situation before table space is dropped

Example 7-5 shows the situation at time between T3 and T4.

Example 7-5 Number of rows before the delete SQL

/home/db2insk1->db2 "select count(*) as total_rows from vps.car"

TOTAL_ROWS

-----------

119986

1 record(s) selected.

Example 7-6 shows the situation just prior to table space drop.

Example 7-6 Number of rows in VPS.CAR table just before table space is dropped

/home/db2insk1->db2 "select count(*) as total_rows from vps.car"

TOTAL_ROWS

-----------

117756

1 record(s) selected.

Action Time

T1 Versioning Repository updated

T2 Full Offline Backup taken

T3 Rows deleted from table VPS.CAR

T4 Table space SMSDAT1 is dropped

T5 Recover SMSDAT1 table space to a time

after T3 and before T4.