you may some times drop a db2 table accidental. To restore a full database backup it would take more time if the database size is big.

here is how you recover the dropped table:-

  • create a database to test table recovery

#db2 create db testdt using codepage 1256 country AA
DB20000I  The CREATE DATABASE command completed successfully.

  • Check if tablespace are enabled for drop table recovery

#db2 “select TBSPACE, DROP_RECOVERY from SYSCAT.TABLESPACES
TBSPACE                                                                                                                          DROP_RECOVERY
——————————————————————————————————————————– ————-
SYSCATSPACE                                                                                                                      N
TEMPSPACE1                                                                                                                       N
USERSPACE1                                                                                                                       Y
  3 record(s) selected.

 

  • Enable database for rollforwad recovery

#db2 “update database configuration for testdt using  logretain on”
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 become
effective.

  • take a backup of the database

#db2 backup db testdt
Backup successful. The timestamp for this backup image is : 20090311121103

  • create some tables in the database

#db2 connect to testdt

Database Connection Information

 Database server        = DB2/NT 9.5.0
 SQL authorization ID   = ADMINIST…
 Local database alias   = TESTDT

db2 “create table TORECOVER(NAME varchar(55) not null primary key)”
DB20000I  The SQL command completed successfully.

#db2 “insert into TORECOVER values(‘one’) “
DB20000I  The SQL command completed successfully.

#db2 “insert into TORECOVER values(‘two’) “
DB20000I  The SQL command completed successfully.

#db2 “insert into TORECOVER values(‘three’) “
DB20000I  The SQL command completed successfully.

#db2 “insert into TORECOVER values(‘four’) “
DB20000I  The SQL command completed successfully.

  • drop the table

#db2 “drop table TORECOVER”
DB20000I  The SQL command completed successfully.

  • check the database history to get the droped table details

#db2 “list history dropped table all for testdt”

                    List History File for testdt

Number of matching file entries = 1

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 — — —————— —- — ———— ———— ————–
  D  T  20090311121524                                        000000000000e60000020004
 —————————————————————————-
  “ADMINISTRATOR”.”TORECOVER” resides in 1 tablespace(s):

  00001 USERSPACE1
 —————————————————————————-
    Comment: DROP TABLE
 Start Time: 20090311121524
   End Time: 20090311121524
     Status: A
 —————————————————————————-
  EID: 2

 DDL: CREATE TABLE “ADMINISTRATOR”.”TORECOVER” ( “NAME” VARCHAR(55) NOT NULL )  IN “USERSPACE1” ;
 —————————————————————————-
 

  •  restore the tablespace which had the droped table

#db2  “restore database testdt tablespace (USERSPACE1) “
DB20000I  The RESTORE DATABASE command completed successfully.

  • roll forward the database with recover dropped table option

#db2 “rollforward database testdt to end of logs and complete recover dropped table 000000000000e60000020004 to ‘C:\Documents and Settings\Administrator\work\'”

                                 Rollforward Status

 Input database alias                   = testdt
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    =  –
 Last committed transaction             = 1970-01-01-00.00.00.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.

  • recreate the droped table

#db2 “create table TORECOVER(NAME varchar(55) not null primary key)”
DB20000I  The SQL command completed successfully.

  • import the data to the table

#db2 “IMPORT FROM ‘C:\Documents and Settings\Administrator\work\NODE0000\data’ OF DEL METHOD P(1) MESSAGES ‘C:\Documents and Settings\Administrator\work\importlog.msg’ INSERT INTO  TORECOVER (NAME)”

Number of rows read         = 4
Number of rows skipped      = 0
Number of rows inserted     = 4
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 4

  • check if the rows are imported properly

#db2 “select * from TORECOVER”

NAME
——————————————————-
one
two
three
four