LIST HISTORY 命令

LIST HISTORY 命令列出数据库历史记录中的条目。数据库历史记录包含恢复和管理事件的记录。恢复事件包括完全数据库和表空间级别备份，增量备份，复原和前滚操作。其他记录的事件包括创建，改变，删除或重命名表空间，重组表，删除表和装入。

LIST HISTORY 命令仅返回对其发出的数据库分区的历史记录信息。要列出多个分区上的历史记录，可以从每个单独的数据库分区发出 LIST HISTORY 命令，也可以使用 db2_all 前缀在所有数据库分区上运行 LIST HISTORY 命令

权限

无

需要的连接

。必须连接到任何远程数据库才能对其运行此命令。对于本地数据库，不需要显式连接。

命令语法

命令参数

HISTORY: 列出当前记录在数据库历史记录中的所有事件。
BACKUP: 列出备份和复原操作。
ROLLFORWARD: 列出前滚操作。
DROPPED TABLE: 列出已删除的表记录。仅当删除了表并且包含该表的表空间启用了 "已删除表恢复" 选项时，才会创建已删除的表记录。返回分区表的 CREATE TABLE 语法，并指示哪些表空间包含已删除的表的数据。
LOAD: 列出装入操作。
CREATE TABLESPACE: 列示表空间创建和删除操作。
RENAME TABLESPACE: 列示表空间重命名操作。
REORG: 列出重组操作。包括分区表的每个重组数据分区的信息。
ALTER TABLESPACE: 列示变更表空间操作。
ARCHIVE LOG: 列出归档日志操作和归档日志。
ALL: 列出数据库历史记录中指定类型的所有条目。
SINCE timestamp: 可以指定完整的时间戳记 (格式 yyyymmddhhmmss) 或初始前缀 (最小 yyyy)。列出时间戳记等于或大于提供的时间戳记的所有条目。
CONTAINING schema.object_name: 此限定名唯一地标识表。
CONTAINING object_name: 此非限定名唯一地标识表空间。
FOR DATABASE database-alias: 用于标识要列示其恢复数据库历史记录的数据库。

示例

以下示例显示了 LIST HISTORY 命令的不同用途:

   db2 list history since 19980201 for sample
   db2 list history backup containing userspace1 for sample
   db2 list history dropped table all for db sample

Example 1

以下样本输出显示了两个条目，一个用于 Load (L) 操作，另一个用于 backup (B) 操作:

db2 list history all for SAMPLE

                    List History File for sample

Number of matching file entries = 2

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  L  T  20100106133005001   R    S  S0000000.LOG S0000000.LOG  
 ----------------------------------------------------------------------------
  "USERNAME"."T1" resides in 1 tablespace(s):

  00001 USERSPACE1                                                            
 ----------------------------------------------------------------------------
    Comment: DB2                                                              
 Start Time: 20100106133005
   End Time: 20100106133006
     Status: A
 ----------------------------------------------------------------------------
  EID: 3 Location: /home/hotel19/username/mydatafile.del
 ----------------------------------------------------------------------------
SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: 3107   sqlerrml: 0

 sqlerrmc: 
 sqlerrp : SQLUVLD
 sqlerrd : (1) -2146107283      (2) 0                (3) 0
           (4) 0                (5) 0                (6) 0
 sqlwarn : (1) W    (2)      (3)      (4)        (5)       (6)
           (7)      (8)      (9)      (10)       (11)
 sqlstate:


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20100106135509001   F    D  S0000000.LOG S0000000.LOG  
 ----------------------------------------------------------------------------
  Contains 2 tablespace(s):

  00001 SYSCATSPACE                                                           
  00002 USERSPACE1                                                            
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP SAMPLE OFFLINE                                        
 Start Time: 20100106135509
   End Time: 20100106135512
     Status: A
 ----------------------------------------------------------------------------
  EID: 4 Location: /home/hotel19/username

Example 2

以下样本输出显示重组回收操作的一个条目:

db2 -v "list history reorg all for wsdb"

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  G  T  20080924101408      N       S0000000.LOG S0000000.LOG
 ----------------------------------------------------------------------------
  Table:  "ZHMFENG "."T1"

 ----------------------------------------------------------------------------
    Comment: REORG RECLAIM
 Start Time: 20080924101408
   End Time: 20080924101409
     Status: A

Example 3

使用 db2_all 前缀在所有数据库分区上运行 LIST HISTORY 命令:

db2_all "db2 list history since 20010601 for sample"

Example 4

以下是 Db2® pureScale® 环境中的数据库历史记录示例。

db2 list history since 20091020163200 for database sample

 Op Obj Timestamp+Sequence Type Dev Backup ID
 -- --- ------------------ ---- --- --------------
  X  D  20091020163218      1    D  
 ----------------------------------------------------------------------------

 ----------------------------------------------------------------------------
 Log Stream ID Earliest Log Current Log
 ------------- ------------ ------------
             3 S0000023.LOG C0000000    
 ----------------------------------------------------------------------------
    Comment:                                                                  
 Start Time: 20091020163218
   End Time: 20091020163245
     Status: A
 ----------------------------------------------------------------------------
 EID: 28 Location: /notnfs/billings/arch_logs/billings/SAMPLE/NODE0000/LOGSTREAM0002/C0000000/S0000023.LOG


 Op Obj Timestamp+Sequence Type Dev Backup ID
 -- --- ------------------ ---- --- --------------
  X  D  20091020163219      1    D  
 ----------------------------------------------------------------------------

 ----------------------------------------------------------------------------
 Log Stream ID Earliest Log Current Log
 ------------- ------------ ------------
             0 S0000001.LOG C0000000    
 ----------------------------------------------------------------------------
    Comment:                                                                  
 Start Time: 20091020163219
   End Time: 20091020163257
     Status: A
 ----------------------------------------------------------------------------
 EID: 29 Location: /notnfs/billings/arch_logs/billings/SAMPLE/NODE0000/LOGSTREAM0000/C0000000/S0000001.LOG

Example 5

以下是 Db2 pureScale 环境外部的数据库历史记录示例。

db2 list history since 20091020155300 for database sample

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  X  D  20091020155341      1    D  S0000004.LOG C0000000      
 ----------------------------------------------------------------------------

 ----------------------------------------------------------------------------
    Comment:                                                                  
 Start Time: 20091020155341
   End Time: 20091020155345
     Status: A
 ----------------------------------------------------------------------------
EID: 9 Location: /notnfs/billings/arch_logs/billings/SAMPLE/NODE0000/LOGSTREAM0000/C0000000/S0000004.LOG

使用说明

SYSIBMADM.DB_HISTORY 管理视图可用于从所有数据库分区检索数据。

在 Db2 pureScale 实例中，数据库的所有数据库历史记录都是全局的。可以使用连接到任何成员的列表历史记录或管理视图接口来检索数据库历史记录。

此命令生成的报告包含以下符号:

Operation

   A - Create table space
   B - Backup
   C - Load copy
   D - Drop table
   F - Rollforward
   G - Reorganize
   L - Load
   N - Rename table space
   O - Drop table space
   Q - Quiesce
   R - Restore
   T - Alter table space
   U - Unload
   X - Archive log

Object

   D - Database
   I - Index
   P - Table space
   T - Table
   R - Partitioned table

Type

   Alter table space operation types:

      C - Add container
      R - Rebalance

   Archive log operation types:
      F - Failover archive path
      M - Secondary (mirror) log path
      N - Archive log command
      P - Primary log path
      1 - Primary log archive method
      2 - Secondary log archive method

   Backup and restore operation types:

      D - Delta offline
      E - Delta online
      F - Offline
      I - Incremental offline
      M - Merged
      N - Online
      O - Incremental online
      R - Rebuild
   
   Load operation types:

      I - Insert
      R - Replace

   Rollforward operation types:

      E - End of logs
      P - Point-in-time

   Quiesce operation types:

      S - Quiesce share
      U - Quiesce update
      X - Quiesce exclusive
      Z - Quiesce reset

History entry status flag:

   A - Active
   D - Deleted
   E - Expired
   I - Inactive
   N - Not yet committed
   P - Pending delete
   X - Do not delete
   a - Incomplete active
   i - Incomplete inactive