Database Backups for Peace of Mind: Part 1

DB2 database backup and recovery serves as an insurance policy for organizational data assets

“Backup equals insurance” is a motto that should be prominently displayed on the office wall of every database administrator (DBA). A backup copy of data is not made just to have it, or to check off an item on a checklist, or because it is written somewhere that the backup task should be performed. A backup helps ensure that organizations can recover when disaster strikes or an unexpected loss of data or downtime scenario occurs.

As with any insurance coverage, there are fees to pay for the policy. Insurance for homes, cars, and health comes in many flavors with a variety of coverage options and exposure levels. Procured policies are those that are appropriate to cover specific assets or secure the level of help that is necessary in time of need and at a cost deemed worthy of the selected coverage. And in the same way that an insured individual or organization never wants to have to file an insurance claim to recoup a loss, DBAs, IT professionals, and line-of-business users hope they never have to use backup data to minimize downtime.

When it comes to recovery, there are two key considerations for organizations: how quickly work can resume after restoration, and how many hours of work are they ready to lose while handling the recovery. The faster that organizations want to be back in business, the more costly is the recovery infrastructure.

Backing up data means having a copy. The copy is preferably kept separately from where the source data resides. The backup data of course needs to be readily available in case of a situation that causes a loss of part or all the data. Part 1 of this series surveys the options for copying data in IBM® DB2® database deployments, and Part 2 will focus on recovery, particularly how the backup process impacts data recovery.


Executing copies in DB2

There are several options available to create a backup of data in a DB2 data management deployment:

  • DB2 COPY utility
  • DB2 copy created during a REORG or LOAD operation
  • DB2 BACKUP SYSTEM utility
  • DB2 UNLOAD utility
  • DB2 DSN1COPY utility
  • Other options, such as those provided by independent software vendors (ISVs)

When using any of the first three methods, DB2 registers the copy in its catalog—the SYSIBM.SYSCOPY table. That information can be used later in a recovery process. When making a copy of the data, the DBA defines the type of storage device where the copy will exist. The COPY utility can make a backup of either data—the table space—or index—index space. The copy process can also be part of a LOAD or REORG operation such as an in-line copy. The BACKUP SYSTEM process uses data facility data set services (DFDSS) behind the scenes to do the actual backup.

The UNLOAD utility puts the data into a sequential file that can be transferred to other systems to be loaded later on. This file provides a picture of how the data looked at a particular point in time.

The DSN1COPY utility copies the file blocks as is or with an option to translate internal IDs as needed. The UNLOAD and DSN1COPY utilities are not registered in SYSIBM.SYSCOPY. Other methods include ISV-provided tools that may copy the data outside of DB2 control and then register the facts in SYSIBM.SYSCOPY to simulate IBM COPY, or using any other method to copy files.

Where the copy resides

The copy target is a file created on either a direct-access storage device (DASD), physical tape, virtual tape library (VTL), or virtual tape system (VTS). In VTLs, tapes are emulated on low-cost DASD; VTSs include a front-end DASD and an optional back-end tape robot. VTS keeps as much data as it can on the front-end DASD and writes it to tapes according to specified rules. In certain configurations, a VTS tends to function more like a DASD versus other configurations in which it behaves more like a physical tape backup device. For the IBM DB2 for z/OS® database I/O subsystem, it performs as a tape backup. Data is written to the front-end DASD of the VTS, and moved later to a tape cartridge—although the z/OS I/O routines see it as another serial tape device.

This characteristic for VTS is important because read/write operations on a tape are serialized; the second file on a tape cannot be accessed without passing through the first one. DASD, on the other hand, allows for accessing data without having to work with any preordained order. DASD may work in parallel; tape cannot. If many files are stacked on the same tape, access to each can occur only one at a time. Files copied from tape to a DASD can be accessed in parallel afterward. Fast recovery requires the capability to recover data in parallel because the elapsed time is shorter than when recovery occurs serially.

When using a VTS to back up DB2 databases, DBAs—or more precisely, storage administrators—can define virtual tape on the front-end DASD part of the VTS. As files are copied to the VTS, they can be written to either a virtual tape as stacked files, one after the other on the same virtual tape, or in parallel, with each file written to a separate virtual file. The first method uses fewer virtual tapes than the second, but requires serial writes and, later, serial reads. The second method is much faster than the first but uses many virtual tapes.

The VTS capacity for the number and size of each virtual tape is an important fact to take into consideration. Each virtual tape file can contain data up to its defined size, so if data is written to one small data file on a big tape file, there will be a lot of unused space. To use more of the virtual tape, administrators need to stack files on each virtual tape, which requires serialization during writing while making a backup and during reading while performing recovery.

DASD hardware replication is an optional feature that allows changes to be written to one DASD, which are automatically replicated to another DASD, even over long distances. This feature enables making an exact copy of the DASD and storing it in another location, which can be used if the contents of the first DASD become damaged. Using DASD hardware features to replicate itself is significantly faster than using the operating system or DB2 to replicate devices.

Tables and indices

DB2 enables DBAs to back up both data and indices. However, because indices can be rebuilt from the data, many DBAs prefer not to back them up in favor of rebuilding them, if needed, during a recovery operation. For some tables, this recovery method works just fine; the time to rebuild the indices may be shorter than implementing recovery of backed-up indices. For other tables, recovering indices from backup along with the tables may be preferable, particularly when recovering files in parallel is more efficient for the whole process than it is when rebuilding the indices.

Table size dictates the time it will take to scan through the table to rebuild the index or indices, and recovering both data and indices also requires handling the copy process’s different timing issues. File sizes for the data and each of the indices are usually different. Their elapsed copy time is also different, and the respective copy process for each will be completed at different times, even if the process for each is started at the same time. Recovering all those files needs to be completed to a point in time that equals or is greater than the time at which copying the last file has completed.

Beginning with DB2 Version 10, the IBM FlashCopy® function was added. It offers an even faster alternative for data backups, except for backing up small tables because the setup time for the operation can be longer than a regular copy time. FlashCopy uses a combination of z/OS DFDSS software and DASD hardware—FlashCopy or equivalent, depending on the DASD manufacturer—to implement the copy and recovery process. Moreover, this method provides a FLASHCOPY CONSISTENT option that enables creating a copy that is consistent on data and index pages at the transaction boundary, compared to a regular copy operation that can be consistent based on information residing on log pages—the quiesce point.

A copy can be created either when no one else is working—a SHRLEVEL REFERENCE—or while daily activity continues—a SHRLEVEL CHANGE. Being able to make a SHRLEVEL REFERENCE copy is a privilege that rarely exists nowadays because the demand for 24/7 operations requires using SHRLEVEL CHANGE, which involves using DB2 logs for any recovery scenario. To recover data when using SHRLEVEL CHANGE, both the backup files and the appropriate log files—those from the time the backup was started to the point in time to recover to—are required. The log files should be as easy to access as the backup files; otherwise, waiting for the log files to be made available may bog down the recovery process.

For large tables that are changed sporadically, DBAs may want to use the INCREMENTAL option. This option forces the operation to copy only pages that have changed. An incremental backup can save time but may introduce some challenges during recovery.

Part 2 of this series takes a deeper dive into the options for recovering backed up data in DB2 database environments. Please share any thoughts or questions in the comments.