Automating recovery history file pruning
It is recommended to configure the database manager to automatically prune and update the status of recovery history file entries.
Two database configuration parameters determine which history file entries are eligible for
pruning. A third parameter can be used to automatically manage the database objects associated with
the pruned history entries. These parameters are:
- num_db_backups
- Specifies the number of database backups to retain in the history file for a database. For example, if you configure the parameter to 12, the database manager prunes all entries older than the 12 most recent backup entries.
- rec_his_retentn
- Specifies the number of days that historical information about backups is retained in the history file. For example, if you configure the parameter to 90, the database manager prunes all entries older than 90 days.
- auto_del_rec_obj
- Specifies whether the database manager automatically deletes log files, backup images, and load-copy images that are linked to recovery history entries when those entries are pruned. These files can be stored on disk, in a storage manager, or in remote storage.
If both of num_db_backups and rec_his_retentn are configured, entries are only pruned when they are to be pruned under both parameters.
| rec_his_retentn | num_db_backups | auto_del_rec_obj | History file entry pruned? | Database objects deleted? |
|---|---|---|---|---|
| -1 | Any value | OFF | Yes. Entries older than the configured value in num_db_backups are pruned. | No |
| -1 | Any value | ON | No | No |
| 0 | Any value | OFF | Yes. All entries older than the latest backup entry are deleted, regardless of num_db_backups value. | No |
| 0 | Any value | ON | Yes. Entries older than the configured value in num_db_backups are pruned. | Yes |
| > 0 | Any value | OFF | Yes. Entries older than the configured value in rec_his_retentn and num_db_backups are pruned. | No |
| > 0 | Any value | ON | Yes. Entries older than the configured value in rec_his_retentn and num_db_backups are pruned. | Yes |
Note: The special cases on the combination of configured values, where it does not fully adhere to
the definition of the parameters:
- If rec_his_retentn is set to -1 and auto_del_rec_obj is set to OFF, history file pruning is done completely based on num_db_backups value. No database objects are deleted.
- If rec_his_retentn is set to -1 and auto_del_rec_obj is set to ON, no history file pruning is done. No database objects are deleted.
- If rec_his_retentn is set to 0 and auto_del_rec_obj is set to OFF, all history file entries are pruned except for the latest full backup. No database objects are deleted.
- If automatic backups (Enabling automatic backup) are enabled and
auto_del_rec_obj is
OFF, the database manager keeps only the latest backup image. It deletes all other database-related files before the next automatic backup starts. For more information, see Automating backup image pruning for Db2 databases running automatic backups.
In Db2 12.1.4 and later, automatic recovery history file pruning is done asynchronously by an engine dispatchable unit (EDU) called db2autoprune. Once the database is activated, the db2autoprune EDU is created as a background agent and runs indefinitely until the database is shutdown, and the db2autoprune EDU is terminated. For Db2 pureScale configurations, each member creates the db2autoprune EDU but only one member drives the automatic prune logic while the rest idles. For cases where the database is not active, or in super-exclusive mode such as during an offline backup, offline restore, or offline rollforward, the EDU is not created. Once started, the db2autoprune EDU periodically checks for the time to start the automatic prune operation.
This time to start the automatic prune operation is set by these operations:
- The successful completion of an online full database backup or an online full table space backup. When either operation is completed on an active database where the db2autoprune EDU is started, the current time is set as the time to begin automatic pruning. This signals the db2autoprune EDU to start the automatic prune operation immediately.
- The successful completion of an offline full database backup or an offline full table space backup. When the database is not active, the EDU does not exist. While the completion of either of these operations sets the current time as the time to start automatic pruning, neither operation triggers an automatic prune operation to start because the EDU does not exist. Once the database is activated and the db2autoprune EDU is spawned, an automatic prune operation is started.
- After a database is created or Db2 12.1.4 is installed. Currently, the time to start automatic pruning is set to zero and an automatic prune operation is triggered to start on first activation. If no full database or full table space backup is taken, the db2autoprune EDU runs a prune operation every 24 hours.
The asynchronous automatic pruning can be disabled by setting the
DB2_AUTOPRUNE_EDU registry variable to OFF. When turned off, the
db2autoprune EDU does not exist and automatic pruning is done synchronously at
the following times:
- After a full database backup operation or full table space backup operation completes successfully.
- After a database restore operation, where a rollforward operation is not required, completes successfully.
- After a database rollforward operation completes successfully.
Note: Synchronous automatic pruning is the only option in Db2 12.1.3 and earlier.