DB2 – Backup history

Question: How do I return DB2 BACKUP and DB2 RESTORE history?  One requirement is to view the data  for troubleshooting , the other requirement is to include in a daily report – for distribution to the DBA team

Answer: Use either LIST HISTORY or or SYSIBMADM.DB_HISTORY Administrative View.

Both methods have their benefits. The usage will depend on  what information and in what format . I’ve included some simple examples. For full detail on all information  returned and OPERATION \ OPERATION TYPES , refer to the DB2 documentation

Method 1

Use the LIST HISTORY command

Some  examples:

# Return history of a database of ALL types
db2 list history ALL  for db  mydb

# Return history of type BACKUP of a certain database since a certain date
db2 list history BACKUP since 20120101 for db mydb 

# Return history of a ARCHIVE LOG for a certain database 
db2 list history ARCHIVE LOG ALL for db mydb

Method 2

Use the SYSIBMADM.DB_HISTORY Administrative View

#Return history of  an OPERATION = BACKUP as OFFLINE (OPERATIONTYPE = F)
select START_TIME,OPERATION from SYSIBMADM.DB_HISTORY WHERE OPERATION = 'B' AND OPERATIONTYPE = 'F'

#Return history of  an OPERATION = RESTORE as OFFLINE (OPERATIONTYPE = F)
select START_TIME,OPERATION from SYSIBMADM.DB_HISTORY WHERE OPERATION = 'R' AND OPERATIONTYPE = 'F'"

 Related Posts

DB2 – check backup command and contents of a database

DB2 – Restore database from a ONLINE backup