One of the most painful experiences in the work life of a database administrator (DBA) is probably the accidental drop of a database object on a production subsystem. Most IT shops have procedures to prevent this occurrence, yet it still happens. When it does happen, it is crucial to recover the object into its original state without any loss of data as soon as possible. Every minute during which your applications cannot access data harms your business. These situations are very stressful for a DBA and the recovery process is exposed to many human errors.       

This article describes a specific situation in an IBM® DB2® for z/OS® (DB2) environment, where a production table was dropped by a DB2 SYSADM. First, we discuss the implications of dropping a table. Then we cover the steps are necessary for recovery. Finally, we discuss tools you can use to simplify this task.    

Implications of dropping a table

Let’s take a close look at what happens when the DROP TABLE MYTBL statement is issued. Besides applications that start failing and losing all data stored in the dropped table, DB2 may also drop more objects. DB2 holds information about all objects in the DB2 catalog. When you issue a drop statement, DB2 starts deleting rows from the catalog that belongs to a dropped table and to any other object that depends on the deleted table. The table below summarizes affected objects:

Dropped table recovery

In general, one of the biggest problems when performing a DB2 recovery is that a DBA does not do it frequently. If a recovery was needed every day, it would indicate that there is a much more serious problem in the environment. Beyond that, dropped object recovery is nearly impossible using only the base utilities provided with DB2. We will now deal with what information is required, how to get it, what steps are necessary to recover from a dropped table situation and where the base utilities are insufficient.

It is highly recommended that before starting a dropped table recovery, create a recovery worksheet. It can be a simple table, such as below, in which you will gradually fill in useful information:

It could also be more complex and structured document. Whichever worksheet type you choose, it is a good starting point as you may repeat some steps more times or you may provide a report with this information afterwards. A worksheet helps keep important information available for whatever reason. It also helps keep you focused during a challenging and stressful experience.

The first required piece of information is the original DDL, with all ALTERs if possible. If you do not have the DDL and you cannot find it, a log analysis tool would be a great help in this situation as you would be able to generate the lost DDL from the log. Using just the base tools, you have very limited options.

You can use the most recent image copy of the DB2 catalog, load it into shadow catalog tables and identify the DDL from there. Not so bad if you have a simple table, but it would be very difficult and error-prone when the object structure is more complex. However still better than running DSN1LOGP and trying to reconstruct the DDL from its output.  

Once you have the DDL you can re-create the table and any indexes on the table.

The newly created object can have the same IDs as the old one, or not. It depends on what IDs are available at the time the object is re-created. You can use OBID in a CREATE TABLE statement if it does not identify an existing or previously used OBID from the database. Remark the object IDs for later use. For the new object, you can get this information from the catalog using the simple queries below:

• DBID, OBID for table:
  • SELECT NAME DBID, OBID FROM SYSIBM.SYSTABLES WHERE NAME='MYDB.MYTBL' AND CREATOR='USERID';
• DBID, PSID for table space :
  • SELECT NAME, DBID, PSID FROM SYSIBM.SYSTABLESPACE WHERE NAME='MYDB.MYTS' AND CREATOR='USERID';
    
    

To obtain IDs of the old object, locate the last full image copy. This might not be easy as all information from SYSCOPY and SYSLGRNGX was also deleted. You can run the DSN1PRNT job with FORMAT and NODATA parameters and get the IDs from its output. Consider the following example for the universal tablespace:

• DSN1PRNT control cards:
  //PRINTIT EXEC PGM=DSN1PRNT,
  // PARM='FULLCOPY,FORMAT,NODATA'
  //STEPLIB DD DSN=DB2.SDSNLOAD,DISP=SHR
  //SYSPRINT DD SYSOUT=*
  //SYSUT1 DD DSN=SSID.MYDB.MYTS.D2016209.T115036,
  // DISP=SHR
• Header page from DSN1PRNT output where HPGOBID field contains DBID and PSID, and HPGROID contains OBID
  • HEADER PAGE:
    PGCOMB='00'X PGLOGRBA='119598D5EF0A'X PGNUM='00000000'X PGFLAGS='38'X
    HPGOBID='19D30002'X HPGHPREF='000000B4'X HPGCATRL='00'X HPGREL='D6'X HPGZLD='00'X
    HPGCATV='00'X HPGTORBA='000000000000'X HPGTSTMP='20160315083037558950'X
    HPGSSNM='D10A' HPGFOID='0001'X HPGPGSZ='1000'X HPGSGSZ='0004'X HPGPARTN='0000'X
    HPGZ3PNO='000000'X HPGZNUMP='00'X HPGTBLC='0001'X HPGROID='0005'X

With all IDs and a full image copy you have all information needed to set-up a DSN1COPY job with the OBIDXLAT and RESET options to recover the table from the full image copy. You will need to stop the table space beforehand, to allow DSN1COPY to access the DB2 dataset. Below is an example of DSN1COPY job card statements where the SYSXLAT DD contains pairs of old and new object ids from the illustration above (6611 as DBID, 2 as PSID and 5 as OBID).

• //RECOVER  EXEC PGM=DSN1COPY,
  //         PARM='FULLCOPY,OBIDXLAT,RESET'
  //STEPLIB  DD DSN=DB2.SDSNLOAD,DISP=SHR                        
  //SYSPRINT DD SYSOUT=*                                              
  //SYSUT1   DD DSN=SSID.MYDB.MYTS.D2016209.T115036,DISP=SHR
  //SYSUT2   DD DSN= SSID.MYDB.MYTS.I0001.A001,DISP=SHR  
  //SYSXLAT  DD *
    6611,6611       
    2,2           
    5,5

Use DSN1COPY with the OBIDXLAT and RESET options to apply any incremental image copies. Alternatively to recovery with DSN1COPY, you can UNLOAD from the image copy and LOAD into a new table. Note that this cannot be done with the base utilities provided with DB2. It is recommended creating a new image copy when you successfully finish recovery from the old image copy(s).

Scared enough?

After this step, your object is recovered to a point in time of the last image copy. You are still missing all changes that have been done from the time of the last image copy until the table was dropped. A log analysis tool that can redo the changes from the DB2 log to recover also those missing changes can be a great help.

Once you are done also with this task you can start the table space again. If you have not made a new full image copy yet I would strongly recommend doing so. 

Once your SELECT statement to quickly check the table is fine, you are out of the woods, but not finished. There are still couple steps that may be required - rebuild the indexes, run a runstats, rebind invalidated packages, or re-creating the objects that are dependent on the recovered table.

Let’s summarize the steps required to perform dropped table recovery:

• Locate the original DDL of the dropped object and re-create it
• Gather the IDs of newly created objects from the DB2 catalog
• Find the most recent full image copy of the object
• Use the DSN1PRNT job and last full image copy to get old object IDs
• Recover the object from the last image copy with OBID translation
• Regenerate and apply all changes applied since the last image copy was taken until the object was dropped
• Rebuild the indexes
• REBIND packages
• Recreate other affected objects
• These steps may require slight modifications depending on your scenario. Table space types, whether the dropped table contained an LOB column or an XML column, and other variables.
  
  

It is easy to see how time-consuming and error prone the manual recovery of an accidentally dropped object can be. It often takes hours to collect the necessary information and set-up all the jobs. It is also near to impossible to recover from such a situation using the base utilities provided with DB2. Even with the help of more advanced utilities, a manual approach is complicated, even more so when you need to recover a database with many objects.

There are also many physical factors that affects a speed of a recovery process. The speed of the discs where your image copies and log data sets reside, how many archived logs will be needed to reconstruct data changes and many more. But there is one that enormously affects the speed of a log based recovery. It is the DATA CAPTURE parameter. If your table is defined with the DATA CAPTURE NONE, which is the default, DB2 does not register the whole row image in the log for update statements, only the updated columns are there. This is fine for reconstructing inserts and deletes, but a different story when it comes to updates. A complete before and after image of the row is required in order to reconstruct an appropriate SQL statement of any data change of the row from the log. You have only two options to get the complete information for an update statement. Locate the log record which represents an insert of that row and combine it with all subsequent updates, or find the information in the image copy. The log dataset which contains the insert record is usually deleted already. So in general, you require a full image copy to completely reconstruct the data changes from the log if your table was defined with DATA CAPTURE NONE. A significantly much more time is required in the recovery process to merge information from the log records and from the image copy. To speed up the recovery process, it is recommended to define your objects with DATA CAPTURE CHANGES. This option impose an increased size of log records for update statements, however it is usually not big. There are tools which can analyze your log and give an accurate estimate of data capture implications.

Don’t worry, there is a solution!

There are several vendor tools on the market that automate dropped object recovery to some degree. Let’s take a look how CA Log Analyzer can help you recover a dropped object in a few easy steps. It reduces the time needed for data collection and jobs preparation from hours to minutes.  

You need only three things to set-up dropped object recovery with CA Log Analyzer™ for DB2 for z/OS:

• Object Type
• Object Name
• Approximate time range when the object was dropped
  
  

That’s all, once you know what was dropped and when, fill this information on the Dropped Object Recovery main menu and CA Log Analyzer will generate and submit a job that:

• Generates UNDO DDL and runs generated SQL to recreate the object
• Determines DBID, PSID and OBID of the old and new objects
• Generates the recovery step and recovers the object from last image copy
• Generates REDO DML and runs generated SQL to apply data changes since the last image copy
• Rebuild indexes
• Recovers STATS from the catalog
  • or generates a step to run RUNSTATS if desired
• Generates REBIND step to rebind all invalidated packages.

In short, CA Log Analyzer automates the steps mentioned above required to recover an accidentally dropped object to a point in time when the object was dropped. If you are a geek and want to have a full control over the process, this option is also available. 

Watch this short video to see the recovery of a dropped database using CA Log Analyzer in action:

Conclusion

Keep in mind that no tool can save you if you are not well prepared for accidental situations and have prepared possible recovery scenarios, but if your backup and recovery procedures are correctly set and you have a tool such as CA Log Analyzer, you reduce the time of outage and loss of critical data to a minimum. As demonstrated in this article, a DBA using CA Log Analyzer can reduce the time needed to setup recovery jobs to recover a dropped object from hours to minutes.   

References

IDUG : Blogs : Recovering Accidentally Dropped Table 

CA Log Analyzer for DB2 for z/OS - CA Technologies 

CA Log Analyzer Dropped Object Recovery - YouTube 

https://docops.ca.com/ca-log-analyzer-db2/19/en 

http://publib.boulder.ibm.com/epubs/pdf/dsnagn04.pdf