Reduce database recovery time by using DB2 Advanced Recovery Solution for Linux, UNIX, and Windows

Learn how backup and recovery tooling can help minimize recovery times

From an IT perspective, manual backup and recovery processes are tedious, error prone, and time consuming, therefore, they are not frequently implemented. As the volume of data increases, though, it becomes more difficult to back up and recover without longer downtime that impacts production applications. To help resolve these challenges in database backup, recovery, and data unload, IBM DB2 Advanced Recovery Solution for DB2 for Linux, UNIX, and Windows provides three distinct products: IBM DB2 Merge Backup, IBM DB2 Recovery Expert, and IBM InfoSphere Optim High Performance Unload.

Share:

Anson Kokkat (ansonk@ca.ibm.com), Product Manager, IBM

Photo of Anson KokkatAnson Kokkat works at IBM as a Product Manager in the Optim Database Tools Group. Anson's professional career spans more than 12 years during which time he worked on application development technologies related to data servers. He has an extensive background working as a product manager for the IBM data modeling tool InfoSphere Data Architect. Now Anson concentrates on creating a marketplace for Database Backup and Recovery Tooling Solutions. He has written many articles and whitepapers related to his expertise, and he continues to work with sales, marketing, development, and especially customers out in the field.



Ivan Nikittin (ifnikitt@us.ibm.com), Information Developer, IBM

Photo of Ivan NikittinIvan Nikittin is an information development lead for IBM DB2 for Linux, UNIX, and Windows tools at IBM Silicon Valley Lab, San Jose, California. He has been documenting Optim, InfoSphere, and DB2 tools for over 4 years.



23 June 2011

Also available in Chinese

Introduction

Backup and recovery resources are critical for any business that wants to minimize database downtime and its associated costs, to meet service level agreements, and to increase customer satisfaction, revenue, and corporation reputations. Very few organizations have perfect or near-perfect datacenter uptimes. Although disruptions are few, they are very costly when they do happen. Most organizations end up losing significant money because of unplanned database downtime. IBM DB2 Advanced Recovery Solution for Linux, UNIX and Windows provides three distinct products to mitigate database downtime:

These tools have the capability to reduce time to back up and recover by aligning backup strategies with outage-related service level agreements (SLAs) to provide faster recovery.

DB2 Recovery Expert helps you recover faster and with greater granularity than what is offered with regular database utilities. The tool also enables you to monitor and audit activity in sensitive DB2 tables. DBAs can view data changes by dates, users, tables, and other criteria. Administrators can institute tighter controls over the data to ensure that DBAs can quickly restore or correct erroneous data using fewer resources and can reduce data outages.

DB2 Merge Backup reduces CPU consumption by virtually eliminating the need to ever take a full backup again. You will only need to take a backup after making database structural changes and merging these with a full DB2 backup. You can do all of this processing off-line without any impact to the processing on the database server. This gives you a more up-to-date and efficient backup strategy that reduces application downtime and aligns with your SLAs.

InfoSphere Optim High Performance Unload enables you to reduce data extraction time by up to 16 times the time needed for the DB2 EXPORT utility. With InfoSphere Optim High Performance Unload, you can extract from a backup, including backups created with DB2 Merge Backup. You can also use InfoSphere Optim High Performance Unload to repartition or move data from one system to another, which significantly reduces the outage time for end users.

Together, these tools give you a powerful recovery solution that enables smarter, simpler, cheaper, and faster database recovery.

Using DB2 Recovery Expert

DB2 Recovery Expert has two main purposes:

  • It provides granular recovery beyond traditional database recovery.
  • It reads active and archived DB2 logs to quickly identify, correct, and restore erroneous data.

This article describes a use case scenario for the log analysis component of DB2 Recovery Expert. Think of log analysis as similar to a "check diagnostics" reading in your car. In this scenario, you loan your car to your brother one day, and he uses it to drive around town. He returns it to you after his voyage, and the next day, the engine won't start. You frantically try to figure out the problem, so you can get to work on time. Think of the car diagnostics check as log analysis: a way to check through all the different diagnostics the car keeps in order to figure out where the problem is. Now you are able to check each part of the car and narrow down the location of the problem. Diagnostics indicate that your brother left the headlights on all night. In a scenario that parallels DB2 Recovery Expert, you could even undo your brother's mistake so that you could start your car.

The car scenario is very similar to the log analysis component of DB2 Recovery Expert. In a database scenario, you are a database administrator (DBA), and you leave for the evening after you ensure that everything is fine with the database. Another DBA is going to implement one of his new applications that he has been working on for the last 3 months. He starts it to run overnight, and he leaves to take the next day off. When you come into the office in the morning, you get calls from different users complaining that the data that they expected from the database incorrect, and they need you to fix it immediately.

You use log analysis in DB2 Recovery Expert to review the events in the DB2 logs to find out what happened while you were gone. You narrow down the problem by looking at the specific tables that were altered and seeing which user ID or application ID accessed the tables. You determine that the problem is caused by the new application that deleted 1000 rows by accident, as shown in Figure 1.

Figure 1. DB2 Recovery Expert interface
Screen shows tables altered during a timeframe

You can use DB2 Recovery Expert to undo the SQL that deleted the rows and to restore the database to its original state.

Returning to the car scenario, suppose that you have a problem with your headlights. With a localized problem like this, the logical course of action is not to scrap your entire car and try to rebuild it from scratch. Obviously, you would analyze the problem with the headlights and either try to fix them or replace them.

Similarly In the database recovery scenario, a user or application might accidentally delete a table. In DB2, you might need to recover the entire database to fix the problem, which probably won't be practical. However, you can use DB2 Recovery Expert to identify the dropped table and simply recover that single table. This course of action gets you back online much faster.

If there are too many problems with your car (broken headlights, alternator not working, transmission failing), it might be wiser to get a new car. Similarly, if there are too many problems with your database, it might make sense to restore the entire database. You need to figure out which strategy works best for each situation and act accordingly. DB2 Recovery Expert can help you make the best decisions and implement the best solution.


Using DB2 Merge Backup

DB2 Merge Backup enables you to replace regular full DB2 backups with more-frequent, full merged backups by combining incremental and delta backups with a recent full backup, as shown in Figure 2. The resulting merged backups are available in the shortest possible restore time, which aligns with your SLAs and reduces application downtime.

Figure 2. DB2 Merge Backup
DB2 Merge Backup architecture diagram shows full backup, incremental backup, and delta backup feeding into DB2 Merge Backup, along withe DB2 history file, to achieve full merged backup.

Consider a scenario in which DB2 Merge Backup can help improve a typical backup routine that involves creating a weekly full DB2 backup every Sunday, creating daily delta backups on weekdays, and creating an incremental backup every Thursday, as shown in Figure 3.

Figure 3. Typical backup schedule
timeline with full backups on Sundays, incremental on Thursdays, and delta backups on weekdays

As your business grows with more customers, the database becomes larger, which increases the amount of time and resource needed for a full backup. With this schedule, if the database is damaged on Saturday, the DBA (that's you) needs the following components for recovery:

  • Full Sunday backup
  • Incremental backup from Thursday
  • Delta backup from Friday
  • DB2 transaction logs

The recovery from all these components takes a lot of time. It would be much easier and faster to recover the database using more-frequent, full backups throughout the week, but creating a full DB2 backup more frequently would require too much time and resources.

DB2 Merge Backup gives you the capability to take delta or incremental backups and merge them with an already existing DB2 full backup to quickly create a new merged backup that serves the same purpose as a full DB2 backup. Creating a full merged backup is faster, so you can afford to schedule a full merged backup as often as necessary, such as every other day, as shown in Figure 4.

Figure 4. Backup schedule with DB2 Merge Backup
Timeline shows merging of full backup with delta backups to create a more frequent full merged backup

With merged backups, if a recovery is needed on Friday, you can restore everything from Thursday's merged backup along with the transaction logs. With DB2 Merge Backup, you need only one full DB2 backup to start the backup routine. All the subsequent merges can start from the full merged backups that DB2 Merge Backup generates.

DB2 Merge Backup supports both offline and online backups. You can run DB2 Merge Backup in standalone mode outside and independently of the current DB2 process, even on a completely different machine, which drastically reduces the impact on the database server.

DB2 Merge Backup helps IT departments increase data availability and integrity, as well as decrease the risk of data loss. Because a complete and accurate data backup is always available, you can recover data more quickly for better compliance with SLAs and for a significantly reduced cost of downtime.


Using InfoSphere Optim High Performance Unload

Use InfoSphere Optim High Performance Unload (HPU) to further accelerate recovery of critical information and to reduce restore and migration times for large volumes of data. During a database outage, there is a lot of pressure to get systems up and running as quickly as possible. But recovery operations often require extraction of large amounts of data when DBAs don't have much time.

High Performance Unload helps to speed up the recovery of large volumes of data while minimizing the impact to production systems. To achieve its exceptional speed, High Performance Unload reads directly from the DB2 table space containers instead of using the database engine when extracting, recovering, and moving data. High Performance Unload can recover data from single table failures or accidental table drops from full, incremental, delta, and merged backups. The recovery does not interfere with or slow down production databases or CPU resources. High Performance Unload can perform unloads from multiple database partitions, and it provides repartitioning capability in a single step for rapid data redistribution on the same or a different system.

Consider a scenario in which you are a DBA who needs to periodically update your company's data warehouse system with the data from the production database, but taking the production system offline would compromise data availability. High Performance Unload does not interact directly with the DB2 database manager, so the production database performance is not affected, as shown in Figure 5.

Figure 5. Unloading data with InfoSphere Optim High Performance Unload
diagram shows movement of data from DB2 online production database through High Performance Unload to the data warehouse

Using parallel processing, advanced output options, and a variety of supported output formats, High Performance Unload can unload the data significantly faster and in a more efficient way than other tools can, including DB2 export.

You can also use High Performance Unload to perform complex system migrations in a few simple steps. For example, your company might purchase new hardware for its database. The new system has bigger and faster drives, and it requires fewer partitions. The existing DB2 system has 3 DPF partitions (S1, S2, and S3), and the new system requires only 2 partitions (T1 and T2). To migrate the data from the old system to the new system, you can use a single High Performance Unload control file to unload the data, transfer it, and load it into the new database, as shown in Figure 6.

Figure 6. Migrating and repartitioning with InfoSphere Optim High Performance Unload
Migrating and repartitioning with InfoSphere Optim High Performance Unload

The tool creates a single named pipe for each partition on the target system and begins loading the data. It consolidates the multiple streams that are coming from each of the source partitions into a single stream, which is then passed to the named pipes. As a result, High Performance Unload performs the unloading and the loading processes in parallel, which optimizes time and resources.


Conclusion

Three tools make up the DB2 Advanced Recovery Solution for Linux, UNIX, and Windows:

  • DB2 Recovery Expert
  • DB2 Merge Backup
  • InfoSphere Optim High Performance Unload

The solution helps you keep your database downtime to a minimum, increases application availability, reduces DBA labor, and keeps end user application data safe and current.

DB2 Recovery Expert for Linux, UNIX, and Windows is a simple, self-managing database recovery tool that enables database recovery operations with minimal disruption. DB2 Recovery Expert enables intelligent analysis of altered, incorrect, or missing database assets, including table spaces, tables, indexes, and data. The product automates the process of rebuilding these assets at a specified point in time, often without taking the database or the business operations offline. Log analysis is a powerful component of DB2 Recovery Expert that helps you maintain high availability and complete control over data integrity. It enables you to monitor data changes by automatically building reports of changes that are made to database tables.

DB2 Merge Backup for Linux, UNIX, and Windows combines incremental and delta backups with the latest full backup to create a new full and current backup without actually taking a new full backup and slowing down your production system. This processing runs independently of the current DB2 process. If a restore is later required, DB2 Merge Backup can help speed up the recovery.

InfoSphere Optim High Performance Unload for DB2 for Linux, UNIX, and Windows is a solution for unloading, extracting, and repartitioning data faster with less demand on system resources. DBAs can use InfoSphere Optim High Performance Unload to quickly plan and execute database migrations and to increase the scalability, reliability, and performance of mission-critical database applications.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=682121
ArticleTitle=Reduce database recovery time by using DB2 Advanced Recovery Solution for Linux, UNIX, and Windows
publish-date=06232011