DB2 10.5 for Linux, UNIX, and Windows

LIST HISTORY command

Lists entries in the database history records. The database history records contain a record of recovery and administrative events. Recovery events include full database and table space level backup, incremental backup, restore, and rollforward operations. Additional logged events include create, alter, drop, or rename table space, reorganize table, drop table, and load.

The LIST HISTORY command only returns history information for the database partition it is issued on. To list the history on multiple partitions, you can either issue the LIST HISTORY command from each individual database partition, or use the db2_all prefix to run the LIST HISTORY command on all database partitions

Authorization

None

Required connection

Instance. You must attach to any remote database in order to run this command against it. For a local database, an explicit attachment is not required.

Command syntax


>>-LIST HISTORY--+-------------------+-------------------------->
                 +-BACKUP------------+   
                 +-ROLLFORWARD-------+   
                 +-DROPPED TABLE-----+   
                 +-LOAD--------------+   
                 +-CREATE TABLESPACE-+   
                 +-ALTER TABLESPACE--+   
                 +-RENAME TABLESPACE-+   
                 +-REORG-------------+   
                 '-ARCHIVE LOG-------'   

>--+-ALL--------------------------------+----------------------->
   +-SINCE--timestamp-------------------+   
   '-CONTAINING--+-schema.object_name-+-'   
                 '-object_name--------'     

>--FOR--+----------+--database-alias---------------------------><
        +-DATABASE-+                   
        '-DB-------'

Command parameters

HISTORY: Lists all events that are currently logged in the database history records.
BACKUP: Lists backup and restore operations.
ROLLFORWARD: Lists rollforward operations.
DROPPED TABLE: Lists dropped table records. A dropped table record is created only when the table is dropped and the table space containing it has the DROPPED TABLE RECOVERY option enabled. Returns the CREATE TABLE syntax for partitioned tables and indicates which table spaces contained data for the table that was dropped.
LOAD: Lists load operations.
CREATE TABLESPACE: Lists table space create and drop operations.
RENAME TABLESPACE: Lists table space renaming operations.
REORG: Lists reorganization operations. Includes information for each reorganized data partition of a partitioned table.
ALTER TABLESPACE: Lists alter table space operations.
ARCHIVE LOG: Lists archive log operations and the archived logs.
ALL: Lists all entries of the specified type in the database history records.
SINCE timestamp: A complete time stamp (format yyyymmddhhmmss), or an initial prefix (minimum yyyy) can be specified. All entries with time stamps equal to or greater than the time stamp provided are listed.
CONTAINING schema.object_name: This qualified name uniquely identifies a table.
CONTAINING object_name: This unqualified name uniquely identifies a table space.
FOR DATABASE database-alias: Used to identify the database whose recovery database history records are to be listed.

Examples

The following examples show different uses of the LIST HISTORY command:

   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

The following sample output shows two entries, one for a Load (L) operation and another one for a backup (B) operation:

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

The following sample output shows one entry for the reorganization reclaim operation:

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

Use the db2_all prefix to run the LIST HISTORY command on all database partitions:

db2_all "db2 list history since 20010601 for sample"

Example 4

The following is an example of DB history records in a DB2® pureScale® environment.

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

The following is an example of DB history records outside of a DB2 pureScale environment.

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

Usage notes

The SYSIBMADM.DB_HISTORY administrative view can be used to retrieve data from all database partitions.

In a DB2 pureScale instance, all DB history records for the database are global. The DB history records can be retrieved using the list history or administrative view interface connected to any member.

The report generated by this command contains the following symbols:

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