Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2009
    Posts
    42

    Unanswered: recover table if dropped by mistake

    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.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    which version ?
    Quote Originally Posted by Stealth_DBA
    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.

    DB2 Database for Linux, UNIX, and Windows

    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.

    alter table ....... restrict on drop
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can recover the table if you had the drop-table-recovery feature activated on the tablespace. (That's DB2 LUW.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2009
    Posts
    42
    Thank you. Can I recover my table using tablespace backup if drop-table-recovery feature is not activated on the tablespace?

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    This applies to cases where the drop table recovery option is not enabled.

  8. #8
    Join Date
    Apr 2009
    Posts
    42
    Thank you. Is this process different on DB2 z/OS?

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  10. #10
    Join Date
    Dec 2008
    Posts
    76
    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.
    RD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •