DB2中自动清理数据库历史文件

DB2可以做到自动清除历史文件，有关详细介绍请参考

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.ha.doc/doc/t0051348.html?cp=SSEPGG_9.7.0&lang=en

这里只举一个例子：

C:\windows\system32>db2 update db cfg for sample using rec_his_retentn 1

C:\windows\system32>db2 update db cfg for sample using NUM_DB_BACKUPS 1

//重新激活数据库

C:\windows\system32>db2 list history all for db sample


                    List History File for sample


Number of matching file entries = 0




测试1.） 备份次数超过NUM_DB_BACKUPS，但时间上未达到rec_his_retentn，不会发生自动prune


C:\windows\system32>db2 "alter tablespace userspace1 rebalance"


C:\windows\system32>db2 "reorg table t1"


C:\windows\system32>db2 "backup db sample online"
Backup successful. The timestamp for this backup image is :
20151023133140


C:\windows\system32>db2 "load from t1.del of del insert into t1"


C:\windows\system32>db2 "backup db sample online"
Backup successful. The timestamp for this backup image is :
20151023133319


C:\windows\system32>db2 list history all for db sample


                    List History File for sample


Number of matching file entries = 5 <-NUM_DB_BACKUPS 达到要求, 但rec_his_retentn没有达到要求，没有prune


..




测试2.）1天之后，观察是否发生了自动prune


C:\windows\system32>db2 list history all for db sample


                    List History File for sample


Number of matching file entries = 5 <--rec_his_retentn 和 NUM_DB_BACKUPS 都达到要求, 但没有 backup 操作. 没有发生prune






测试3.）再次发出backup命令，这次的backup命令触发了之前的历史文件条目被删除


C:\windows\system32>db2 "reorg table employee"
DB20000I  The REORG command completed successfully.


C:\windows\system32>db2 backup db sample online


Backup successful. The timestamp for this backup image is :
20151024134000


C:\windows\system32>db2 list history all for db sample


                    List History File for sample


Number of matching file entries = 2   <--已经被自动prune


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------
--------------
  G  T  20151024133939      F       S0000530.LOG S0000530.LOG
 -----------------------------------------------------------------------
-----
  Table:  "MIAOQINGSONG"."EMPLOYEE"


 -----------------------------------------------------------------------
-----
    Comment: REORG
 Start Time: 20151024133939
   End Time: 20151024133939
     Status: A
 -----------------------------------------------------------------------
-----
  EID: 90


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------
--------------
  B  D  20151024134000001   N    D  S0000531.LOG S0000531.LOG
 -----------------------------------------------------------------------
-----
  Contains 3 tablespace(s):


 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 -----------------------------------------------------------------------
-----
    Comment: DB2 BACKUP SAMPLE ONLINE
 Start Time: 20151024134000
   End Time: 20151024134007
     Status: A
 -----------------------------------------------------------------------
-----

  EID: 91 Location: C:\windows\system32

这里需要注意一点：

No matter how small the retention period, the most recent full database backup plus its restore set is always kept, unless you use the PRUNEcommand with the FORCE option.