Before you can recover a database table, you must have backed up its housing database. Don't worry, that doesn't mean clairvoyance is a requirement. As you will see shortly, you don't have to backup your database right before your table is dropped. The database backup can occur right after you create the database. Furthermore, you could even perform it before you create the deleted table. You can then use the database logs to reconstruct your database (for more on this subject, read through my previous Solution on DB2 database recovery
As mentioned previously, DB2 uses database logs to recover the changes that occurred to the database after a backup. For recovery to take place, you need to enable a logging type called archival logging. Enter the following command into the command line processor:
db2 => update database configuration for DBSTUDY using
The command should return a message similar to the following:
DB20000I The UPDATE DATABASE CONFIGURATION command
SQL1363W One or more of the parameters submitted for
immediate modification were not changed dynamically.
For these configuration parameters, all applications
must disconnect from this database before the changes
As the message implies, you have to disconnect all applications from the database for the changes to take effect.
You can also enable archival logging using the Database Logging wizard of the Control Center as shown in Figure 3.
|Figure 3: Enabling Archive Logging via the Database Logging Wizard|
You can backup your database via the command line with the following syntax:
backup database DBSTUDY user db2admin using db2admin to
This syntax backs up your database to
c:\dbbackup. If the backup operation is successful, you should receive a notification like the following:
Backup successful. The timestamp for this backup image is :
Be sure to keep the timestamp of the backup operation in a safe place. You'll use it later.
A Table to Be Recovered
To learn how to recover a dropped table, you obviously need a table to work with first. The following syntax creates a simple table consisting of one column that holds names:
db2=> create table RECOVERME(NAME varchar(55) not null
Now, populate the table with a row of data:
db2=> insert into RECOVERME values('Kulvir Singh Bhogal').
To showcase the power of the table recovery feature, create another table and add a row to it also (you'll see why later):
db2=> create table ANOTHERTABLE(animals varchar(55) not
null primary key)
Next, insert a row:
db2=> insert into ANOTHERTABLE values('Giraffe').
Now "accidentally" drop the RECOVERME table:
db2=> drop table RECOVERME