Restore an Accidentally Dropped DB2 Table Space!

DB2 belongs to Relational Database Management System (RDBMS) family and based on relational models.  IBM introduced this file format in 1983 for Multiple Virtual Storage (MVS) mainframe platform which was later redesigned as DB2 ‘common server’ products. It was redesigned with general code base in order to run on various operating system platforms such as Windows, UNIX and currently Linux. Using this file, user is able to store and manipulate large amount of data. DB2 table space is a set of volumes on disks to hold data sets where tables are actually stored. Table spaces have all the tables in it. One or more tables can be available on a table space. A table space consists of several VSAM linear data sets. It is divided in equal sized units known as pages. The pages size can be specified as 4 KB, 8 KB, 16 KB, or 32 KB but the default size is 4KB. In every database, must keep one table space as this is the best. In most table space, data can be compressed that enable you to store more data on every data page.

CAUSES:

In some cases, you may drop a table space accidently such as table space is dropped when all the tables is explicitly created or when someone executes a DROP TABLESPACE statement unintentionally for specific table space.

EFFECTS:

When the table space is dropped then DB2 loses all information of image copies for that specific table space. Though, you will not lose image copy data set but while locating it lead to the requirement for examining the image copy job listings or recording information about the image copies manually.

RESOLUTION:

When you have accidently dropped a table that includes LOB table space also then you will be able to recover the table space. In order to restore an accidently dropped DB2 Table Space, the first thing you need to set up the database for the Table recovery. For this, you require to do the following mentioned steps:

• Firstly, turn on the archival logging.
• Then, create table space with DROPPED TABLE RECOVERY ON.

You can also do ALTER TABLESPACE Statement while creating Table space if above mentioned is not done.

 ALTER TABLESPACE DROPPED TABLE RECOVERY ON

Below mentioned procedure will help you to restore an accidently dropped table:

Step 1: In order to get the information about the dropped table id and the DDL so that table is created, list the history file.

DB2 LIST HISTORY DROPPED TABLE FOR

Get the backup id and DDL in order to create table from the listing provided.

Step 2: By using the ‘RESTORE’ backup command, you can restore the database from the available backup image. It can be either of one of them database level restore or tablespace level restore.

Step 3: Using ‘RECOVER DROPPED TABLE’ option, rollforward the database/tablespace in time to end of logs or point.

ROLLFORWARD DATABASE TO END OF LOGS AND COMPLETE RECOVER DROPPED TABLE to

You can use ‘To a Point-in-Time’ instead of ‘TO END OF LOGS’.

You can obtain backup id from the history command list.

You will get the path in which file containing the dropped table row required to put.

Step 4: In the ‘LIST HISTORY’ command, create the table using the DDL in the output.

Step 5: In the file named ‘data’ in ‘path’ just import your data there.

If you need to recover more than one dropped table then repeat the Rollforward process for every table. Apart for the last table, there is no need to include “AND COMPLETE’ option of the ROLLFORWARD command.

If your file gets corrupted due to this then you need to use data recovery software. There are several software available in the market to repair corrupted files but they are not trusted since they results in destroying your db2 file permanently. In order to avoid this situation, use trusted software which is DB2 File Repair Tool software that can repair your corrupted or inaccessible db2 file without losing any data. This tool also provides manual option to select so that you can recover database stored at any location on your system. This software has user friendly interface in order to repair your db2 files easily.