Backup Requirements

Backup Frequency
The main concern in implementing backup procedures is to ensure fast recovery. Db2 allows you to use image copies to recover the data to current when the physical datasets are damaged or to recover the data to a point in time when the data has been corrupted. RECOVER uses the image copy to replace the current data and then uses the Db2 log to apply all the updates made since the image copy to the desired point in time. If the table had many changes, the log apply might take some time. For this reason, it is a good idea to take image copies frequently enough to speed up the recovery time. Backup frequency should be adjusted based on the table updates rate. Tables with high rate of updates might need a backup every few hours. Tables with low rate of updates might need a backup every few days. Db2 Real Time Statistics allows you to identify the rate of changes on each tablespace since the last image copy.

The log retention also affects the speed of recovery. Reading log records from the active logs is much faster than reading the archive logs because the active logs have better buffering. Archive logs are also often allocated on tapes that might require waiting for a mount. If the log records have been offloaded to archive logs and are no longer recorded in the active logs, recovery might take longer. It is a good idea to take a fresh image copy when the updates recorded in the logs after the last image copy are no longer in the active logs. This way, RECOVER can access active logs only. Real Time Statistics allows you to identify the first log record that was written after the last image copy. You can then read the BSDS to identify if this log record is still recorded in the active logs.

Applications might also create the need to backup the data before it is manipulated by the application logic. To create a common point of consistency for all related tables, it is required to backup and QUIESCE all the tables at the same time.

Periodical Backups
Backup frequency and retention are prescribed by business requirements and regulations. The business might have a requirement to retain the backups for at least one business cycle because from a business point of view it does not make sense to go back in time further. However, regulations might require to retain the backups for months and even years to allow investigating the data in case of a fraud. For these reasons, some shops need to run different periodical backup cycles such as daily backups or monthly backups. With each periodical backup, it might be required to retain the image copy datasets for different periods or use different allocation options. You can control how long you want to keep your backups by using JCL retention period RETPD, expiration date EXPDT, SMS data class or a template that uses these options in dynamic allocation. Daily and monthly cycle jobs can have different values for these options or use different templates.

The periodical backup cycle is often selected using fixed days or dates, such as daily backup on weekdays, weekly backup on weekends and monthly backup on the beginning of each month. In some cases, the periodical backups must be taken on specific business days, for example, after monthly processing or after year-end processing. These business events do not always have a predefined schedule or predicted completion time. Full backup must be scheduled immediately after the business event.

Daily backups are often based on rate of changes and may not include all tables. Tables that have not been updated since the last image copy can be excluded to avoid unnecessary resource consumption. However, a periodical backup designed to retain data for extended periods, must include all tables unconditionally, even if these tables had no changes since the last backup. This way, it should be possible to restore all the tables aside to see the full business case.

Incremental Copies
Incremental copies include only the pages that have been changed since the last backup. It makes sense to use incremental copies if less than 50 percent of the pages have been changed. The COPY utility identifies the updated pages using the space map that include an update indication for each page. Incremental copies run much faster than full copies because there is no need to scan the entire tablespace. In addition, the incremental image copy dataset is much smaller than a full image copy and require less space. Real Time Statistics can be used to determine if full or incremental copy is required based on the rate of changes.

The COPY utility CHANGELIMIT option can be used to automatically determine at runtime to use full or incremental image copy. COPY then reads the space map and switches to full copy if the rate of changed pages exceeds the specified limit. However, it is better to use Real Time Statistics and find out before execution which objects require full copy and which ones require incremental. CHANGELIMIT can also decide to take no image copy. If no image copy is taken, the image copy datasets are allocated for no reason and are left empty. Real Time Statistics allows you to exclude tables that had no changes and avoid creating empty datasets.

The RECOVER utility first uses a full image copy and then applies all the following incremental image copies so a full image copy is still required. Full image copies should be forced from time to time, regardless of the rate of changes. This can be achieved by periodical backups, for example a weekly full copy. Real Time Statistics tells you only when was the last image copy but without any indication when was the last full image copy. To identify when was the last full image copy you must select the information from SYSCOPY or use the REPORT RECOVERY utility.

MERGECOPY utility can be used to merge the last full copy with all the recent incremental copies and create a new full image copy. Merging incremental copies can speed up recovery time because the RECOVER utility can then use the most recent full image copy. It is a good practice to automate MERGECOPY and identify automatically which objects have pending incremental copies waiting to be merged.

LISTDEF is often used to COPY all objects using generic notations. Db2 identifies automatically at run time the objects included in processing. If for example, the entire database is included, new tables are automatically picked up. This way there is no need to generate and refresh the JCL for every newly created table. The COPY statement is using the LIST to take an image copy for all the objects included by LISTDEF. However, all the included objects share the same copy option, full or incremental. For this reason, it might be required to generate 2 different lists of objects, one including all objects for full copy and one including all objects for incremental copy. These list can no longer use generic notations because each tablespace might have a different change rate since the last copy. Therefore, the tablespaces must be included in each list explicitly. The list must be refreshed prior to every COPY to accurately reflect the current change rate.

Relating Datasets
Image copies are recorded in SYSCOPY. The recording describes the nature of the image copy, full or incremental, share level and the log point. This information is required for recovery. However, you will not be able to find this description, if the recording was removed by the MODIFY RECOVERY utility, if the object was dropped or if the image copy is associated with a different subsystem. SYSCOPY also does not include information on unload datasets created by the UNLOAD utility and discard datasets created by the REORG DISCARD utility. RECOVER does not allow using image copies that are not recorded in SYSCOPY, but you can use these image copies with DSN1COPY to copy the data from subsystem to subsystem or to recover a dropped object. It is a good idea to use a naming convention that allows relating datasets to objects even if the datasets are not recorded in SYSCOPY.

Image copy dataset names are limited in length, like any other dataset, to a maximum of 44 characters. Choose a naming convention that will allow you to associate the dataset with a specific object and partition. For this reason you need to include the full 8 characters database name, the full 8 characters space name, and 4 digits partition number. In addition use a naming convention that will allow you to identify the type of image copy (full, incremental or inline) or the SHRLEVEL (CHANGE or REFERENCE). To avoid duplicates you may need to include data/time and site (local or recovery site). Similar naming convention could be helpful with unload or discard datasets, especially when these datasets are not recorded anywhere. This way it should be easy to relate datasets to objects and generate recovery jobs when needed.

The MODIFY RECOVERY utility cleans up old recording of events from SYSCOPY and SYSLGRNX. The recommendation is to avoid keeping this recording longer than you keep your log records. You cannot use the recover utility to recover to a point in time if the log records are no longer available. Alternatively, if you want to recover to an image copy, you can always use the DSN1COPY utility. Therefore, it makes sense to cleanup SYSCOPY and SYSLGRNX from obsolete information to improve access time and operability of these tablespaces. It is not advisable to include MODIFY RECOVERY execution in backup jobs because the scan of SYSCOPY may take a long time and can delay the completion of backup jobs. It is a good idea to retain a specific number of image copies per object. In addition you can also specify the traditional number of days to retain image copies. [/slider]