Hi,
I am new to DB2 with background in Oracle.
Recently, just got a chance to configure online backup on windows for DB2 ver 8.1 and 9.5 databases.

I have enabled Archive logging for the DB2 database by configuring:
- LOGRETAIN to ON
- USEREXIT to ON
- TRACKMOD to ON

Some IBM online help, mentioned turning on LOGRETAIN & USEREXIT, while some other sites mentioned need to turn on TRACKMOD. I'm a little confused.

I am able to perform online backup of the DB2 database including logfiles.

However, after every online backup, the non-active archived logfiles in the LOGPATH directory never gets cleared.

Is there any parameter change which can allow the non-active archived logfiles to be automatically removed after each successfully online backup?

I have read from IBM docs :
http://publib.boulder.ibm.com/infoce...n/c0008178.htm

Log file allocation and removal
Log files in the database log directory are never removed if they may be required for crash recovery. However, if you have enabled infinite logging, log files will be deleted once they have been successfully archived. When the logarchmeth1 database configuration parameter is not set to OFF, a full log file becomes a candidate for removal only after it is no longer required for crash recovery. A log file which is required for crash recovery is called an active log. A log file which is not required for crash recovery is called an archived log.
The process of allocating new log files and removing old log files is dependent on the settings of the logarchmeth1 database configuration parameter:

I have tried enabling "infinite logging" but the non-active archived logfiles are still not removed after a successful backup?

Do I really have to write housekeeping scripts to do this task?

Any advise is greatly appreciated.

Cheers
Oracbase

First TRACKMOD is for doing incremental backups only. IT has nothing to with setting up online/offline backups.

If you turn on userexit, then you need to supply a user exit routine to DB2 to perform the archiving of the logs.

LOGRETAIN and USEREXIT parameters are the old ways to do this. You need to look at LOGARCHMETH1 and LOGARCHMETH2.

Andy

Hi,

Thanks for your reply.

Does it mean that if I turn on LOGARCHMETH1 parameter, specify a disk location to this parameter... all non-active archived logfiles will be moved to this location after a successfully online backup?

Do I still need to backup those non-active archived logfiles located at LOGARCHMETH1 location if my online backup command includes logfiles?

BTW, does this new parameter LOGARCHMETH1 applies for DB2 ver 8.1?
I'm currently testing all this on non-prod DB2 ver 9.5, but I can't really test it on the production DB2 ver 8.1 yet.

Cheers
Oracbase

yes logarchmeth1 is also available in V8
specify DISK:/newpath for this parameter
this will move the logfiles to this path when logfile gets closed
this archive path has to be cleaned by user operation and is NOT done by db2
the logfiles included in the backup are only these available in the time the backup was running - if full recovery is needed upto the crash moment, all logfiles after backup (located in archive path) are also needed. the files from this location, should be moved to tape or any external media (disk)

not disagreeing with anything Guy is saying at all, but as an Oracle and DB2 DBA thought it would be good to clarify for oracbase a big difference between Oracle and DB2 backups.

Oracle backups thru rman etc, bundle logs needed to restore from an online backup with the backup image . On DB2 managing the logs is a distinct process than managing the backup images.

So you need to make sure you are retaining the backup image and the minimum amount of logs needed to recover with that image.

we have user exit programs set up that move the closed log files and then we make sure the directories with the closed logs are getting tape backed up in alignment with tape backups of the disk images of the db2 backups.

We then have scripts that clean up the old copies of both the logs and the backup images and make sure that is in alignment with getting everything to tape first.

In some ways its nicer being able to control all that yourself, it gives you more flexibility than oracle, but you do have to do a little extra setup up front.

Hi All,

Thanks for your valuable advice.

I finally understand how to really configure archive logging in DB2 now.

In order to use logarchmeth1 parameter (pointing it to another location on disk), USEREXIT parameter will be disabled.

So that means to say, after a successful backup, my non-active archived logfiles will be automatically moved to the location specified in logarchmeth1.
This makes it easier for me to do housekeeping!

However, I did notice something strange in DB2.
There are some database parameters which are dynamic in nature, but it seems that when I changed these dynamic parameters it will not take effect until the next database restart. Is this true?

Cheers
Oracbase

some parameters are immediate and others not. this is specified in the command reference or online doc : immediate or restart ... needed for value to take effect..

Hi Guys,

Thanks for all your clarifications... its been really helpful.

Now, I have another query.

I backup online my Db2 database to a disk location ie. "D:\backup" before a 3rd party backup software picks it up to tape.

I know there is this command to list the backup history for my DB:
list history backup all for <DBNAME>;

Can I just removed those older backups for D:\backup using OS commands?
Or is there a DB2 command to properly delete off older backups on disk such that the DB2 history file is updated accordingly?

Cheers
Oracbase

imo, just delete them with os commands once you know they were successfully put to tape.

There are a couple db configurations that relate to how long records of a backup are kept in the history file:
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366