Hi all,

DB2 V8.1 fixpak 6 on Solaris 8 and DB2 V8.1 fixpak 6 on Windows 2000 Server

I have created a task in the task center to do daily backups. DB2 backs up correctly but does not delete backup images after a certain period of time. I have specified NUM_DB_BACKUP = 3 and REC_HIS_RETNTN = 3 in the configuration. I have to manually delete the images everyday. For the Windows i have written a batch script (to delete the image folder) and scheduled it in the windows scheduler, so far its working good. I dont know any workaround for the solaris box. Any suggestions?

Additional Info:
I have enabled circular logging / LOG_RETAIN = NO.
I do full backups everyday, kicking out the users whoever is online.

Thanks in advance,
Newbie

"This parameter specifies the number of database backups to retain for a database. After the specified number of backups is reached, old backups are marked as expired in the recovery history file. Recovery history file entries for the table space backups and load copy backups that are related to the expired database backup are also marked as expired. When a backup is marked as expired, the physical backups can be removed from where they are stored (for example, disk, tape, TSM). The next database backup will prune the expired entries from the recovery history file.

When a database backup is marked as expired in the history file, any corresponding file backups linked through a DB2 Data Links Manager will be removed from its archive server."

So what the above says is that "old backups are marked as expired in the [DB2] recovery history file." If you have an automated backup facility like TSM, it will read the DB2 recovery history file and delete the actual backup images. If you are doing your own backup command (via some script) then you will have to delete the backups yourself. You could figure out how to read the DB2 recovery history file to determine which backups to keep, but I have no idea how hard that is.

We do the same things you describe on both Unix and Windows, theres nothing wrong with controlling the delete thru scripts.

Thank you for the prompt replies (Marcus_A and akratz). I thought i was doing something weird.

akratz,
Could you send me an example of your unix script? I dont know much about shell scripting in unix.

Thanks in advance,
Newbie

In our unix environment, we only keep one backup image online so we dont have anything fancy in the script to determine timestamp etc like we do in windows.

So we have a shell script that does the delete and the backup all in one run

You can put these lines straight into a file, we use the .sh naming convention but as long as it is executable, it can run from a cron schedule

cd /backupdir

rm DBNAME*

db2 backup db DBNAME online to /backupdir


As long as you know you have no files in /backupdir that begin with DBNAME other than the backup image, the wildcard on the rm is safe.

If you wanted to be extra safe, you could make the name longer, such as
DBNAME.0.INSTNAME.NODE*

In this example using whatever is the naming convention for your backup image including your database name and instance name.

To make a scripts file executable, say you have backup.sh
just do this:

chmod +x backup.sh

One full backup works for me too. I will try to get the script done as soon as i can. Thanks.

You don't want to delete all previous backups unless you are sure that the current backup is sucessful. Otherwise, make sure you have a current copy of your resume on hand.

In my example our online backup is also copied to tape. The extra copy online is redundancy we can afford between backups.

If you are only going to depend on 1 copy of a backup and only on disk, you should probably have the resume prepared as well.

Ha! Ha! I did not see that comming.

I was defnitely gonna do something about the script. Like play around with dates and delete only those backed up 3 days ago or something like that... Thanks guys.

I don't want to beat a dead horse, but does only deleting a backup more than n days old guarantee that you still have a valid backup? I don't think so unless you only run the delete script if the backup job runs successfully.

The existence of a current backup file does not necessarily mean the last backup completed without error. You need to somehow make sure the backup command did not return an error message before you delete any backups.

Not a dead horse, its a good point MA. What I'm wondering now, is how do you determine from the script if the backup succeeds, do you know how to do this, is there a method from within the script where you can check return code.

I don't know because I am not a UNIX scripting expert. But every time my UNIX system administrator shows me the script he wrote to do this, I explain to him why it won't work the way it needs to.

I would rather delete the old backups manually than allow for the possibility of being without any backups. A lot of off-line DB2 backups fail in our shop because of applications that automatically reconnect and other similar situations.

This is only a problem for our development environment. Our production uses TSM and handles it OK.

OK, I'll look into it and post it back here if I find a way.

We haven't had a backup fail in the 3 years I've been doing it, but like you said it only takes 1 such failure to put you in a bad spot.

Do you perform off-line backups? They fail quite easily if an application is still connected to the database, and sometimes it is hard to force them off, or they reconnect automatically. This is a bigger problem at some shops than at others.

Yeah ours are online. When we do have to offline we're lucky to have our apps isolated to a few places so we can shut them down pretty easily too.