Browse DevX
Sign up for e-mail newsletters from DevX


Recover Dropped Tables in DB2-4 : Page 4




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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:


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:\dbbackup\NODE0000\data OF DEL METHOD P
(1) MESSAGES C:\importlog.msg INSERT INTO

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.

Kulvir Singh Bhogal works as an IBM consultant, devising and implementing Java-centric solutions at customer sites across the nation. .
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date