Hello,

If my table is dropped by mistake - how to recover it? Can I recover this table from tablespace backup? Can I recover this table if I don't have any backup?

Thank you.

If you don't have a backup, I can't see how you could recover.

If you do have a backup, you should be able to recover. If nothing else, you could recover the database someplace and used DDL to recreate the object and move the data to the newly recreated table.

Not that this helps you now, but there is a parameter when you create a table that restricts dropping the table until you alter the table to remove this restriction. This would prevent accidental drops in the future.

which version ?

You can recover the table if you had the drop-table-recovery feature activated on the tablespace. (That's DB2 LUW.)

Thank you. Can I recover my table using tablespace backup if drop-table-recovery feature is not activated on the tablespace?

You have to restore the db and rollforward to a point prior to the drop table operation. DB2 will force you to rollforward to at least the minimum recovery time after restoring the tablespace and this minimum recovery time will be the time of the last DDL performed against any object in the tablespace.

This applies to cases where the drop table recovery option is not enabled.

Thank you. Is this process different on DB2 z/OS?

I haven't used mainframe for a while... From what I remember, you can recover if you have a full image copy of your tablespace. I also remember recovering a dropped table when the image copy was not available. If the original vsam dataset still exists and the obid of the table has not been reused (??), you can use dsn1copy to copy the data from the vsam dataset to a flat file and then use this file to load the table (assuming you have the DDL to recreate the table). Please don't rely on this info since this may not be accurate and there are might be other options available in newer versions. It's better to wait for mainframe people to answer your z/OS recovery question.

Utilities act at the tablespace level on the mainframe. If you have 1 table defined per tablespace (not unusual on MF) you can use DSN1COPY to directly recover the tablespace. Otherwise, it gets complicated. Of course, this assumes you have an imagecopy (backup) of the tablespace.