What to expect when you do not have a backup

Body

There is an alarming and disturbing trend where databases are no longer being backed up.  Then when the database becomes corrupted due to disk failure or even a db2 defect, users are left calling into support to try and rescue the database.    Expectations should be set appropriately beforehand, as not all corruption can be easily recovered from.   Here are a few tips on what to expect when you have corruption and do not have a backup.

    •    There will be data loss.  The only exception is when the corruption is on an index page.   For index page corruption, the index can be recreated based on the existing data in the table.

    •    The export command will not be able to tolerate bad data pages.   Db2dart with the /ddel option will have to be used to salvage the good rows from the table.   The /ddel option does not export any lob or xml columns, so tables with these columns will result in missing lob and xml data.  

    •    If the corruption is on object page zero of either the data object or the index object, then the table cannot be dropped.   All non corrupted tables will have to be moved to a new tablespace and then the tablespace can be dropped.

    •    If the corruption is in a metadata object like an Extent Map Page (EMP) then the data in the table cannot be accessed and the table cannot be dropped.  All non corrupted tables will have to be moved to a new tablespace and then the tablespace can be dropped.

    •    If the corruption is in a system catalog table like syscolumns then both the table and tablespace are not able to be dropped.   Of course, tools like db2look would also not work.   All tables not affected by corruption will have to be exported and imported into a new database.

    •    Corruption in a metadata file such as SQLOGCTL.LFH or SQLSPCS.1 will prevent the database from being activated and may prevent any access to the database.    

    •    Corruption in a metadata object such as the object table will prevent access to the tables affected by the corruption.   All non corrupted tables will have to be moved to a new tablespace and then the tablespace can be dropped.

If zero data loss is required by the business plan,  then make sure to have a valid backup and be able to roll forward through all saved transaction logs.