I am interested in one thing, if I understood correctly. DB2 is in Archive mode (online backups are made and archive logs keep coming which is OK).
If I made offline full backup today and I know I will not need returning to previous days etc. can I be free to delete all archive logs? In case of doing the restore of today's backup I suppose I will not get some error "S000013.log is missing" or something like that?
By my opinion it should be OK but I want to check this with experts?
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.
When you restore an offline backup, you are not required to roll forward (although the database will be in ROLLFORWARD PENDING state at the end of the backup, which you can avoid by using the WITHOUT ROLLING FORWARD option for RESTORE DATABASE).
This means that you can remove any archived log files.
However, keep in mind that you may want to be careful about which archive log files you remove – if you aren't careful you could accidentally remove files newer than the backup that might be necessary.
Excellent synopsis by Chris. The term of art in DB2 product documentation in case you want to read more about all this is "recovery objects". Specifically, the Managing recovery objects section in the Database administration manual.
There is a PRUNE HISTORY command that in some of the more common use cases can now substitute for the scripting Chris mentioned. Lots of other goodies as well which I wouldn't have found were it not for this question and Chris' answer.
