DB2 Basics: Backups of Data and Configuration
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.
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.
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
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
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.
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
DELTA is specified, then the backup will be a full.
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.
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.
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.