DB2 Version 9.7 for Linux, UNIX, and Windows

Recovering a dropped table

You might occasionally drop a table that contains data you still need. If this is the case, you should consider making your critical tables recoverable following a drop table operation. You could recover the table data by invoking a database restore operation, followed by a database rollforward operation to a point in time before the table was dropped. This can be time-consuming if the database is large, and your data will be unavailable during recovery. The dropped table recovery feature lets you recover your dropped table data using table space-level restore and rollforward operations. This will be faster than database-level recovery, and your database will remain available to users.

About this task

There are some restrictions on the type of data that is recoverable from a dropped table. It is not possible to recover:

The DROPPED TABLE RECOVERY option can not be used for temporary table.
The metadata associated with row types. (The data is recovered, but not the metadata.) The data in the hierarchy table of the typed table will be recovered. This data might contain more information than appeared in the typed table that was dropped.
XML data. If you attempt to recover a dropped table that contains XML data, the corresponding column data will be empty.

If the table was in reorg pending state when it was dropped, the CREATE TABLE DDL in the history file will not match exactly that of the import file. The import file will be in the format of the table before the first REORG-recommended ALTER was performed, but the CREATE TABLE statement in the history file will match the state of the table including the results of any ALTER TABLE statements."

File type modifiers to use with LOAD or IMPORT

To recover the table by loading or importing, specify the following file type modifiers:

The file type modifier usegraphiccodepage should be used in the IMPORT or LOAD command if the data being recovered is of the GRAPHIC or VARGRAPHIC data type. The reason is that it might include more than one code page.
The file type modifier delprioritychar should be used in the IMPORT or LOAD commands. It allows LOAD and IMPORT to parse rows which contains newline characters within character or graphic column data.

Only one dropped table can be recovered at a time. You can recover a dropped table by doing the following:

Before you begin

For a dropped table to be recoverable, the table space in which the table resides must have the DROPPED TABLE RECOVERY option turned on. This can be done during table space creation, or by invoking the ALTER TABLESPACE statement. The DROPPED TABLE RECOVERY option is table space-specific and limited to regular table spaces. To determine if a table space is enabled for dropped table recovery, you can query the DROP_RECOVERY column in the SYSCAT.TABLESPACES catalog table.

The dropped table recovery option is on by default when you create a table space. If you do not want to enable a table space for dropped table recovery, you can either explicitly set the DROPPED TABLE RECOVERY option to OFF when you issue the CREATE TABLESPACE statement, or you can use the ALTER TABLESPACE statement to disable dropped table recovery for an existing table space. The dropped table recovery feature may have a performance impact on forward recovery if there are many drop table operations to recover or if the history file is large.

When a DROP TABLE statement is run against a table whose table space is enabled for dropped table recovery, an additional entry (identifying the dropped table) is made in the log files. An entry is also made in the recovery history file, containing information that can be used to re-create the table.

For partitioned tables, dropped table recovery is always on even if the dropped table recovery is turned off for non-partitioned tables in one or more table spaces. Only one dropped table log record is written for a partitioned table. This log record is sufficient to recover all the data partitions of the table.

Procedure

Identify the dropped table by invoking the LIST HISTORY DROPPED TABLE command. The dropped table ID is listed in the Backup ID column.
Restore a database- or table space-level backup image taken before the table was dropped.
Create an export directory to which files containing the table data are to be written. This directory must either be accessible to all database partitions, or exist on each database partition. Subdirectories under this export directory are created automatically by each database partition. These subdirectories are named NODEnnnn, where nnnn represents the database partition or node number. Data files containing the dropped table data as it existed on each database partition are exported to a lower subdirectory called data. For example,
```
\export_directory\NODE0000\data.
```
Roll forward to a point in time after the table was dropped, using the RECOVER DROPPED TABLE option on the ROLLFORWARD DATABASE command. Alternatively, roll forward to the end of the logs, so that updates to other tables in the table space or database are not lost.
Re-create the table using the CREATE TABLE statement from the recovery history file.
Import the table data that was exported during the rollforward operation into the table. If the table was in reorg pending state when the drop took place, the contents of the CREATE TABLE DDL might need to be changed to match the contents of the data file.