Clean up the archiving logs of DB2

The information related to the DB2 archive log is saved in the recovery history file. You can run the db2 list history command to view the information. Db2inst1 @ suse-db: ~> Db2 list history archive log all for ggyy List History File for ggyy Number of matching file entries = 6 Op Obj Timestamp + Sequence Type Dev Earliest Log Current Log Backup ID ------------------------------------------------------ -------------- x d 20100103235553 1 D S0000038.LOG C0000007 ------------------------------------------------------------------------------------------------------- ----------------------------------------------- Comment: Start Time: 20100103235553 End Time: 20100103235553 Status: A www.2cto.com ---------------------------------------------------------------------------- EID: 80 Location: /data/palog/db2inst1/GGYY/NODE0000/C0000007/S0000038.LOG Op Obj Timestamp + Sequence Type Dev Earliest Log Current Log Backup ID --------------------------------------- --- ------------ -------------- X d 20100103235553 N S0000038.LOG Comment: archive log Start Time: 20100103235553 End Time: 20100103235553 Status: A Comment EID: 81 Op Obj Times Tamp + Sequence Type Dev Earliest Log Current Log Backup ID -- --- -------------------- ---- ------------ ------------------ x d 20100103235608 1 D S0000039.LOG C0000007 Comment: Start Time: 20100103235608 End Time: 20100103235608 Status: A required EID: 82 Location:/data/palog/db2inst1/GGYY/NODE0000/C0000007/S0000039.LOG ...... to clear this information, you need to use the db2 prune history command. This command will clear the specified Timestamp and all previous entries (not just archived logs ). By default, the system does not clear the archived log information after the last full backup of the database. Db2inst1 @ suse-db: ~> Db2 prune history 20100103235553 SQL1024N A database connection does not exist. SQLSTATE = 00000 db2inst1 @ suse-db: ~> Db2 connect to ggyy www.2cto.com Database Connection Information Database server = DB2/LINUX 9.7.0 SQL authorization ID = DB2INST1 Local database alias = GGYY db2inst1 @ suse-db: ~> Db2 prune history 20100103235553 DB20000I The PRUNE command completed successfully. db2inst1 @ suse-db: ~> Db2 list history archive log all for ggyy | head-20 List History File for ggyy Number of matching file entries = 6 Op Obj Timestamp + Sequence Type Dev Earliest Log Current Log Backup ID ----------------------- ---- --- ------------ -------------- x d 20100103235553 1 D S0000038.LOG C0000007 certificate -------------------------------------------------------------------------------------------- Comment: Start Time: 20100103235553 End Time: 20100103235553 Status: A previous EID: 80 Location:/data/palog/db2inst1/GGYY/NODE0000/C0000007/S0000038.LOG db2inst1 @ suse-db: ~> Db2 list history backup all for ggyy List History File for ggyy Number of matching file entries = 0 perform a full backup for the database and try to clear it again: db2inst1 @ suse-db: ~> Db2 backup db ggyy online to/data/backup Backup successful. The timestamp for this backup image is: 20100104004121 db2inst1 @ suse-db: ~> Db2 connect to ggyy www.2cto.com Database Connection Information Database server = DB2/LINUX 9.7.0 SQL authorization ID = DB2INST1 Local database alias = GGYY db2inst1 @ suse-db: ~> Db2 prune history 20100103235553 DB20000I The PRUNE command completed successfully. db2inst1 @ suse-db: ~> Db2 list history archive log all for ggyy | head-20 List History File for ggyy Number of matching file entries = 5 Op Obj Timestamp + Sequence Type Dev Earliest Log Current Log Backup ID ----------------------- ---- --- ------------ -------------- x d 20100103235608 1 D S0000039.LOG C0000007 certificate -------------------------------------------------------------------------------------------- Comment: Start Time: 20100103235608 End Time: 20100103235608 Status: A Comment EID: 82 Location:/data/palog/db2inst1/GGYY/NODE0000/C0000007/S0000039.LOG use the and delete parameter, you can delete the corresponding archived log files while clearing the entries. Db2inst1 @ suse-db: ~> Ls-l/data/palog/db2inst1/GGYY/NODE0000/C0000007 total 48-rw-r ----- 1 db2inst1 db2iadm1 12288 2010-01-03 23:55 minutes-rw-r ----- 1 db2inst1 db2iadm1 12288 2010-01-03 23:56 s0000039.LOG-rw-r ----- 1 db2inst1 db2iadm1 12288 2010-01-03 23:56 S0000040.LOG-rw-r ----- 1 db2inst1 db2iadm1 12288 2010-01-04 00:41 S0000041.LOG db2inst1 @ suse-db: ~> Db2 prune history 20100103235608 and delete DB20000I The PRUNE command completed successfully. www.2cto.com db2inst1 @ suse-db: ~> Db2 list history archive log all for ggyy | head-20 List History File for ggyy Number of matching file entries = 3 Op Obj Timestamp + Sequence Type Dev Earliest Log Current Log Backup ID ----------------------- ---- --- ------------ -------------- x d 20100103235618 1 D S0000040.LOG C0000007 certificate -------------------------------------------------------------------------------------------- Comment: Start Time: 20100103235618 End Time: 20100103235618 Status: A previous EID: 84 Location:/data/palog/db2inst1/GGYY/NODE0000/C0000007/S0000040.LOG db2inst1 @ suse-db: ~> Ls-l/data/palog/db2inst1/GGYY/NODE0000/C0000007 total 36-rw-r ----- 1 db2inst1 db2iadm1 12288 2010-01-03 23:55 minutes-rw-r ----- 1 db2inst1 db2iadm1 12288 2010-01-03 23:56 s0000040.LOG-rw-r ----- 1 db2inst1 db2iadm1 12288 2010-01-04 41 S0000041.LOG can be seen, the archived Log File S0000039.LOG has been deleted, but S0000038.LOG still exists because the related entries of S0000038.LOG have been cleared. Try to clear both S0000040.LOG and S0000041.LOG: db2inst1 @ suse-db: ~> Db2 list history archive log all for ggyy | tail-15 Op Obj Timestamp + Sequence Type Dev Earliest Log Current Log Backup ID -- --- snapshot ---- --- -------------- x d 20100104004127 1 D S0000041.LOG C0000007 ------------------------------------------------------------------------------ Co Mment: Start Time: 20100104004127 End Time: 20100104004128 Status: A www.2cto.com region EID: 86 Location:/data/palog/db2inst1/GGYY/NODE0000/C0000007/S0000041.LOG db2inst1 @ suse-db: ~> Db2 prune history 20100104004127 and delete DB20000I The PRUNE command completed successfully. db2inst1 @ suse-db: ~> Db2 list history archive log all for ggyy | head-20 List History File for ggyy Number of matching file entries = 1 Op Obj Timestamp + Sequence Type Dev Earliest Log Current Log Backup ID ----------------------- ---- --- ------------ -------------- x d 20100104004127 1 D S0000041.LOG C0000007 certificate -------------------------------------------------------------------------------------------- ------------------------------------------------------------ Comment: Start Time: 20100104004127 End Time: 20100104004128 Status: A www.2cto.com region EID: 86 Location: /data/palog/db2inst1/GGYY/NODE0000/C0000007/S0000041.LOG db2inst1 @ suse-db: ~> Ls-l/data/palog/db2inst1/GGYY/NODE0000/C0000007/total 24-rw-r ----- 1 db2inst1 db2iadm1 12288 2010-01-03 55 S0000038.LOG-rw-r ----- 1 db2inst1 db2iadm1 12288 2010-01-04 00:41 S0000041.LOG S0000041.LOG still exists, because it is generated after full backup. You can use the with force option parameter to forcibly clear the object. Db2inst1 @ suse-db: ~> Db2 prune history 20100104004127 with force option and delete DB20000I The PRUNE command completed successfully. db2inst1 @ suse-db: ~> Db2 list history archive log all for ggyy | head-20 List History File for ggyy Number of matching file entries = 0 db2inst1 @ suse-db: ~> Ls-l/data/palog/db2inst1/GGYY/NODE0000/C0000007/total 12 www.2cto.com-rw-r ----- 1 db2inst1 db2iadm1 12288 2010-01-03 23:55 S0000038.LOG note, here, the database full backup entries are also deleted, but the backup file is still in: db2inst1 @ suse-db: /data/backup> db2 list history backup all for ggyy List History File for ggyy Number of matching file entries = 0 db2inst1 @ suse-db: /data/backup> ls-l/data/backup total 99994-rw ------- 1 db2inst1 db2iadm1 10229 3504 2010-01-04 GGYY.0.db2inst1. node).catn0000.20100104004121.001 In addition, you can use the db2 prune logfile command to clear online archived log files in the active log directory, but the offline archived log files are invalid. Author: jackytang520