The worst experience in the professional life of a database administrator is basically the accidental drop of a particular database object within a particular production subsystem. As a result, most of the IT organizations have found a new strategy to prevent all of these accidental occurrences. Still, it continues to take place and at this juncture, one of the crucial objectives is to recover the database into its original state without any loss of data as soon as possible. At ever point of time, when you are unable to access your database, a huge loss happens to your business as it remains totally unsecured. Generally, these situations are very stressful and at times cause lots of worries for a DBA as the recovery process generally exposes to both human and system error.

About Recovery of a Dropped Table in Db2 Database:

At times, there exists certain restrictions on the type of data that are recoverable from a dropped table. It is not possible to recover

  • The dropped table recovery option cannot be used for a specific temporary table.
  • The metadata are generally associated with the row types. (The data is mainly recovered, but not the metadata.) The data in the hierarchy table of the typed table will be recovered on an immediate basis. As this data might contain more information than that appear in the typed table that was dropped.
  • If you attempt to recover a dropped table that contains XML data, the corresponding column data will always remain empty.

If the table appears to be in a reorganization pending state when it was first dropped, then the Create Table DDL in the history file will not at all match with the imported file. On the other hand, the import file will be found in the format of a table till the first Reorg-recommended alter is performed. The create table statement within the history file will always match the state of the table including the results of any ALTER TABLE statements.

Recover Your Accidentally Dropped Table in Db2 Database with All Blue Solutions!

Contact Us

File type modifiers to use with LOAD or IMPORT

In order to recover the table by loading or importing, one should specify the following file type modifiers:

  • The file type modifier usergraphiccodepage are used in the import or load command, while the data being recovered is of the Graphic or Vargraphic data type. The reason is very simple, as it might include more than one code page as a whole.
  • Whereas, the file type modifier delprioritychar are normally used in the import or load commands. It allows load and import to parse rows which generally contains newline characters within character or graphic column data.


  • One should identify the dropped table simply by invoking with the help of the list history dropped table command, The dropped table ID is basically listed in the backup id column.
  • One should also restore the database or table space level backup image taken before the table was even dropped.
  • At the same time, you should create an export directory to which files containing the table data are needed to be written down. As a result, this directory must either be accessible to all database partitions, or exist on each database partition as a whole. On the other hand, subdirectories under this export directory are mainly created with the aid of each database partition. These subdirectories are named as Nodennnn, where nnnn mainly 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.”
  • Always roll forward to a point in time immediately after the table being dropped, thus, by using the recover dropped table option on the Rollforward database command. Alternatively, one should roll forward to the end of the logs, so that updates to other tables in the table space or database are not yet lost.
  • One can recreate the table simply by using the create table statement from the recovery history file.
  • Try to import the table data that was exported during the rollforward operation within the table. If the table was in reorganization 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.

In conclusion, it can be said that one should always keep in mind that no tools can save you if you are not well prepared for accidental situations and for possible recovery scenarios, but if your backup and recovery procedures are set correctly, and if you have a tool like log analyzer, you can eventually reduce the time of outage and loss of critical data to a minimum level. In one word, a DBA using log analyzer can reduce the time needed to setup recovery jobs to recover a dropped object from hours to minutes as a whole.

Do you plan to recover accidentally dropped table of your Db2 Database? Connect with us, as our experts from All Blue Solutions, will help to recover your accidentally dropped table of your Db2 database.