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.

Get IBM DB2 Recovery Expert for Multiplatforms V2 Usage Guide now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.