DB2 Backup Basics

DB2 Backup Basics

Chris Eaton   (Product Manager, IBM) posted 1/10/2008 | Comments  (66)

I thought I would start the year with some basics as I have had a lot of people asking me to cover off some of the basics of backup and recovery.

So let’s first start with backup basics. In DB2 for LUW, I often talk about the backup feature producing a logical backup image . By logical I mean that DB2 does not backup the physical files that make up your tablespaces and database but rather it goes directly into those objects and backs up the data and index pages directly. This gives DB2 a lot more flexibility when it comes time to do a restore (but more on restore later).

The simplified version of the backup command is as follows:
DB2 BACKUP DATABASE db_name TO device_or_directory

That is the simplest form which will back up all the tablespaces in the database named db_name to the directory or the device (like tape drive for example) you specified in the TO clause.

Now for some options.

You don’t have to backup the full database. You can backup a set of tablespaces if you like (or even a single tablespace). Note that you don’t need to do tablespace level backups in order to perform tablespace level restores. If you do a full database backup, you can use that backup image as the source to restore just a single tablespace. So use tablespace backups only if you don’t have enough time or space to do a full database backup (otherwise if you have the storage it’s just simpler to do the full database backup). To do tablespace level backups simply add the

TABLESPACE ( tablespace_name, tablespace_name )

to the backup command above. Just list the tablespace(s) that you want to backup inside the parenthesis and DB2 will only backup those objects (again it is doing a logical page level backup of these tablespaces).

Another option is to perform the backup online (the above command I gave you would be an offline full backup). To do an online backup just add the keyword ONLINE to the backup command. One point to remember is that if you want to do an online backup, you must be archiving your log files. You cannot use circular logging. The reason is that when you restore this image, DB2 needs the log files that existed at the time of the backup to replay in order to ensure the database is consistent. This is because as the backup is happening, we are copying data pages to the backup directory but transactions could be updating data pages just after we back them up and it could also update a data page we are just about to back up in the same unit of work. So when the restore happens, DB2 must replay that unit of work to make sure the data pages are consistent.

The last option I’ll talk about in this post is the use of alternate targets (instead of just backing up to disk or tape). DB2 has tight integration with Tivoli Storage Manager (TSM) as well as other vendors backup storage management systems. For example, if you are using TSM, all you have to do is replace the clause TO directory/device with

USE TSM

In this case, DB2 will allocate an in memory buffer, fill it up with data pages and pass it directly to TSM using TSMs own API calls. Similarly you can USE XBSA to backup to any XBSA compliant backup storage management vendors software.

I think that’s enough for one posting. In the next posting I go over some more of the basics of backup and add in more details on some of the other options like incremental backup and automatic performance tuning for backup.

DB2 Backup Basics - Part 2

Chris Eaton   (Product Manager, IBM) posted 1/22/2008 | Comments  (35)

In my last posting I discussed some backup basics including the basic backup command, how to specify a target for the backup, tablespace level backups and how DB2 backups are integrated into TSM. In this posting I’ll cover a bit more about online backups and also talk about incremental backups.

Online backups

DB2 allows you to backup the data while the tables are completely accessible for reads and write transactions. What happens in this case (when you specify the ONLINE keyword) is that DB2 will back up the data pages as they exist at the moment in time the backup utility gets to that data page. This also means that a page that is backed up to your target image could change a moment after it is backed up. In fact a single transaction could update one page that has already been backed up and another page that has yet to be backed up. The result is that the image on tape (or other target) is not in and of itself consistent. However, that’s not a problem…DB2 requires that when you restore that image you roll forward to at least the time when the backup completed. This will result in a consistent image after the restore and rollforward. And to make life easier DB2 also allows you to store the required log files inside the backup image by using the INCLUDE LOGS keyword on the backup so that the image includes everything you need to restore it to another system.

You should note that there are some operations that are not compatible with online backup. For example, you can’t do an online backup at the same time you are also doing a restore or rollforward (seems like common sense but it’s a restriction none the less). Another one that may not be so obvious is that you can’t do an online backup at the same time as you do an offline load of a table. The reason here is that offline load is going and directly manipulating the data on disk include the space maps for the tablespaces and we can’t allow that during an online backup because those changes are not logged and so the backup image may be inconsistent and rollforward wouldn’t be able to take care of that. There are other incompatibilities that can come up only under certain circumstances.

A complete list of things to consider when using online backup is documented in the manuals.

Incremental Backups

By default, backups in DB2 back up all of the data in the database or tablespaces. However, you have the option to back up only the data pages that have changed. This is referred to as an incremental backup. By specifying the keyword INCREMENTAL as part of the backup command, DB2 will only back up data pages that have changed since the last full backup was taken. A second option is to use the keyword INCREMENTAL DELTA which will back up data pages that have changed since the last backup of any kind (i.e. since the last full or incremental or delta backup). The benefit of incremental backups is that the backup image can be much smaller than the size of the database (and very small if you use delta backups). The downside is that when you want to restore the backup, you must first restore the full backup and then apply the most recent incremental backup and then apply any delta backups that you have since that incremental. One nice thing is that using the RECOVER command, DB2 automatically figures out what images you need and restores them automatically and in the right sequence. However, it can still take longer than just restoring a full backup. You need to figure out what is best for your environment to optimize both the backup times but also to optimize the restore time (which is usually the more critical one to optimize for).

DB2 Backup Basics - Part 3

Chris Eaton   (Product Manager, IBM) posted 1/28/2008 | Comments  (11)

So far we have discussed backup syntax including targets and backup types and some options for backup. In this posting let’s look at a few other options on the backup command.

The first is, how do I make backups run faster. There are several performance tuning options on the backup command. For example you can set

• WITH num_buffers BUFFERS – number of memory buffers DB2 will use to store backup data prior to sending it off to the target
• BUFFER buffer-size - the size of each of these member buffers
• PARALLELISM n - the number of table spaces to be backed up in parallel

You can specify these values to tune the performance of backups. However, my advice to you is do not use these parameters with the backup command . More importantly if you have used these in prior releases after doing some tuning tests, (and have never changed them in a few years) try running a backup with these parameters removed and see how it works. DB2 will inspect the system and automatically set these values based on the resources available. Now I’m sure it won’t be optimal 100% of the time but we have seen some customers that have done hand tuning and spent a lot of time on it and when they just let DB2 do the tuning the backups ran faster (your mileage will vary but it’s worth a try to not hand tune the backup command).

The last option I’ll discuss in these postings is the UTIL_IMPACT_PRIORITY. This allows you to throttle the backup command so that it does not impact your production workload as much. This parameter along with the database configuration parameter UTIL_IMPACT_LIM allow you to tell DB2 to not impact the system by more than x% (where x is the util_impact_lim). When using this DB2 senses the impact that the backup being executed has on the throughput of your transactions and will slow down the backup intentionally if the throughput is degraded by more than x%. This is not a static limiter but rather it is dynamic and will adjust itself based on the workload running on the system even if the workload is changing over time.

That’s all for now…good discussion generated by the previous postings on backup basics so I’m assuming this is of interest to many of you. I’ll post about the basics of restore in my next few postings.

==========================================

* Make a backup in my local Database

CONNECT TO POS;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET;
BACKUP DATABASE POS TO "/mnt/aa/backup" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;
CONNECT TO POS;
UNQUIESCE DATABASE;
CONNECT RESET;
==========================================

Previous Entry / Next Entry