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