urphy’s Law states that if something can go wrong, it will. It is comforting to know that you can guard against at least some calamities. In the case of IBM® DB2® Universal Database, the mere notion of your database data being deleted or somehow corrupted might cause some sleepless nights. Thankfully, DB2 has built-in features for performing backups of your precious data. This 10-Minute Solution explains and demonstrates these features, which give you at least one less thing to worry about.

Requirements: The instructions in this 10-Minute Solution assume that you have a user on your machine named db2admin with a password of db2admin and the appropriate privileges: SYSADM, SYSCTRL, and SYSMAINT.

How do I ensure that the critical data stored in my DB2 database isn’t lost forever when data loss or corruption occurs?



Use DB2’s built-in backup and recovery features to recover critical data from loss or corruption.

Create the Data to Backup
Before you can back up data, you need to create a database with a table and populate it with the data. Use the DB2 Command Line Processor to create your database:

db2 => create db backdem

Next, connect to the database you just created:

db2 => connect to backdem user db2admin using db2admin

Next, create a simple table consisting of three columns. Table 1 shows the structure and sample data you’ll store inside the table.

To create your table, use the following command:

db2 => create table secretidentities(superheroname varchar(45) primary key not null,
 superheroidentity varchar(45) not null)

Use the following syntax to populate the first row in Table 1:

db2 => insert into secretidentities values (Superman,Clark Kent)

Repeat the command to populate the table with the other two rows.

Backup! Backup! Backup!
Now back up your database. The following command backs up the database backdem to the directory c:dbback:

db2 => backup database backdem user db2admin using db2admin to c:dbback

After issuing this command, you should receive a prompt similar to the following:

Backup successful. The timestamp for this backup image is: 20031119181253.

The timestamp uniquely identifies each database backup. You should make note of it so you can later specify the database backup from which you want to restore.

You can also use the DB2 Control Center to perform your backup. Just right click on the database you want to backup and choose the “Backup” option from the context menu as shown in Figure 1.

Figure 1: Using the Control Center to Backup

The resulting DB2 backup wizard helps you create a backup task via a graphical interface (see Figure 2).

Figure 2: The Backup Wizard

Offline or Online Backup?
The syntax used in the previous section is that of an offline backup, which assumes that no user or process is connected to the database except for the one performing the backup. Keep the list applications command in mind. It lists the processes that are currently accessing the database to which you are connected.

In a production environment, you might not have the liberty to tell your database users (or processes that access your database) to go away so you can perform a database backup. In such a case, consider an online backup. Online backups require only that the tablespaces being backed up not have a user (other than the one performing the backup) connected to them.

Before you can perform an online backup, you have to make sure that the roll-forward recovery option is enabled for your database. This is not turned on by default. To enable this option, use the following command:

db2 => update database configuration for backdem using logretain on

In order for the configuration change above to take effect, you must stop and restart DB2. You can do so by issuing the commands db2stop and db2start from the command line. Also, at least one full offline database backup must be performed after the logretain option has been enabled and before an online database backup can take place. Assuming that all these requirements have been met, you can use the following command to back up the userspace1 tablespace in which your table (secretidentities) is housed:

db2 => backup database backdem tablespace(userspace1) online to c:dbback

Again, you will be provided with a timestamp for your backup image when the backup is completed.

Restore Your Database from Backups
How do you restore your backed-up database? Similar to backups, you have offline and online restore options. The following command performs an offline restore that requires exclusive access to the entire database:

db2 => restore database backdem user db2admin using db2admin from
 C:dbback taken at 20031026195337 without rolling forward

In the syntax above, you effectively destroy whatever data resides in backdem and replace it with your backup image. Also, make note of the without rolling forward clause. It states that you do not want transactions that occurred after your backup to be recovered. (The next section discusses how to recover these unaccounted-for transactions.)

In the following online restoration syntax, exclusive access is required only for the tablespace: userspace1:

db2 => restore database backdem tablespace (userspace1) online from
 c:dbback taken at 20031026195337 without rolling forward

A very important feature to note is that you don’t have to restore to the database from which you backed up. You can create a new database and restore your backup to the new database. This is extremely helpful when you have a number of programming team members who each needs access to his or her individual, yet similar database structure. The following syntax shows how to backup to a new database, which you named newdb:

db2 => restore database backdem from c:dbback taken at 20031026195337 into newdb 

The DB2 Control Center provides a graphical interface for restoring your database. You can access it by choosing the “Restore” option from the context menu of the database of interest (as Figure 3 shows).

Figure 3: Restoring from the Control Center

The resulting Restore Data Wizard walks you through the restoration process from a database image (see Figure 4).

Figure 4: The Restore Wizard

Teach DB2 to Roll Data Forward
What if you perform a change to the database after you backed it up? Are the inserts or changes you made to rows lost? No, thanks to the rollforward command. It lets you recover changes to your databases from transaction logs that DB2 maintains behind the scenes.

Before you can perform a roll forward, however, you have to make sure that your database is configured for the appropriate type of logging. By default, DB2 databases use circular logging, which doesn’t retain the transaction history you need to recover a database. The type of logging you need is known as archive logging.

As previously discussed, you can enable archive logging with the following command:

db2 => update database configuration for backdem using logretain on

You can also right click on the appropriate database name in the Control Center’s context menu and choose the “Configure Database Logging” option to start the Configure Database Logging Wizard (see Figure 5). The wizard will guide you through the process of selecting a logging type and specifying logging options and parameters.

Figure 5: Choosing the Logging Type

To test out how rolling forward works, add a new row to the database table:

db2 => insert into secretidentities values (Robin,Dick Grayson)

If you restored your database from the image you created earlier, you would not be able to see the row insert above.

You can perform a rollforward using the following syntax:

db2=> rollforward database backdem user db2admin using db2admin
 to end of logs and stop

You can perform rollforwards to a certain point in time (not necessarily to the end of the logs, as done above). The following command specifies that you want to roll forward only to a certain point in time:

db2=> rollforward database backdem user db2admin using db2admin
 to 2001-02-22-14.65.21.245343 and stop

The DB2 Control Center includes a Rollforward Wizard that allows you to graphically specify the options for a rollforward operation (see Figure 6).

Figure 6: The Rollforward Wizard

If Something Can Go Wrong…
If your data is precious to you and your organization, use DB2’s built-in features to recover from a data-loss nightmare. You could automate the backup process on a periodic basis to external mediums (e.g., tape drives). The Backup Wizard of the Control Center can guide you through the process of choosing the frequency of your database backup process.

Backup measures may sound pessimistic, as they assume something might go wrong, but they are realistic. Many times things do go wrong. It helps to know that with DB2, you can recover when they do (pun intended).