Section 13.7. Recovering a Dropped Table

13.7. Recovering a Dropped Table

You may accidentally drop a table that has data you still need. To recover such a table, you can perform a database restore operation, followed by a database roll forward operation to a Point In Time before the table was dropped. However, all of the changes you made after the table was dropped are lost. Moreover, this process may be time-consuming if the database is large, and your data will be unavailable during recovery.

DB2 offers a dropped table recovery feature that makes recovering a dropped table easier. This feature lets you recover your dropped table data using table space-level restore and roll forward operations. This is faster than database-level recovery, and your database remains available to users.

For a dropped table to be recoverable, the table space in which the table resides must have the DROPPED TABLE RECOVERY option turned on. By default, dropped table recovery is enabled for newly created data table spaces. To alter this feature, use the ALTER TABLESPACE statement. 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.

 SELECT TBSPACE, DROP_RECOVERY FROM SYSCAT.TABLESPACES 

NOTE

The DROPPED TABLE RECOVERY option is limited to regular table spaces only, and does not apply to temporary table spaces and table spaces containing LOBs and LONG VARCHARs

To recover a dropped table, perform the following steps.

1.	Identify the dropped table by invoking the LIST HISTORY command with the DROPPED TABLE option. This command displays the dropped table ID in the Backup ID column and shows the DDL statement to recreate the table. LIST HISTORY DROPPED TABLE ALL FOR dbname
2.	Restore a database-level or table space-level backup image taken before the table was dropped. RESTORE DB dbname TABLESPACE (tablespace_name) ONLINE
3.	Create an export directory to which files containing the table data are to be written. In a partitioned database environment, this directory must either be accessible to all database partitions or exist on each partition.
4.	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. ROLLFORWARD DB dbname TO END OF LOGS TABLESPACE ONLINE RECOVER DROPPED TABLE dropped_table_id TO export_directory If successful, subdirectories under this export directory are created automatically by each database partition. These subdirectories are named NODEnnnn, where nnnn represents the database partition 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 The data file is a delimited file.
5.	Recreate the table using the CREATE TABLE statement from the recovery history file, obtained in step 1.
6.	Import the table data that was exported during the roll forward operation into the table. IMPORT FROM data OF DEL INSERT INTO table

NOTE

The DB2 Recovery Expert is a multiplatform tool that you can use to easily recover a dropped table and its dependent objects, including indexes, authorizations, DDL, and data. See the Resources section for more information about this tool.