A dropped table prevents a DBA from recovering the table space
to any point in time prior to the transaction that dropped the table.


select tbspace,drop_recovery from syscat.tablespaces where tbspace='TBSP1'

DROP_RECOVERY=Y is the default for user-defined table spaces.

select tbsp_name,tablespace_min_recovery_time from table(MON_GET_TABLESPACE('TBSP1',-1)) AS TS
--> based on the local time
db2 list history dropped table containing db2inst1.dept for mydb
这个输出中有个Backup ID，0000000001009d5f00040004, 后面会用到
这个输出中还有这个table的DDL，后面也会用到
--> based on UTC timestamp

select devicetype as device,location,operationtype,objecttype,start_time,
tbspnames from sysibmadm.db_history where operation='B'
这个输出中可用找到表空间TBSP1的最近一个备份的时间点START_TIME,20160822151544, 后面会用到
这个输出中可用找到表空间TBSP1的最近一个备份的LOCATION,/home/db2inst1/backups, 后面会用到

You can also see the same information with:
db2 list history backup all for DB mydb

恢复表空间
db2 "restore db mydb tablespace(tbsp1) online from /home/db2inst1/backups taken at 20160822151544 without prompting"
db2 "select tbsp_name,tbsp_state from table(MON_GET_TABLESPACE('TBSP1',-1)) as n"
tbsp_state - > ROLLFORWARD_PENDING

建立一个目录来放置后面rollforward时候导出文件
mkdir /home/db2inst1/dropdept

恢复表，会把表导出到文件中
db2 "rollforward database mydb to end of logs tablespace online recover dropped table 0000000001009d5f00040004 to /home/db2inst1/dropdept"

ls -l /home/db2inst1/dropdept/NODE0000

db2 "CREATE TABLE DB2INST1.DEPT(DEPTNO CHAR(3),DEPTNAME VARCHAR(36),MGRNO CHAR(6),ADMRDEPT CHAR(3),TEMP CHAR(16) NULL) IN TBSP1 ORGANIZE BY ROW"

db2 "IMPORT FROM /home/db2inst1/dropdept/NODE0000/data of DEL INSERT INTO db2inst1.dept"

db2 "ALTER TABLE db2inst1.dept add restrict on drop"

--To drop a table, you must either be the owner of the table or have CONTROL permission on the table

db2 "select tabname,droprule from syscat.tables where tabname='DEPT'"

--To drop table DEPT when DROPRULE=R, someone with ALTER or CONTROL
permissions or the table owner must issue:
ALTER TABLE db2inst1.dept drop restrict on drop