List DB2 backups

Question

Is there a way to list the DB2 backups? All I can find is db2 list history backup all for <dbname> but I think you need to check through all of those to see if they've since been deleted. This seems like a simple question but I'm coming up blank.

Edit: I am using DB2 9.7 on AIX with TSM

Answer 1

Yes, that is how you would query for all the backups that were taken by your database.

If you don't set AUTO_DEL_REC_OBJ = ON for your database configuration (as well as NUM_DB_BACKUPS and REC_HIS_RETENTN) then, yes, it will show backups that were deleted from disk.

You can also use the prune command to clean out backup entries (ans their linked backup files if AUTO_DEL_REC_OBJ is set to ON).

db2 prune history <timestamp> [with force option] and delete

you can also prune archived logs as well.

db2 prune logfile prior to <logfile name>

Answer 2

using this SQL you could query in an easy manner the history file

SELECT start_time, entry_status, operation, operationtype, backup_id,  
       devicetype, OBJECTTYPE 
  FROM SYSIBMADM.DB_HISTORY 
 WHERE operation in ('B','R') 
 ORDER BY start_time DESC FETCH FIRST 30 ROWS ONLY  

if you're using tsm you could use

db2adutl query db DBNAME