How to backup IBM DB2 Database online?

Question

I tried to backup the IBM DB2 (LUW) Database using this command:

./db2 backup database DBEMP to /home/user1/db2bkup

But I got this message:

SQL1035N The database is currently in use. SQLSTATE=57019

Then I tried this:

./db2 backup database DBEMP online to /home/user1/db2bkup

And I got this message:

SQL2413N Online backup is not allowed because the database is not recoverable or a backup pending condition is in effect.

What does it mean? Is there alternative way to backup the database online? I can't stop the database because it's being used.

• Amol Pujari: here are my notes on db2 backup and recovery, I have tried explaining in short the answer you are looking for and more such notes - amolnpujari.wordpress.com/2009/08/29/…

Answer 1

Unfortunately, it's not possible to take an online backup of a DB2 database if it's in circular logging mode, which is the default for DB2 databases when they are created.

You can check whether your database is using circular logging by issuing:

./db2 get db cfg for dbemp | grep LOGARCH

If both LOGARCHMETH options are switched off...

First log archive method (LOGARCHMETH1) = OFF
Second log archive method (LOGARCHMETH2) = OFF

... then you'll need to change your database configuration to use archive logging before backups can be taken. I'm not 100% sure of this but I believe you'll need to restart your database and take an offline backup before you can start taking online backups.

There's an overview of how to do this in IBM's "configuring database logging options" documentation.

Note that if you change from CIRCULAR LOGGING to ARCHIVE LOGGING, you'll need to figure out what to do with your archived log files. Storing them on SAN/NAS/Tape to allow roll-forward recovery can be a really good idea if this data is important to you.

• null: So we only need to set up LOGARCHMETH1, right? Is there any disadvantages or complications by setting up the LOGARCHMETH1? Is it better to use DISK or TSM for the LOGARCHMETH1 parameter?
• Nathan Jolly: It's likely that TSM won't be installed and you'll want to use DISK (If you have a Tivoli Storage Management (TSM) service running, I'd suggest speaking to the people that look after it). The disadvantage of setting up archive logging is that you'll need to start maintaining (archiving or deleting) the files in your new archive log directory - it also means that DB2 has a little more work to do when it reaches the end of a log file. The benefits should outweigh the costs. As Ian Bjorhovde suggested, it'd be wise to read up on how backup/recovery works in a bit more detail.
• Chris Aldrich: You are correct about needing an offline backup first. As for managing logs (and backups) you can actually do so with another (three) parameters. AUTO_DEL_REC_OBJ if set to ON will manage your (full) backups both online and offline and your archived logs. NUM_DB_BACKUPS tells DB2 how many full backups to retain and REC_HIS_RETENTN tells DB2 how many days you want to keep the value set for NUM_DB_BACKUPS. We use that at our shop. We taken onlines twice a day and an offline once a week. We keep 3 backups for 2 days. Works great!
• null: The purpose of the online backup is because I'm doing development of application that connect to remote IBM DB2 server that also being connected by many developers. I want to backup a database from the remote DB2 and restore into my local DB2 (Express-C version) because I want to use local DB2 for development. It's not a routine database backup that usually performed in production. So is this command enough? => db2 update db configuration for DBEMP using logarchmeth1 disk:F:/dbemp/archived_logs (note: my machine OS is Windows)
• Chris Aldrich: Yes, except that you may need to enclose the disk:F:/dbemp/archived_logs in single quotes depending on how your OS interacts with the command line for DB2. I know I have to on AIX.

Answer 2

Online backups require that the database be enabled for rollforward recovery. However, this is not the default when you create a database.

In order to do this, you need to set the LOGARCHMETH1 database configuration parameter. Once you have done this, you'll need to take one offline backup (i.e., no users can be connected).

Once you've completed these steps, you'll be able to run online backups as you wish.

You may want to spend some time reading the Data Recovery section of the DB2 Database Administration guide to help familiarize yourself with DB2 Backup/Recovery.

• null: So we only need to set LOGARCHMETH1, right? Is there any complications by setting up the LOGARCHMETH1?