DB2 Backup Basics - Part 1-3

 
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.

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

In my next posting I’ll talk about some of the tuning parameters with backup.

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.