Troubleshooting Guide

Dropped Table Point-in-Time Recovery

This feature allows you to recover dropped tables to the table space. Prepare every table for Point-in-Time (PIT) recovery in a case before a table is dropped.

Accidentally dropping a table

Sometimes a table is accidentally dropped. Once committed, the drop statement cannot be rolled back. To restore the table you must perform a full database restore and roll forward to a point in time prior to the drop. This action requires that the entire database be made unavailable until the end of the roll forward. The transactions made after the table drop will be lost. You cannot use the table space restore and roll forward.

If dropped table recovery is enabled

If dropped table recovery is enabled, when a DROP TABLE statement is executed, DB2 writes an additional log entry in the log files. This entry contains the following items:

• Name of the table being dropped
• Time stamp
• GXID (Global Transaction ID). This number is unique for each transaction and is constant across partitions.
• TID (Table Space ID)
• FID (Table ID)

History File Entry

DB2 also writes an entry to the history file, which contains the DDL statement used to re-create the table, as well as a copy of the log record. The administrative information provided by the history file includes the following:

• ROLLFORWARD
• ALTER TABLESPACE
• REORG
• RUNSTATS
• DROP TABLE information

To recover a dropped table

1. Obtain a special ID that identifies the dropped table. This ID can be obtained from the history file by the command LIST HISTORY DROPPED TABLE ALL. This command displays a list of tables that have been dropped, their names, the time at which they were dropped, a special ID string to identify the tables during the rollforward phase, and the DDL commands used to re-create the table.
2. Restore a backup taken before accidentally dropping the table.
3. ROLLFORWARD DATABASE ... RECOVER DROPPED TABLE TableID to /expdir. Rollforward to the end of logs using the option RECOVER DROPPED TABLE <tableID> TO <export_dir>, where Table ID is the string obtained in step (1) and export_dir is the directory where the output files are written.
4. CREATE TABLE. Re-create the table using the CREATE TABLE DDL statement obtained in step number (1).
5. Import the data exported during the roll forward phase into the table.

Notes:

1. Only a single dropped table can be recovered at a time. To recover multiple accidentally dropped tables, the recovery sequence will have to be attempted multiple times.

2. The RECOVERABLE TABLES option (ON or OFF) is only allowed on REGULAR table spaces

3. The table is not re-created; rather, the data in the dropped table is extracted from the log files and written in DEL ASC format to the specified export directory.