lot of ugly things can happen in the early morning hours when you are frantically trying to meet a deadline. For example, your mind can falter for a split second and you might accidentally drop an IBM DB2 Universal Database table. Before your stomach starts twisting in knots and you scramble for your stress ball, relax. Believe it or not, you might be able to recover the table.

This article demonstrates a DB2 feature that allows you to recover tables. By performing a few steps when you create a database, you can recover (no pun intended) from a table dropping mishap.

This Solution assumes you are using DB2 Universal Database Version 8.1. You can download a trial version from IBM.

How do I ensure that the critical tables in my DB2 database are recoverable in case I inadvertently drop them?



Apply DB2’s built-in dropped table recovery feature to recover accidentally dropped tables.

Why Not Just Use the Database Restore and Rollforward Functions?
DB2 allows you to recover a database’s data using a database restore operation (see Defy Murphy’s Law with DB2 Backup and Restore). In fact, you could use the restore operation in tandem with the rollforward operation to restore the database to a point before the table was dropped, but restoring the entire database can be very time consuming. More importantly, during this database-level operation, you can’t access your database. On the flipside, with the table recovery feature, your table-level restore effort is more directed, allowing you to use the database in question.

The Set Up
To apply the dropped table recovery feature to an end-to-end example, first create a database (the sample DB in this Solution is named DBSTUDY). You can do so either graphically using the DB2 Control Center or via the command line:

db2=>  create database DBSTUDY

Next, connect to the database. In the case of this sample, DBSTUDY has a user named db2admin with a password of db2admin:

db2=> connect to DBSTUDY user db2admin using db2admin

Enable Dropped Table Recovery
Inside DB2, the database is divided into table spaces. Database tables are assigned to these table spaces. In order for a dropped table to be recoverable, you have to turn on the DROPPED TABLE RECOVERY option on the table space that houses it. This option is enabled by default for newly created data table spaces if you used the command line to create your table space. However, if you use the Control Center to create a housing table space, you must enable the option in the wizard (see Figure 1).

Figure 1: Enabling the Dropped Table Recovery Option with the Create Table Space Wizard

To make sure a given name space has table recovery enabled, query the DROP_RECOVERY column of the SYSCAT.TABLESPACES catalog table:

db2 => select TBSPACE, DROP_RECOVERY from SYSCAT.TABLESPACES

In the sample case, the DBSTUDY database exists in the table space named USERSPACE1. Figure 2 shows the execution of the above query, which confirms that USERSPACE1 has DROP_RECOVERY enabled.

Figure 2: Query to See DROP_RECOVERY Status of Table Spaces

To enable the DROPPED TABLE RECOVERY option after you’ve created a table space, use the following syntax:

ALTER TABLESPACE  DROPPED TABLE RECOVERY ON

Backing Up
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 
logretain on

The command should return a message similar to the following:

DB20000I  The UPDATE DATABASE CONFIGURATION command 
completed successfully.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 
becomeeffective.

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:

db2 => backup database DBSTUDY user db2admin using db2admin to 
c:dbbackup

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 : 
20040320123838

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 
primary key)

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

The Recovery Process
Pretend you don’t remember the name of the table you dropped. You can find out with the following command:

db2=> list history dropped table all for DBSTUDY

This syntax gives you information about tables that have been dropped, including the name of the table space that housed them and backup IDs for the tables (see Figure 4).

Figure 4: Results of the List History Dropped Table Command

Figure 4 also shows the DDL (Data Definition Language) necessary to reconstruct the table you dropped. Take note of this for later.

Now you can restore your database from the backup image you made earlier using the RESTORE backup command. This can be either a database-level restore or a tablespace-level restore. When deciding which one to choose, understand that tablespace-level restore requires exclusive access only to the tablespace and not the entire database during the recovery. The following syntax restores your database tablespace (USERSPACE1) using the backup image you created before:

db2 => restore database DBSTUDY tablespace (USERSPACE1) 
from "c:dbbackup" taken at 20040320123838 without 
rolling forward without prompting

You can also graphically perform the restore operation using the Restore Data wizard of the Control Center (see Figure 5).

Figure 5: The Restore Data Wizard

At this point in time, if you tried to query for data in the RECOVERME and ANOTHERTABLE tables, DB2 would inform you that the tables do not exist. You need to rollforward to the end of your logs to get back to the point of your last data transaction (i.e., the insert on ANOTHERTABLE). You do this with the following command, which instructs the database to recover the dropped table’s data to c:dbback (specifying which table with the backup ID obtained from the list history statement):

db2 => rollforward database DBSTUDY user db2admin using 
db2admin to end of logs
and complete recover dropped table 000000000000af000002002 
to c:dbbackup

If the rollforward operation completes successfully, you should see a confirmation like the one Figure 6 shows.

Figure 6: Confirmation That the ROLLFORWARD Command Completed Successfully

Now you’re at the point where you can see ANOTHERTABLE and its data. However, you still cannot see the RECOVERME table since by this time in your database history, it had been deleted. You should confirm this by issuing a couple of simple SELECT queries.

You can use the information from the DDL statement you saw in the LIST history command to reconstruct the RECOVERME table:

DDL: CREATE TABLE "DB2ADMIN"."RECOVERME" ( "NAME" 
VARCHAR(55) NOT NULL )  IN "USERSPACE1" ;

In your ROLLFORWARD operation, you specified the predicate of:

and complete recover dropped table 000000000000af000002002 
to c:dbbackup

When you did that, you created subdirectories under your export directory (i.e., c:dbbackup). The subdirectories are named NODEnnnn (where nnnn represents the database partition or node number), and the data files created house your dropped table data in a subdirectory called data. Take a look at this backup file (see Figure 7).

Figure 7: Peeking into Your Backup Table Data

Importing the Table Content
Now that you have reconstructed your table, you can import data from where you backed up earlier. You can perform this data import via the command line with a statement like the following, which grabs the data from your backup file:

db2=> IMPORT FROM C:dbbackupNODE0000data OF DEL METHOD P 
 (1) MESSAGES C:importlog.msg INSERT INTO 
DB2ADMIN.RECOVERME (NAME)

The table data import process can also be performed using the Control Center (see Figure 8).

Figure 8: Using the Control Center to Import Your Dropped Table’s Data

At this point, you can confirm that your data recovery process was successful with a query of your RECOVERME table (see Figure 9).

Figure 9: Confirming Your Efforts Were Successful

Avoid Disaster with a Little Due Diligence
Inadvertently dropping a database table can be catastrophic. Fortunately, DB2 has a built-in, easy-to-use dropped table recovery feature that can save the day (or some heads from rolling). You have to do some work upfront to make your critical tables recoverable, but as you have seen, doing so is a pretty painless process that DB2 administrators definitely should consider.