The Db2 Recovery History File

Critical to recovering a database, but largely invisible, it is a good idea to understand the recovery history file, what it contains, and how to access it.

What is the Recovery History File?

The recovery history file is one of the many files associated with a database, but it is one of the most important. It is one of the only files that can be independently recovered from a backup image. Db2 keeps multiple copies of it to protect against corruption. It is heavily used to do recoveries and to make them easier. It is also useful for understanding some types of database activity. It may also be used when pruning old recovery objects(backups, logs, and load copy files).

Information in the Recovery History File

A record is recorded in the recovery history file for the following activities:

• Backing up a database or table spaces
• Restoring a database or table spaces (even if the restore fails)
• Rolling forward a database or table spaces
• Automatically rebuilding a database
• Creating a tablespace
• Altering a table space
• Quiescing a tablespace
• Renaming a table space
• Dropping a table space
• Performing a LOAD
• Dropping a table (when dropped table recovery is enabled, and recoverable logging is used)
• Reorging a table (but not when reorging indexes)
• Archiving transaction logs manually
• Writing to a new log file(when recoverable logging is used)
• Archiving a log file(when recoverable logging is used)
• Recovering a database

Where to Find the Recovery History File

The actual history file, along with a backup copy of it are in the partition-global directory. For Linux and UNIX systems, this is $INSTHOME/<instname>/NODExxxx/SQLxxxxx. The files are called db2rhist.asc and db2rhist.bak. That said, you’ll likely not do much with the files themselves. The recovery history file is one of precious few files you can restore independently out of a backup image using the RESTORE DATABASE command. This is to help you locate recovery objects or to help Db2 locate them for using the RECOVER command instead of the RESTORE command.

Structure of a Record in the Recovery History File

There are a number of standard fields in the history file:

• An identification (ID) field to uniquely identify each entry
• The part of the database that was copied and how
• The time the copy was made
• The location of the copy (stating both the device information and the logical way to access the copy)
• The last time a restore operation was done
• The time at which a table space was renamed, showing the previous and the current name of the table space
• The status of a backup operation: active, inactive, expired, or deleted
• The last log sequence number saved by the database backup or processed during a rollforward recovery operation.

Querying Information from the Recovery History File

LIST HISTORY

The older command to view information in the history file is the LIST HISTORY command. The format of this I most frequently use is:

$ db2 list history backup all for sample

                    List History File for sample

Number of matching file entries = 2


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20180716154306001   F    D  S0000000.LOG S0000000.LOG                
 ----------------------------------------------------------------------------
  Contains 5 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 IBMDB2SAMPLEREL
 00004 IBMDB2SAMPLEXML
 00005 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP SAMPLE OFFLINE
 Start Time: 20180716154306
   End Time: 20180716154311
     Status: A
 ----------------------------------------------------------------------------
  EID: 4 Location: /db2home/db2inst1/db2inst1/NODE0000/SQL00001


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20180716154318001   F    D  S0000000.LOG S0000000.LOG                
 ----------------------------------------------------------------------------
  Contains 5 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 IBMDB2SAMPLEREL
 00004 IBMDB2SAMPLEXML
 00005 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP SAMPLE OFFLINE
 Start Time: 20180716154318
   End Time: 20180716154322
     Status: A
 ----------------------------------------------------------------------------
  EID: 5 Location: /db2home/db2inst1

The above output represents two backup images. For each backup image, the earliest and latest log files are listed, along with the timestamp, informaiton on the type of backup and a list of the included tablespaces. A failed backup looks a bit different, like this:

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20180716154534000   F       S0000000.LOG S0000000.LOG                
 ----------------------------------------------------------------------------
  Contains 5 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 IBMDB2SAMPLEREL
 00004 IBMDB2SAMPLEXML
 00005 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP SAMPLE OFFLINE
 Start Time: 20180716154534
   End Time: 20180716154535
     Status: A
 ----------------------------------------------------------------------------
  EID: 6 Location: 

SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -2036   sqlerrml: 22

 sqlerrmc: /this/dir/doesnt/exist
 sqlerrp : sqlubMWR
 sqlerrd : (1) 0                (2) 0                (3) 0
           (4) 0                (5) 0                (6) 0
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
           (7)      (8)      (9)      (10)       (11)
 sqlstate:

Note the difference at the bottom here, which includes the error message and the full sqlca. In this example, the backup failed because I specified a directory that does not exist.

There are a number of other syntax options on the LIST HISTORY command. Check out the IBM Db2 Knowledge Center entry on LIST HISTORY for more details.

Using SQL to Query Recovery History

As with most areas of Db2, we now have the ability to use SQL to access this data. SYSIBMADM.DB_HISTORY is the system view we use for this. For example, this query will return all locations of backups (at least where they were initally taken) on a server:

select distinct(location)
from sysibmadm.db_history 
where operation='B' 
and (sqlcode is null or sqlcode > 0)
with ur

LOCATION                                                                                                                                                                                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/db2home/db2inst1                                                                                                                                                                                                                                              
/db2home/db2inst1/db2inst1/NODE0000/SQL00001                                                                                                                                                                                                                   

  2 record(s) selected.

The full structure of this system view is available on the IBM Db2 Knowledge Center page on the DB_HISTORY view.

Pruning History and Recovery Objects

Setting REC_HIS_RETENTN will automatically delete entries from the history file after the specified number of days. The default value of 366 is far too large for nearly every database. One of the actions I take when building out a Db2 environment is often to set this at 60 or 90 days. What harm is there in a large recovery history file? Not much, but in my early days as a DBA, I actually encountered a bug that caused an instance crash when the history was over a certain size. That taught me to keep in it check.

Setting REC_HIS_RETENTN will only delete older history file entries from the history file. If you also want the number of database backups, transaction logs, and other recovery objects to be deleted from disk, you should consider setting AUTO_DEL_REC_OBJ to YES and ensuring your values for NUM_DB_BACKUPS is reasonable.

The history file can also be pruned manually using the PRUNE HISTORY command. If using that command, be careful with the use of the WITH FORCE keyword – using it could get you in trouble. To not just prune the history file, use the AND DELETE keyword to also remove recovery objects from disk.