DB2 Basics: Backups of Data and Configuration

Why Backup?

Backups are so ingrained into DBAs. They should really be the first and the last thing we think of and ensure we do properly. We do regular backups so we can get data back in case of some failure, be it human, software, or hardware. We do ad-hoc backups before and after upgrades or fixpacks, before and after major application or database structure changes. Frequently, backups are used to move data between servers.

Developing a Recovery Strategy

How often you backup depends on your recovery strategy. Developing a recovery strategy involves things like explicitly stating a Recovery Point Objective (RPO) and a Recovery Time Objective (RTO). These are decisions that may be outside of the DBA’s sphere of influence. Often a DBA has to ask questions and listen carefully to understand what is important about the recovery objectives for a particular database, along with helping a business understand the bugetary impications of decisions they make in this area. Determining such a strategy is beyond the scope of this article.

DB2’s Backups

The specific files that are called backups by DB2 are binary files that represent every bit of data in a DB2 database. They are as big as the entire database as a result. Since they are at the bit level, they also cannot be restored between operating systems that may use different representations of characters. Restores are not supported between Windows and Linux/Unix. Restores are not supported between big-endian Linux/UNIX and little-endian Linux/Unix. See this IBM Knowledge Center page for details on cross-platform restrictions: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.ha.doc/doc/c0005960.html?lang=en

One of the questions I get most frequently from non-dbas is “so are they like MYSQL dumps?” Nope, not at all. There are other tools you could combine to generate the SQL to create every object in the database (db2look), and then to export/import all the tables in the database (db2move), but they are not what we call backups.

Backup Options

There are a lot of options when taking a backup. I’m not going to cover all of them in this article, but focus on a few that are most basic.

Online vs. Offline

One of the most basic backup choices is whether the database will be online or offline while the backup is being taken. If an offline database backup is taken, no one will be able to access the database while the backup is running, and you must force off all connections and deactivate the database before the backup can be taken.
It is important to consider whether your database is even enabled for online backups. Only databases that are using archive logging allow online backups. If a database uses the default of circular logging, you will not be able to take an online backup (SQL2413N Online backup is not allowed because the database is not recoverable or a backup pending condition is in effect). To understand if a database is using circular logging, you can use:

$ db2 get db cfg for SAMPLE |grep LOGARCHMETH
 First log archive method                 (LOGARCHMETH1) = DISK:/db_arch_logs/SAMPLE/
 Second log archive method                (LOGARCHMETH2) = OFF

Replace ‘SAMPLE’ above with your database name. In the above example, the database is using archive logging and not circular logging. If both LOGARCHMETH1 and LOGARCHMETH2 are set to OFF, then the database is using circular logging, and online backups will not be possible. There are other things to consider when chosing a logging method, so be sure to research the implications before changing your archiving method. Among other things, if you enable archive logging, you will have to manage deleting old transaction log files.
Taking an online database backup is done by including the ONLINE keyword in the proper place in the BACKUP DATABASE command. An offline database is taken if you do not use the ONLINE keyword.
If it is easy to take the outage for an offline database backup, then choose an offline one. They are slightly easier to restore from when you don’t need to rollforward.

Backup Types

The types of backups that can be taken include:

  • Full – the entire database is backed up
  • Incremental – changes since the last full backup are backed up (restore requires a full image and the incremental image)
  • Delta – changes since the last full or incremental or delta backup are backed up (restore requires a full image and all incremental and delta images since the full image)

I prefer to take a full backup whenever possible. I use incremental backups sometimes to reduce the time it takes to perform a restore when either space or backup duration prevent me from taking more frequent full backups. I don’t like delta backups in most scenarios because I worry about managing all the files needed for a restore.
To take an incremental backup, the INCREMENTAL keyword is used in the BACKUP DATABASE command. To take a delta backup, the DELTA keyword is used in the BACKUP DATABASE command. If neither INCREMENTAL or DELTA is specified, then the backup will be a full.

Backup Scope

Backup can capture the entire database, or it can just capture a single tablespace or subset of tablespaces. A subset of tablespaces should only be used in specific situations. To backup only a subset of tablespaces, the TABLESPACE keyword is used in the BACKUP DATABASE command.

Backup Compression

You can optionally tell DB2 to compress the backup while it is being taken. This is a good option if you are taking backups to disk. If you have a dedup device or other location that applies its own compression to the backup files, then you’ll want to avoid compression when the backup is taken – compression on top of compression is not the best idea. To compress the backup as it is taken, use the COMPRESS keyword in the BACKUP DATABASE command. You can also compress the backup after it is taken using the gzip or your favorite compression command. This method has the advantage of reducing the backup duration, but it requires more disk space, more overall time, and may require more time on restore as the backup image has to be uncompressed before it can be used.

Backup Syntax

See the IBM Knowledge Center for full backup command syntax.
My favorite backup syntax is usually:

db2 backup db sample online to /db_bkup/full compress without prompting

There are many databases that only get offline backups at upgrade time, and at no other time.

What is in a DB2 Backup and What isn’t

By default, online DB2 backup images include the transaction log files that were written to while the backup was being taken. No other transaction log files are included in the backup image. Both the structure and data of all objects in the database are included in the backup image. The backup also includes the database history file and the database configuration (though these are only part of restore in specific circumstances).
I like to also regularly copy other data to ensure I have it in addition to what’s in the backup itself. Data I collect regularly includes:

  • db2look extracting SQL to recreate objects and permissions
  • list of tablespaces
  • list of tablespace containers
  • node directory
  • database directory
  • dbm cfg
  • db cfg
  • db2 registry (db2set)

Some of these items are included in database backups, but it may be hard to extract only a small part of the data from a backup. I don’t want to have to restore the whole database to see the syntax to create a single dropped table or to get the database configuration.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 544

5 Comments

  1. Hi Ember,

    Could you please help me understanding the clause “Open num sessions” in db2 database backup command using TSM and backup restore client.Is it increasing the backup performance or take the backup simultaneously in multiple streams(divided the backup image into segments).In our environment database backup command is using the clause “open 2 sessions using TSM” and I can see there are 3 entries in recovery history file and during checking the backup image with db2ckbkp,the check is being done 3 times.So is it checking the same image 3 times or it is just 3 segments of a backup image.Could you please let me know.

    Regards
    Somraj

    • I haven’t worked with TSM in several years, so I’d have to play with it to understand. You can use db2adutl commands to extract a backup image to disk (assuming you have disk with enough space) so you can play with it in a more familiar environment. I know that specifying more than one session only helps performance if you have fully separate paths to your TSM server – specifying multiple sessions when it all goes over the same adapters and same link is not going to make much difference.

      If backing up to disk, you can specify multiple locations and split your backup file into multiple pieces – to gain efficiencies of separate I/O paths or deal with not having a single large enough disk.

  2. Could you please post DB2 command to take offline TSM DB backup with multiple streams to disk(single and multiple paths)?

    • You can either take the backup to TSM or to disk, not both. There are commands to extract a backup taken to TSM to disk using db2audtl. Db2 commands do not exist to move a backup from disk to TSM, but I believe TSM would have commands for that.

      If you want to take a backup to multiple disk locations, you would use syntax like this:

      db2 backup database SAMPLE to /disk1, /disk2, /disk3 ...

      If you want multiple TSM sessions to be used, you would use syntax like this:

      db2 backup database SAMPLE use TSM open 3 sessions ...

  3. Could you please post the process flow & commands for “moving the database from old instance to new creating instance”.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.