Learning Visually with Examples [Electronic resources] نسخه متنی

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.

LIST HISTORY DROPPED TABLE ALL FOR  
dbname 

RESTORE DB  
dbname  
TABLESPACE ( 
tablespace_name 
) ONLINE 

ROLLFORWARD DB  
dbname 
TO END OF LOGS TABLESPACE ONLINE 
RECOVER DROPPED TABLE  
dropped_table_id 
TO export_ 
directory 

\export_directory\NODE0000\data

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.