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
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
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
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
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
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
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
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.
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.
- Learn more from the DB2 Advanced Recovery Solution product page.
- Watch and learn from the DB2 Advanced Recovery Solution video demonstration.
- Find the DB2 Recovery Expert product page, which includes a download link for a trial version of the product.
- Check out the trial download for DB2 Recovery Expert for Linux, UNIX, and Windows.
- Explore the DB2 Merge Backup product page, which includes a download link for a trial version of the product.
- Read the InfoSphere Optim High Performance Unload product page, which includes a download link for a trial version of the product.
- Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Follow developerWorks on Twitter.
Get products and technologies
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
- Check out the developerWorks blogs and get involved in the developerWorks community.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.