Simplify backup and recovery with IBM DB2 Merge Backup for Linux, UNIX, and Windows

Step-by-step guide on how to create a merge backup with IBM DB2 Merge Backup

It is important to have an up-to-date and consistent backup available so that you can speed database recovery times. IBM® DB2® Merge Backup for Linux®, UNIX® and Windows™ gives you alternative strategies to eliminate the need to take regular DB2 full backups and instead use multiple delta and incremental backups to build a new full backup copy that is fully recognized by DB2. This step-by-step article introduces you to IBM DB2 Merge Backup and teaches you how to create merge backups using the control file structure in IBM DB2 Merge Backup.

Share:

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

Photo: 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.



Soid Quintero (squinter@us.ibm.com), Optim Technical Enablement, IBM

Photo of Soid QuinteroSoid Quintero is a software engineer at IBM's Silicon Valley Lab in San Jose, CA. She focuses on customer technical enablement for Optim Database Tools. She recently graduated with a Master's degree in Human Factors and Ergonomics from San José State University.



28 March 2013

Also available in Chinese

This article is a step-by-step guide to show how you can use IBM DB2 Merge Backup for LUW to create a full merge backup using the control file structures available in DB2 Merge Backup. Using the article, you will learn how to create both a full and a delta backup, and then how to merge the delta and full backups together using a control file to create a new full merged backup.

Following the steps in this article, you will:

  1. Take a full backup of your database to serve as your baseline backup.
  2. Simulate some activity in a database.
  3. Take a delta backup to capture the changes you have made since your regular full backup.
  4. Create a new merged backup combining the DB2 full backup and the delta backup and investigate how this new backup gets registered in the DB2 history file.
  5. Use the db2ckbkp utility to validate that the backup created by Merge Backup has the integrity needed to be used when it is time to do a restore of your database.

The examples used in this article are for illustrative purposes and may not reflect best practices.

This article refers to the IBM DB2 Merge Backup for Linux, UNIX, and Windows product as DB2 Merge Backup, Merge Backup utility, or simply Merge Backup. The article is written for DB2 for Linux, UNIX, or Windows database administrators whose skills and experience are at a beginning to intermediate level. You should have a general familiarity with database administration and backup and recovery principles. You do not need to have IBM DB2-specific skills.

System requirements

To perform the examples and get the most out of this article, it is recommended to have the following software installed:

  • IBM DB2 Merge Backup for Linux, UNIX and Windows version 1.1.1.1
  • DB2 10.1 or a previous version of DB2 for Linux, UNIX, and Windows

Refer to the Resources section for software download information.

Throughout the examples in this article, you will be using a sample database called GSDB, which contains sample data for a fictional outdoor equipment seller called the Great Outdoors company. Before you continue further with this article, please create the GSDB database (refer to Resources for download information). If you want to use your own existing database, remember to use your database name instead of GSDB when referenced in the examples of this article.

To be able to successfully complete incremental or delta backups, the database must be placed into archive log mode, which means that the TRACKMOD database parameter must be set to YES. Refer to Resources for help on setting the TRACKMOD parameter to YES.

Create a directory on your machine that you will use as the location where your backups will be saved. This article references the /home/db2inst1/Desktop/BACKUPS directory as the location where backups will be saved. Be sure that the directory you use to complete the examples in this article exists.


IBM DB2 Merge Backup for LUW overview

Every second that your database is down, whether it is planned or unplanned, can mean productivity loss, data loss, or money loss, which can be very damaging to a company. The DB2 Advanced Recovery Solutions have been carefully prepared to help you save money, time, and decrease database down time. DB2 Merge Backup is one of the tools in the DB2 Advanced Recovery Solutions that can help you decrease your backup time and speed up your recovery time.

DB2 Merge Backup is a command-line utility tool that lets you merge full DB2 backups with incremental or delta backups to build a new full merge backup that you can use to restore databases or individual table spaces. The merge backups contain the same information as a full backup created using DB2 for LUW.

As you may already know, databases tend to grow rapidly with data and that, in turn, makes creating frequent full backups more and more time consuming. However, if you do not constantly back up your data it can prove to be a costly choice for you. The choice can prove to be so costly that data may never be recovered. With DB2 Merge Backup, you can create delta or incremental backups that you can use to create full backups, instead of always having to create regular full backups. You start with a full regular backup and merge any delta or incremental backups together to create a new full merged backup. What this means is that you only have to create a full regular backup once, and after that, all merges will start from the full merge backup that was created using DB2 Merge Backup.

Figure 1 shows that Merge Backup takes the information from the history file about the latest full backup along with the incremental and delta backups, and merges them into one new full merge backup. This new full merge backup can then be processed by the db2ckbkp and restore utilities just like regular DB2 full backups. The new merge backup can also be used for future subsequent merges along with incremental or delta backups, eliminating the need for performing a regular DB2 full backup.

Figure 1. Merge Backup overview diagram
Merge Backup Overview

DB2 backup types

As mentioned in the previous section, a database backup that was created using DB2 Merge Backup holds the same information as a regular full backup created by DB2 and can be used instead of the last source backup to do a restore. This means that all types of backups that are supported by DB2 can be used by Merge Backup to create a new merge backup that can eventually be used to restore your database. Now, take a look at the four different types of backups—full backup image, incremental backup, delta backup, and logs—that are supported by DB2 and DB2 Merge Backup.

  • Full backup image: This is the foundation to any recovery strategy. Without a full backup image you can't do anything with recovery. You always need to have a full backup as your baseline. If the backup is taken online, you require the logs of all transactions that transpired while the backup was taking place. Restore a full backup, replay the logs of all transactions since the backup, and your recovery is complete.
  • Incremental backup: This includes all changes since the last full backup. You would need to restore a full backup, restore the incremental backup, replay the logs since the incremental backup, and your recovery is complete. Figure 2 shows the incremental backups.
    Figure 2. Incremental backups
    Diagram showing full backup on Sunday, with incremental backups Monday to Saturday and another full backup on Sunday.
  • Delta backup: This includes all changes since the last backup of any kind—full, incremental, or delta. If the last backup was a full backup image, it and the delta backup provide the most complete backup. If a delta is preceded by an incremental backup image, you need the delta, the incremental backup, and the full backup image on which the incremental is based. If a delta is preceded by one or more deltas, you need all deltas until you reach an incremental backup or a full backup image. Figure 3 shows the incremental backups.
    Figure 3. Incremental delta backups
    Image shows a full backup at both ends, with 6 delta backups in between.
  • Log Files: The logs contain all the transactions since the last backup you were able to restore.

Control files

A control file is simply an ASCII file that can contain a full list of DB2 Merge Backup options. With these options you can have total flexibility and power over the data. It is recommended that you use control files when you want to accomplish a more complex task, but that doesn't mean you can't use them for simple tasks. You can also reuse control files across databases by overwriting their contents with the command line. For example, if you wanted to process the same task on multiple databases you can overwrite the database name specified in the file by using the –d database_nameoption when invoking a control file.

Each control file requires only a MERGE clause. The MERGE clause is used to specify the scope of your merge backup. For the example in this article, the scope will be the GSDB database. It is possible to have more than one MERGE clause in one control file, separated by semicolons. In the example used in this article, the control file will include an OUTPUT clause, which lets you specify the location of where to store your merge backup file.

You can specify the use of a control file from a command-line window using the following syntax: db2mbk -f control_file_name, where control_file_name is the name of your control file. In the section Merging Backups, you will learn how to create and run a control file to create a merge backup.


Creating a full database backup as a baseline

To start, you need to first create a full backup of the database GSDB. This full backup will serve as the baseline. Remember that IBM Merge Backup requires the use of a full backup as a source to be able to create a merge backup as a starting point. After you create the DB2 full backup you will not need to create another full backup again.

  1. Open a DB2 command prompt and start DB2 if it is not already started. To start DB2 you can issue the following command:

    db2start
  2. Run the DB2 TERMINATE command to end any database connections that may exist.

    db2 terminate
  3. Finally, execute the BACKUP command to create your GSDB database backup.

    db2 backup db gsdb to /home/db2inst1/Desktop/MBK/BACKUPS compress without prompting

    Remember to use an existing directory on your machine of where you want to store the backup.

    When the backup operation completes, the terminal displays the timestamp for when the backup image was created. Make a note of the timestamp because you will refer to it later in this article. Figure 4 shows the output.

    Figure 4. Create DB2 full backup terminal output
    Command window showing the terminal output with the timestamp outlined in red.
  4. You can check that your full backup was created by checking the directory location you specified to save the backups. You should see a new file that has been created with the timestamp that was shown in the output when you created the backup as part of the file name.

    Figure 5. File browser viewing backup
    Screenshot showing the file browser viewing backup location

    If the backup did not complete successfully, check that the command information you entered is correct.

Now that you have your initial full backup to use as a baseline, you can begin to use any other type of DB2 supported backups, including merge backups created with DB2 Merge Backup, to create merge backups.


Simulating changes to the database

To capture any changes to your database since the initial backup you will need to simulate some changes to the database. This section walks you through the process of creating some changes to the GSDB database.

If you are not using the suggested sample database GSDB, you will need to simulate some database changes to the database you are using for this article. You can simulate some simple changes such as adding a new entry to a table, creating a new table, or updating an entry, just so there are some changes to capture since the full backup.

The following query will increase the customer quantity by 1 for the CUSTOMER_ORDER_DETAIL table of the GOSALESCT schema of the GSDB database.

  1. In your DB2 command prompt, create a connection to the GSDB database. To connect to the GSDB database, type the following command:

     db2 connect to GSDB
  2. Next, you must update the customer quantity by 1 using the following query:

    db2 update gosalesct.cust_order_detail set cust_quantity = cust_quantity + 1;
  3. Commit your changes to the database.

    db2 commit
  4. Terminate the database connection you created to GSDB.

    db2 terminate

    Figure 6 shows this process.

    Figure 6. Making changes to the database
    Command window showing the process.

At this time, all the changes that will be made to the GSDB database to be captured by the delta backup have been made.


Creating a delta backup

You are now ready to create the delta backup that will be used to create the merge backup in the next section. This backup only captures the changes that have been made to the database since the last full backup.

  1. While still in the DB2 command prompt, type the following information:

    db2 backup db gsdb incremental delta to /home/db2inst1/Desktop/MBK/BACKUPS compress
    without prompting

    The command should be written out on one line.

    Wait for the command to finish executing; you will see a success message in the terminal. The terminal output also displays a timestamp, and you need to make note of the timestamp. Figure 7 shows the delta backup output.

    Figure 7. Delta backup output
    Command window showing the terminal output with the timestamp outlined in red.

    If the command does not complete successfully, make sure you have typed the correct information.

    Let's make sure that both the full backup and the delta backup have actually been created.

  2. Using a file browser window, navigate to the location where you specified for the backups to be saved. In this example, the directory is /home/db2inst1/Desktop/MBK/BACKUPS.
  3. There are two files now, your full backup and the delta backup. Figure 8 shows the file browswer window with the two backups.

    Figure 8. File Browser reviewing backups
    File Browser showing 2 backups
  4. Notice that the file names of your backups appear similar; this is not a coincidence. DB2 backup file names are made up of several elements concatenated and separated by periods. The general rule is: DB_alias.Type.Inst_name.DBPARTnnn.timestamp.Seq_num.

    Where

    • DB_alias is the database alias name, in this example it is shown by GSDB.
    • Type refers to the type of operation. Here it is seen as 0, which means that this is a database-level operation.
    • Inst_name is the name of the database instance. In this example, the DB2 instance name is db2inst1.
    • DBPARTnnn represents the database partition number. Because the GSDB database has only one partition in the example environment, the result seen is DBPART000.
    • timestamp is also used as part of the file name. Each of the timestamps you noted match one of the files, with the most recent time belonging to the delta backup.
    • Seq_num is just a sequence number given to the file. This example shows that both files have a sequence number of 001.

    You can also clearly see a difference in sizes in the two files, with the full backup file being the larger of the two.


Creating a full merge backup

This article is about showing you how to create a merge backup by creating a control file. The following section explains how to create a control file to merge the full back and the delta backup. Although you can create a merge backup without the use of a control file, it is recommended that you use a control file for more complex tasks.

  1. To create a control file, open a new blank text document and save it as merge.ctl. Here is how the completed control file will look.

    MERGE database gsdb
    
    OUTPUT to “/home/db2inst1/Desktop/MBK/BACKUPS”
    
    compress yes

    What this will do is create a new merge file that will include any delta or incremental backups that were created since the last full merged backup or initial regular full backup for the GSDB database.

  1. Following are the individual steps to create the contents of the control file.

    1. Start with the MERGE clause. In your newly created control file, type:

      MERGE database gsdb
    2. Next, in your control file you want to have a location of where to save the new merge backup that will be created. This can be done with the OUTPUT clause. In your control file, write the following for the OUTPUT clause on a new line:

      OUTPUT to “/home/db2inst1/Desktop/MBK/BACKUPS”
    3. On a new line, write to compress the merged backup. In the control file, type:
      compress yes

      This uses the default DB2 compression library to reduce the size of the backup.

  1. Save all the changes that you have made to the control file and close it.
  2. From the DB2 command line, change directories to where you saved your control file.
  3. Execute the control file by typing the following in the DB2 command window:
    db2mbk -f merge.ctl

    Wait for the merge to complete. You will see output information about the merge in the terminal. Let's review what the output says about the merge you just completed. Figure 9 shows the output.

    Figure 9. Merge backup output
    Command window showing the merge data.

    The contents of the control file are shown at the top of the output. The start and end time of the merger are shown as well as how long the process took. You are also given information about the backups that were involved in the merge (they are listed by their timestamp). To find exactly which backups were involved, refer to the timestamps that you wrote down when you created the backups. Make note of the timestamp for the merge backup because you will use this for the following section when you invoke the db2ckbkp utility.


Reviewing the history

DB2 Merge Backup uses the history file to determine the list of backups. You can use the db2 list history command to determine how merge backups get registered to the history file.

  1. Retrieve the database backup history by issuing the following command in a command prompt:
    db2 list history backup all for GSDB

    Information about the three backups that you took will be listed. For example, Figure 10 (see larger image) displays the end of the output from the list history command; the details belong to the merge backup. You may be wondering how you can tell that the information in Figure 10 belongs to the merge backup. The answer is that it is indicated by the M in the operation type (OP) column in the table. You could also match the timestamps.

    Figure 10. History details for merge backup
    Command window showing the list history for merge

There is plenty of information listed about your backups after using the list history command. Details about a backup such as operation type, the type of backup, the timestamp of the backup, and the location of the backup can be found in the output.

Table 1 summarizes the different values for operation and operation type that you can find in a recovery history file.

Table 1. Operation types
TypesDescription
FFull Offline
MMerge
BBackup
NOnline
IIncremental offline
OIncremental online
DDelta offline
EDelta online
RRebuild

Validating the backup with the db2ckbkp command

With the DB2 check backup utility, invoked by using the db2ckbkp command, you can test the integrity of your backup image to determine if you can restore the image.

Let's validate the integrity of the merge backup you just created.

  1. In the DB2 terminal or from a file browser, change directories to the location where you have been saving your backups.
  2. You will need to know the full merge backup file name to use the db2ckbkp command. Perform a listing of the backup image files from the command prompt or use a file browser to get the file names of the backups you took. Locate the merge backup using the timestamp information you noted when you created the backup. The merge backup is the file with the most recent timestamp. In this example, the file name of the merge backup is GSDB.0.db2inst1.DBPART000.20130304125847.001. Figure 11 shows the files.
    Figure 11. List backups to get merge file name
    Command window showing list backups

    You are now ready to invoke the db2ckbkp command.

  3. From the command prompt, type the following command:

    db2ckbkp -h GSDB.0.db2inst1.DBPART000.20130304125847.001

    Remember to use you own backup file name.

  4. Wait for the verification process to complete; you will see hash tags (#) being displayed in the bottom of the output as the command is being processed.
  5. When the command is finished running, you will see the result of the verification at the end of the output (Figure 12).
    Figure 12. db2ckbkp output
    db2ckbkp output

    Take a look at what else is being displayed in the output about the backup.

  6. Note that in the output the image type is identified as being that of a merge backup because of the 1 showing in the Merge Backup Image. If this was a traditional backup you would see a 0 instead.
    Figure 13. db2ckbkp output details
    db2ckbkp output details

Conclusion

This article has given you an introduction on how to use the DB2 Merge Backup utility to combine multiple backups into a full backup. You have also learned how to validate that this backup is good to use for a restore by using the db2ckbkp utility.

You can see the value of using DB2 Merge Backup in a real-world environment because it lets you have the most up-to-date and consistent backup to recover from. The value of this tool is not in the amount of time it saves you in taking the actual backup, but in the ability to have a backup that you can take more often, and therefore, you can RECOVER faster than what you could do before. Also you never really have to take a full backup again because these DB2 Merge Backups are real DB2 full backup copies that get registered in the DB2 history file and get treated like any other DB2 backup file. Using DB2 Merge Backup in production environments helps to reduce downtime costs and DBA labor by allowing backup strategies that have the lowest impact on production, yet provide the necessary recovery assets.

Resources

Learn

Get products and technologies

Discuss

  • Get involved in the My developerWorks community Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.

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=862835
ArticleTitle=Simplify backup and recovery with IBM DB2 Merge Backup for Linux, UNIX, and Windows
publish-date=03282013