Question & Answer
Question
An online backup operation locks the history file for pruning and other agents will have to wait until pruning completes. This can result in all other applications encountering lock wait situations. Applications might eventually receive the following error: SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code 68.
Cause
With an online backup running, lock wait situations might occur during the time frame that the history file is being processed. The backup operation locks the history file for pruning. If there is a log archive request or a new log allocation that needs to be recorded by the logger process in the recovery history file, it needs to wait for pruning to complete and the history file to be unlocked. If the history file is big, the locks might be held for a significant period of time.
During pruning, DB2® Universal Database™ (DB2 UDB) processes the file entry by entry in order to find out which entries need to be pruned and which entries can remain. The history entries are not written in chronological order. DB2 UDB uses the database configuration parameter "rec_his_retentn" to determine which files should be retained. During this time, other applications attempting to access the history file will be in lock wait state. When the time specified in the "locktimeout" database configuration parameter is reached, these applications will receive SQL0911N errors.
Answer
To minimize the amount of time that the history file is locked by the backup operation:
- Manually prune the history file at regular intervals. For example, use the PRUNE HISTORY command.
- Set the database configuration parameter rec_his_retentn to a small value.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21244562