PRUNE HISTORY/LOGFILE command using the ADMIN_CMD procedure

The PRUNE HISTORY command is used to delete entries from the recovery history file, while the PRUNE LOGFILE command is used delete log files from the active log file path of the currently connected database partition. Deleting entries from the recovery history file might be necessary if the file becomes excessively large and the retention period is high.

Although the PRUNE HISTORY command deletes entries from the history file, by default, the command will always retain entries belonging to the most recent restore set. This is to ensure that in the unlikely event of a failure a database can be recovered. The most recent restore set consists of the entries belonging to the most recent full database backup image plus any other objects that are associated with it, for example, log archives, load copy images, table space backup images and incremental/delta backup images. If entries belonging to the most recent restore set are not required then these entries can be deleted by specifying the WITH FORCE OPTION on the PRUNE HISTORY command.

In a partitioned environment, the PRUNE HISTORY command only performs on the database partition it is issued on. To prune the history on multiple partitions, you can either issue the PRUNE HISTORY command from each individual database partition, or use the db2_all prefix to run the PRUNE HISTORY command on all database partitions.

Important: The PRUNE LOGFILE command is deprecated and might be removed in a future release. Use the PRUNE HISTORY command instead.

Authorization

One of the following authorities:

SYSADM
SYSCTRL
SYSMAINT
DBADM

Required connection

Database

Command syntax

Command parameters

HISTORY timestamp: Identifies a range of entries in the recovery history file that will be deleted. A complete time stamp (in the form yyyymmddhhmmss), or an initial prefix (minimum yyyy) can be specified. All entries with time stamps equal to or less than the time stamp provided are deleted from the recovery history file. When an initial prefix is specified, the unspecified components of the time stamp are interpreted as yyyy0101000000.
WITH FORCE OPTION: Specifies that entries will be pruned according to the time stamp specified, even if some entries from the most recent restore set are deleted from the file.
AND DELETE: Specifies that the associated log archives will be physically deleted (based on the location information) when the history file entry is removed. This option is especially useful for ensuring that archive storage space is recovered when log archives are no longer needed. If you are archiving logs via a user exit program, the logs cannot be deleted using this option.
If you set the auto_del_rec_obj database configuration parameter to ON, calling PRUNE HISTORY with the AND DELETE parameter will also physically delete backup images and load copy images if their history file entry is pruned.
LOGFILE PRIOR TO log-file-name: Specifies a string for a log file name, for example S0000100.LOG. All log files before (but not including) the specified log file will be deleted. The logarchmeth1 database configuration parameter must be set to a value other than OFF.

Note: This value is not supported in Db2® pureScale® environments.

Example

Example 1: Remove all entries from the recovery history file that were written on or before December 31, 2003:

CALL SYSPROC.ADMIN_CMD ('prune history 20031231')

Example 2: Delete all log files from the active log file path before (but not including) S0000100.LOG:

CALL SYSPROC.ADMIN_CMD('prune logfile prior to S0000100.LOG')

Usage notes

If the WITH FORCE OPTION is used, you might delete entries that are required for automatic restoration of databases. Manual restores will still work correctly. Use of this command can also prevent the db2ckrst utility from being able to correctly analyze the complete chain of required backup images. Using the PRUNE HISTORY command without the WITH FORCE OPTION prevents required entries from being deleted.

Those entries with status DB2HISTORY_STATUS_DO_NOT_DELETE will not be pruned. If the WITH FORCE OPTION is used, then objects marked as DB2HISTORY_STATUS_DO_NOT_DELETE will still be pruned or deleted. You can set the status of recovery history file entries to DB2HISTORY_STATUS_DO_NOT_DELETE using the UPDATE HISTORY command, the ADMIN_CMD with UPDATE_HISTORY, or the db2HistoryUpdate API. You can use the DB2HISTORY_STATUS_DO_NOT_DELETE status to prevent key recovery history file entries from being pruned and to prevent associated recovery objects from being deleted.

You can prune snapshot backup database history file entries using the PRUNE HISTORY command, but you cannot delete the related physical recovery objects using the AND DELETE parameter. The only way to delete snapshot backup object is to use the db2acsutil command.

The command affects only the database partition to which the application is currently connected.