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


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
  • DB2 10.1 or a previous version of DB2 for Linux, UNIX, and Windows

Refer to the Related topics 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 Related topics 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 Related topics 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
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.
    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.
    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:

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


    • 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.
    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
    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
FFull Offline
IIncremental offline
OIncremental online
DDelta offline
EDelta online

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
    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
    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
    db2ckbkp output details


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.

Downloadable resources

Related topics


Sign in or register to add and subscribe to comments.

Zone=Information Management
ArticleTitle=Simplify backup and recovery with IBM DB2 Merge Backup for Linux, UNIX, and Windows