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:
- Take a full backup of your database to serve as your baseline backup.
- Simulate some activity in a database.
- Take a delta backup to capture the changes you have made since your regular full backup.
- 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.
- Use the
db2ckbkputility 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.
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 220.127.116.11
- 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
TRACKMOD database parameter must be set to
YES. Refer to Resources for help on
TRACKMOD parameter to
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
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
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
Figure 2. Incremental backups
- 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
- Log Files: The logs contain all the transactions since the last backup you were able to restore.
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
database_nameoption when invoking a control file.
Each control file requires only a
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
clause in one control file, separated by semicolons. In the example used in this
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
db2mbk -f control_file_name,
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.
Open a DB2 command prompt and start DB2 if it is not already started. To start DB2 you can issue the following command:
Run the DB2
TERMINATEcommand to end any database connections that may exist.
Finally, execute the
BACKUPcommand 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
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
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.
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
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;
Commit your changes to the database.
Terminate the database connection you created to GSDB.
Figure 6 shows this process.
Figure 6. Making changes to the database
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.
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
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.
- 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.
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
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_aliasis the database alias name, in this example it is shown by GSDB.
Typerefers to the type of operation. Here it is seen as
0, which means that this is a database-level operation.
Inst_nameis the name of the database instance. In this example, the DB2 instance name is
DBPARTnnnrepresents the database partition number. Because the GSDB database has only one partition in the example environment, the result seen is
timestampis 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_numis just a sequence number given to the file. This example shows that both files have a sequence number of
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.
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.
Following are the individual steps to create the contents of the control file.
Start with the MERGE clause. In your newly created control file, type:
MERGE database gsdb
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
OUTPUTclause. In your control file, write the following for the
OUTPUTclause on a new line:
OUTPUT to “/home/db2inst1/Desktop/MBK/BACKUPS”
On a new line, write to compress the merged backup. In the control file, type:
This uses the default DB2 compression library to reduce the size of the backup.
- Save all the changes that you have made to the control file and close it.
- From the DB2 command line, change directories to where you saved your control file.
- 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
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
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.
- 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
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
Validating the backup with the
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.
- In the DB2 terminal or from a file browser, change directories to the location where you have been saving your backups.
- You will need to know the full merge backup file name to use the
db2ckbkpcommand. 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
You are now ready to invoke the
From the command prompt, type the following command:
db2ckbkp -h GSDB.0.db2inst1.DBPART000.20130304125847.001
Remember to use you own backup file name.
- 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.
- 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
Take a look at what else is being displayed in the output about the backup.
- 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
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
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.
- Watch and learn from the DB2 Advanced Recovery Solution video demonstration (developerWorks, Jun 2011).
- Watch and learn about DB2 Merge Backup Part 2 from the DB2 LUW Advanced Recovery Solution video series.
- Learn how to create more complex control files from the examples in the IBM DB2 Merge Backup documentation.
- Learn more from the DB2 Advanced Recovery Solution product page.
- Follow developerWorks on Twitter.
Get products and technologies
- For download, installation, and general information about the GSDB sample database refer to the GSDB sample database page.
- Download a trail version of DB2 for Linux, UNIX, and Windows.
- Download a trial version of IBM DB2 Merge Backup for LUW.
- Get involved in the My developerWorks community Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.