DB2 – How to Purge Old Online Backups and Archive Logs

When you enable archive logging in a database, you're telling DB2 that you want to retain a copy of all transaction logs that have been created. LOGPRIMARY and LOGSECOND only control how many active log files can be in use in the active log path at any given time.

Please read the documentation on database logging for more details.

There are two ways to determine which logs to keep. One you write yourself. You would need to issue a db2 list history backup all for <db name>. This will show you the history file and locate the last backup and the last needed archive log. Anything prior to that can be deleted.

Now you can add in automation.

First step set db2 update db cfg using AUTO_DEL_REC_OBJ ON for the database you are connected to. This will now allow you to use the history file to prune unneeded backups and archive logs. You still will have to use fancy scripting and text parsing, but you can do it. (You can probably also locate a table in DB2 to get at the data. That may be easier to grab the data.) But then you can issue db2 prune history <timestamp> [with force option] and delete to remove unneeded backups. And db2 prune logfile prior to <log file name> to delete unneeded archived logs.

This is great, but why not let DB2 handle it for you all the way? Set db2 update db cfg using AUTO_DEL_REC_OBJ ON for the database in question. Then you can set db2 update db cfg using NUM_DB_BACKUPS <number>. This set the maximum number of backups you wish to retain for the period of REC_HIS_RETENTN days. Now set db2 update db cfg REC_HIS_RETENTN <number> to the number of days. If you set NUM_DB_BACKUPS to 3 and REC_HIS_RETENTN to 2, it will keep 3 backups for 2 days. In other words a backup needs to be at least two days old before it will be consider for removal. Once a backup is older than 2 days, it will check to see if there are more than 3 backups. If this is true, then it will remove the older backups.

Now for the logs.....As long as you set LOGARCHMETH1 to DISK rather than LOGRETAIN, DB2 will also clean up any archive logs that are no longer necessary. It will retain archive logs between each of the saved backups, but delete everything else. Example setting: db2 update db cfg using LOGARCHMETH1 'DISK:/archive/logs'. If you set to LOGRETAIN, then DB2 will not auto clean the logs and you are left to do it yourself.