You might occasionally drop a table
that contains data you still need. If so, 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. The restore and rollforward operations can
be time-consuming if the database is large, and your data is unavailable
during the recovery. The dropped table recovery feature
lets you recover your dropped table data by using table space-level
restore and rollforward operations.
This table space-level
recovery is faster than database-level recovery, and your database remains
available to users.
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 option can be enabled 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. If
there are many drop table operations to recover, or if the history
file is large, the dropped table recovery feature might
have a performance impact on forward recovery.
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.
About this task
If the table was in reorg pending state when it was dropped,
the CREATE TABLE DDL in the history file does not match exactly that
of the import file. The import file is in the format of the table
before the first REORG-recommended ALTER was performed,
but the CREATE TABLE statement in the history file matches 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.
Restrictions
Only
one dropped table can be recovered at a time.
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 cannot 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 is 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 is empty.
Procedure
You can recover a dropped table by doing the following:
- 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,
by using the RECOVER DROPPED TABLE parameter
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 by 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.