Triton Db2 Geek

Confessions of a DB2 geek

IBM Gold Consultant Program and IBM Premier business Partner

Confession of the Month

March 2010- Unintended consequences

January 19th, 2010 - by

We’ve all done it.  You see a great new feature in DB2 and jump headlong into using it without fully appreciating all of the implications.  Here’s a case in point:

One of our clients is a large SAP customer running on DB2 for z/OS V9. With a huge number of subsystems to support and over 35,000 tables in each, taking traditional image copies would be a bit of a nightmare. Luckily, DB2 9 for z/OS contains the BACKUP/RESTORE SYSTEM function, which uses Flashcopy technology to rapidly backup and restore an entire system with a single utility. That means no messing with the traditional tablespace-level COPY utility, except when it’s needed for other reasons (such as when taking an inline image copy during an online REORG, to avoid putting the tablespace into COPY PENDING).


So far, so good. However, a couple of weeks ago the DBAs came in one morning to find a lot of SAP users complaining about database errors, and dozens of tablespaces unable to be updated due to being in a COPY PENDING state. What the heck happened? After some investigation, it seems that a routine MODIFY RECOVERY job that ran the night before had removed the last image copies for these tablespaces from SYSIBM.SYSCOPY (ones taken during some REORGs a few months ago). Despite the fact that several perfectly good system-level backups existed that included those tablespaces, DB2 still decided to put them in COPY PENDING mode once the last image copy was removed!


So, don’t assume that BACKUP SYSTEM causes objects to be treated in the same way as traditional COPY, despite the fact that V9 allows you to recover individual tablespaces from a system copy. As workaround, we used yet another new V9 feature – the ability to run MODIFY RECOVERY with the RETAIN option instead of the DELETE option we had been using. RETAIN allows you to keep a certain number of image copies in SYSCOPY (regardless of their age) and deletes anything else, thereby ensuring you always keep at least one or two entries in SYSCOPY and avoid the COPY PENDING issue.


There are plenty more gotchas to talk about with BACKUP/RESTORE system, but we’ll leave those for another month……



Tag Archives