Thought Leadership: DB2 Recovery on Steroids — Flex Your Recovery Muscles

By Paul Pendle

Every now and then, a DBA is needed to execute a recovery action on a database. The action can be as simple as restoring a table or as vast — though not necessarily complex — as restoring a whole system. Performing a recovery on a component of a DB2 for z/OS system can be a nightmare for a DBA, which requires determining what went wrong and why it went wrong, as well as selecting the quickest and most accurate path to remediation. While doing the recovery, it is most likely the application, database or system will be down until the situation is rectified. For some companies, this downtime can directly result in lost revenue (i.e., online sales websites and credit card processing systems). There can be a lot of pressure for the DBA to deliver a fix in the shortest time period possible.

Complicating the situation, the recovery action is often required in the middle of the night, typically when a batch process has gone awry. With all of this analysis and pressure upon the DBA when he is not performing at his best due to lack of sleep, it can be a recipe for disaster.

One of the biggest problems with performing a DB2 recovery action is that the DBA does not do it frequently. If a recovery action is needed every day, I would suggest that there is a bigger problem in the environment that needs to be addressed. Trying to figure out the syntax of the RECOVER utility by scrutinizing the railroad diagrams is challenging enough and takes time. Then you must determine the objects, or set of objects, that need to be recovered. That is not a simple task unless you have done some preparation ahead of time.

Sometimes recovery of a single table will create problems of referential integrity. Those RI rules, enforced by the database, are relatively easy to resolve. But when RI is enforced at the application, the referential sets, those objects that need to be recovered together, may not be clearly exposed to the DBA and may be the source of integrity issues if ignored.

You could also be hit with some (almost) unrecoverable situations, such as a rogue transaction that needs to be undone and just that transaction when all other updates to the table(s) are valid. How do you do that? You can’t simply recover the table to a prior point in time, because you would be backing out valid transactions that have already been committed. Using the base utilities provided with DB2 for z/OS (or in fact most databases), this type of surgical transaction recovery is impossible.

There are other recovery situations that may be even more challenging, such as those in which there has been a REORG, a not-logged activity took place or an ALTER has been performed that changes the table structure and therefore table version. This last one creates a complicated recovery situation. Usually, this kind of recovery warrants dropping the object and re-creating it as it was before the ALTER. But this means everything related to that table will be dropped, including indexes, views, triggers, security, etc. How about tackling that when you have not had enough sleep?

What about recovering from accidentally dropped objects? Granted, this type of recovery is unlikely to happen in the middle of the night, but it does provide a challenging recovery situation for a DBA. When the object is dropped, DB2 faithfully removes everything about that object from the DB2 Catalog and deletes the associated datasets. It also cleans up every associated entity that relates to this object, listed below:



Stored Procedure

Contraint MQT Synonym
Clone Package SYSCOPY Entries
Data Type Plan Table Space
Database Procedure Trigger
Function Role UDF
Global Variable Sequence View
Grants Sequence  

Recovery is extremely difficult with the base utilities, since SYSCOPY entries are either deleted (in the case of a dropped table space) or not referenceable with the recovery utility. How can you recover a table that is not defined in the DB2 catalog? Do you have all the DDL and DCL ready to execute to recreate the table and associated components? Even with that in hand, you need to locate an image copy that you can DSN1COPY into the space with the correct DBID, PSID and OBID. This is certainly not for the faint of heart.

These are just a few examples of complicated situations that can occur while performing database recovery, not just restricted to DB2, but with many different relational databases. At least with DB2 for z/OS there is a solution: IBM DB2 Recovery Expert for z/OS. If down-time and application unavailability can really hurt your business, you may want to consider procuring this tool. It can help solve all the situations mentioned above and more by automatically generating the recovery JCL and dynamically allocating the recovery assets that you need to perform a specific type of recovery. Interrogation of the DB2 LOG, the DB2 Catalog, the ICF Catalog helps the “expert system” inside the tool determine the optimal path for recovery of a given object or set of objects. The result is that the DBA can go to sleep knowing that recovery nightmares are largely put to bed.

Learn more about recovery solutions at Pendle’s presentation:
“DB2 Recovery on Steroids! Develop your Recovery Muscles”
Date: Tuesday, March 1
Time: 3:15-4:15 p.m.

Paul Pendle, managing director of R&D at Rocket Software, Inc., is a 35-year veteran in the IT industry working on both mainframe and distributed platforms. He has worked on MVS, VSE and VM as well as AIX, HPUX and Solaris. Pendle’s focus over his career has been on databases and their performance, and in his latest position at Rocket Software, he has managed the DB2 tools business for backup and recovery. Pendle is an IBM information Champion, a well-known speaker at conferences and published author.

Recent Stories
SHARE: GAO’s Mainframe Risk Claims Debunked

Lost in Translation

Glenn Anderson: How Improv Can Improve a Career in Mainframe