01 August,2012 by Jack Vamvas
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
Use the LIST HISTORY command
# 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
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'"