Robert's Blog


Wednesday, October 7, 2009

Thoughts on DB2 for z/OS BACKUP SYSTEM and RESTORE SYSTEM

Recently I worked with an organization that is planning an implementation of SAP's ERP application, with the associated database to be managed by DB2 9 for z/OS. This impending SAP installation was a major impetus for getting DB2 9 in-house, thanks largely to the significant enhancements delivered in that release for the BACKUP SYSTEM and RESTORE SYSTEM utilities. In this entry, I'll provide a brief overview of BACKUP SYSTEM and RESTORE SYSTEM, describe new features of these utilities in a DB2 9 environment, and pass on some related information of the "news you can use" variety.

BACKUP SYSTEM and RESTORE SYSTEM are prime examples of user-driven advances with respect to DB2 functionality. Near the end of the 1990s, several large companies using SAP with DB2 for z/OS met with IBM and SAP to press for a solution to a recovery-preparedness challenge. For these organizations, the traditional means of DB2 data backup - the COPY utility - was not satisfactory, owing to the fact that an SAP-DB2 database could contain tens of thousands of objects. System-wide, disk volume-level backups could be efficiently created using the FlashCopy technology of IBM disk subsystems (other disk storage vendors such as EMC and HDS offer a similar capability), but this approach had two significant drawbacks: 1) it was an outside-of-DB2 process, and 2) recovery of a database (using a system-wide volume-level backup) to a consistent state depended on the existence of system-wide quiesce points established via the DB2 commands -SET LOG SUSPEND and -SET LOG RESUME (the former of these commands was quite disruptive in a high-volume OLTP application environment). The SAP- and DB2-using companies wanted a system-wide backup solution that would take advantage of FlashCopy (or equivalent) technology, be executable through DB2, and allow for recovery with consistency to a user-specified point in time.

IBM's response to this request was delivered with DB2 for z/OS Version 8, in the form of the aforementioned BACKUP SYSTEM and RESTORE system utilities. One of the big advantages of this new solution was the elimination of the formerly required system-wide quiesce points for recovery of a database to a consistent state: with BACKUP SYSTEM and RESTORE SYSTEM, the database could be recovered to any user-specified prior point in time (prior to currency - more on that momentarily), as DB2 would use information in the recovery log to back out any data-changing units of work that were in-flight at the designated point in time (underscoring the importance of this being a DB2-managed backup and recovery process). DB2 9 delivered some important enhancements to the functionality of these utilities, as described below:
  • Object-level recovery from a system-level backup - With DB2 Version 8, it was only possible to perform a system-level recovery using a system-level backup. In a DB2 9 environment, the RECOVER utility can be used to recover an individual object (e.g., a tablespace) or a set of objects using a system-level backup made with the BACKUP SYSTEM utility.
  • Recover to currency using RESTORE SYSTEM - Before DB2 9, recovery using RESTORE SYSTEM had to be to a point in time prior to the end of the log. Now, a recovery to currency can be performed by specifying SYSPITR=FFFFFFFFFFFF on the control statement of the DSNJU003 utility (change log inventory) that is executed prior to running RESTORE SYSTEM.
  • Support for incremental FlashCopy - Initially, FlashCopy - as invoked through the BACKUP SYSTEM utility - will create a full copy of the source volumes on the designated target volumes. Logically speaking, this copy operation is almost instantaneous. The physical copy operation takes more time to complete. If data is written to a source volume while the physical copy on the target is being made, the storage subsystem will check to see if the to-be-changed source-volume track has been copied to the target. If it hasn't, the source track will be copied to the target volume before being changed by the pending write operation. After the initial full copy has been completed (in the physical sense), subsequent copies can be incremental, with only the tracks changed since the last backup being copied from source to target. Thus, the workload on the I/O subsystem is reduced.
  • Support for backup to tape - With DB2 Version 8, getting a disk copy generated via BACKUP SYSTEM to tape was a manual process. With DB2 9, BACKUP SYSTEM can be used to copy a source backup to tape as it's being written to the target volumes. Alternatively, a backup can be written to tape some time after the physical copy to the target volumes has completed.
Now, the BACKUP SYSTEM and RESTORE SYSTEM utilities are very nice pieces of DB2 functionality, but there are some things you should think about before using them. First of all, even though DB2 9 enables object-level recovery from a system-level backup, you SHOULD NOT expect BACKUP SYSTEM to eliminate the need to use the COPY utility - this for two reasons:
  1. You'll need to use COPY to establish a new "recovery base" for an object following a LOAD REPLACE (or a LOAD RESUME with LOG NO) or an offline REORG with LOG NO (an inline image copy is required if you run an online REORG job).
  2. Object-level recovery from a system-level backup is currently not possible for a data set that has been moved since the system-level backup was created (as would be the case for most REORG and LOAD REPLACE operations). This restriction will be lifted in the near future, but it's there today.
Second, the use of BACKUP SYSTEM and RESTORE SYSTEM requires that the active log and BSDS data sets have an ICF catalog that is separate from the one used for the DB2 catalog and directory and user/application data sets. In other words, it's not sufficient that these two categories of DB2 data sets (active log/BSDS and catalog/directory/application) use different aliases that point to the same ICF catalog. The ICF catalogs themselves have to be different (and the two categories of DB2 data sets have to be in different SMS storage groups). If your BSDS and active log data sets are currently in the same ICF catalog as the DB2 catalog and directory and application objects (i.e., application tablespaces and indexes), you'll need to separate them before using BACKUP SYSTEM and RESTORE SYSTEM. Moving the BSDS and active log data sets to a separate ICF catalog basically involves doing the following:
  • Define the new ICF catalog and the new high-level qualifier for the BSDS/active logs.
  • Define the new BSDS and the active log data sets in it.
  • WHILE DB2 IS DOWN, copy the active log and BSDS data sets to the new data sets (the ones in the separate ICF catalog). The best way to do this is probably to use DFDSS to copy the datasets with the RENAMEU parm specified to change the high-level qualifier.
  • Then use the DB2 change log inventory utility to fix up the BSDS with the correct log ranges for the current active log (the non-reusable one).
  • For the rest of the log data sets, you can use the change log inventory utility to delete the old ones (with the old high-level qualifier) from the BSDS and and add the new ones (using the NEWLOG statement) without specifying log ranges. This saves a bunch of time and effort, and you needn't worry about DB2 being able to find log data sets with records in a certain range - as long as the other logs have been archived, DB2 can find the ranges in the archive log.
  • Note: you do NOT have to use the change log inventory utility with the NEWCAT statement to change the VCAT name in the BSDS, because that VCAT name is the one used for the catalog and directory, and the one you're changing is for the BSDS and active log data sets.
  • Then start DB2.
Finally, a note on the frequency of BACKUP SYSTEM execution. Organizations that are using the BACKUP SYSTEM utility today are generally running it twice daily for their production systems. IBM recommends keeping at least two system-level backups on disk, but not all organizations can afford to allocate the amount of disk resources required for this. You might end up keeping the most recent backup on disk, with previous backups going to tape.

I encourage you to check out BACKUP SYSTEM and RESTORE SYSTEM. If you have a whole lot of objects in your DB2 database, these utilities could make your backup and recovery processes a whole lot simpler.

14 Comments:

Anonymous Anonymous said...

Robert,
Another good piece. Thanks for all the helpful suggestions over the years. I have been looking forward to using this when we finally get to V9. Though I have a question, when looking through the V9 Utility Manual, there is no syntax for restoring a single tablespace. Any idea where to find that info? Thanks.
Dave Nance

October 9, 2009 at 6:14 AM  
Blogger Robert Catterall said...

Thanks for the positive feedback, Dave.

When you say that "there is no syntax for restoring a single tablespace" to be found in the DB2 9 for z/OS Utility Guide, are you referring to the RESTORE SYSTEM utility? To recover a single tablespace from a system-level backup, you'd use the RECOVER utility, not RESTORE SYSTEM, and there's nothing special that you need to specify in RECOVER utility syntax. If you have the ZPARM parameter SYSTEM_LEVEL_BACKUPS set to YES (the default is NO), and you submit a RECOVER job to recover a tablespace, DB2 will use for a recovery base the most recent system-level backup, image copy, or concurrent copy, based on the specified or implied recovery point (to currency or to some prior point in time).

Does this answer you question?

October 9, 2009 at 6:13 PM  
Anonymous Anonymous said...

Hi Robert, (another Dave here).

I think I'm asking for clarification on Dave Nance's question.
There paragraph "Object-level recovery from a system-level backup" say's...

In a DB2 9 environment, the RECOVER utility can be used to recover a tablespace with the BACKUP SYSTEM utility.

To me this implies that you can use a RECOVER (presumably RECOVER TABLESPACE) to recover a tablespace from a BACKUP SYSTEM ? or are YOU refering to an FRRECOV type recover ?

Thanks
Dave DeWinkle

October 28, 2009 at 9:47 PM  
Blogger Robert Catterall said...

Hello, Other Dave.

What I meant to convey is that the DB2 9 for z/OS RECOVER utility can restore an object (such as a tablespace) backed up via the DB2 utility BACKUP SYSTEM.

Does that answer your question? If not, let me know.

Robert

November 2, 2009 at 7:40 PM  
Anonymous Anonymous said...

Thanks Again, Robert the blog was helpful, and my system programers appreciate the advise, Thanks a bunch

December 1, 2009 at 12:31 PM  
Blogger Robert Catterall said...

I'm pleased to know that the information was useful to you.

Robert

December 2, 2009 at 5:58 AM  
Anonymous Anonymous said...

Hi Robert,

Your analysis and subjective knowledge seems to be very good.

IBM is really progressing.

December 18, 2009 at 2:53 AM  
Blogger Robert Catterall said...

Thanks for the kind words.

I agree that IBM is progressing well on the DB2 availability and manageability fronts. It will be interesting to see, especially in DB2 for z/OS V9 environments, the extent to which organizations supplant COPY utility usage with BACKUP SYSTEM (you have to use COPY in some cases, but BACKUP SYSTEM could become the prevalent means of backing up databases at many sites).

December 18, 2009 at 6:38 AM  
Anonymous Anonymous said...

Robert, very informative article. I just wanted to know if Backup System can be used for the DB2 system catalog/directories as well. I am concerned about the worst case where we might loose the DB2 system catalog and how that would be recovered?

March 22, 2010 at 8:12 AM  
Blogger Robert Catterall said...

Glad you liked the article.

Yes, BACKUP SYSTEM can be used for the DB2 catalog and directory, as well as for application objects. An excellent reference related to this topic is a new IBM "red book" titled "DB2 9 for z/OS: Using the Utilities Suite." Chapter 13 in this publication is dedicated to the BACKUP SYSTEM and RESTORE SYSTEM utilities. You can download a copy of the redbook from IBM's Web site, at this url:

http://www.redbooks.ibm.com/abstracts/sg246289.html?Open

March 22, 2010 at 9:43 AM  
Anonymous Anonymous said...

Thank you Robert for this good article. I'm preparing for separating the BSDS and active log into new ICF catalog and your article is clarifying the technique.
I'm a little bit confused about the following sentence though,

" Then use the DB2 change log inventory utility to fix up the BSDS with the correct log ranges for the current active log (the non-reusable one)."

So my question is. Why do I have to change the log ranges
in the not reusable active log and to what value?

August 3, 2010 at 9:12 AM  
Blogger Robert Catterall said...

Sorry about the delay in responding.

You do step 4 (the one about which you're asking) in the "move the active log and BSDS data sets to a separate ICF catalog" procedure because after copying the contents of the old BSDS (the one using the ICF catalog from which you're removing the BSDS and active log data sets) into the new BSDS (in the new ICF catalog, and with a new HLQ), you'll run the change log inventory utility to delete the old current active log data set (the one in the "come-from" ICF catalog) and add to the BSDS the new current active log data set (in the new ICF catalog, with the new HLQ). In deleting the old one from the BSDS, you got rid of the associated log range information. When you add the new current active log data set to the new BSDS, you'll be missing the log range information associated with that active log data set. You need to specify this, and you need it to be correct (i.e., the log range information associated with the old current active log data set). You do NOT have to specify log range information for the other active log data sets (assuming they've been archived). That's why the current active log data set is singled out in step 4.

As for the values to use for that log range, they'd be the ones for the old current active log data set. You could get this information from an execution of the print log map utility (aka DSNJU004), run for the old BSDS after stopping the DB2 subsystem (as pointed out in step 3 of the procedure, you'd have DB2 down when moving the log and BSDS data sets to the new ICF catalog).

Hope this clarifies things.

August 5, 2010 at 3:34 PM  
Anonymous Anonymous said...

Hi Robert
With DB2 V10 Z/OS RELEASED
Is there any way to do Pre-process , say without doing backup , something like
>> Is there any way to know time it will take to do Backup system ( full/incremental) or similarly Restore Backup - particularly for VLDB and simulate performance improvement for backup opoeration

>> Estimated CPU utilization
]
>> Total size of the Database to be backup



September 28, 2012 at 11:37 PM  
Blogger Robert Catterall said...

The only performance data I've seen on BACKUP SYSTEM and RESTORE SYSTEM is in an IBM "red book" titled "DB2 UDB for z/OS Version 8 Performance Topics" (BACKUP SYSTEM and RESTORE SYSTEM were introduced with DB2 for z/OS V8). This document is available online at http://www.redbooks.ibm.com/abstracts/sg246465.html?Open. Check out section 5.1, "System level point-in-time recovery." You'll see elapsed times for BACKUP and RESTORE tests involving differing numbers of volumes. The elapsed times recorded were quite low, and would likely be lower today given advances in I/O channel, disk subsystem, System z server, and DB2 for z/OS performance during the 7 years since the tests described in the red book were conducted. CPU times for BACKUP and RESTORE are not provided, but they would probably be quite small since the bulk of the cycles consumed in BACKUP SYSTEM and RESTORE SYSTEM execution are at the disk subsystem level (speaking of FlashCopy exploitation), versus the System z server level.

October 1, 2012 at 6:36 AM  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home