Lists entries in the history file. The history file contains a record of recovery and administrative events. Recovery events include full database and table space level backup, incremental backup, merged 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.
None
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.
>>-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-------'
db2 list history since 19980201 for sample
db2 list history backup containing userspace1 for sample
db2 list history dropped table all for db sample
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
db2_all "db2 list history since 20010601 for sample"
The SYSIBMADM.DB_HISTORY administrative view can be used to retrieves data from all database partitions.
Operation
A - Create table space
B - Backup
C - Load copy
D - Dropped table
F - Roll forward
G - Reorganize table
L - Load
N - Rename table space
O - Drop table space
Q - Quiesce
R - Restore
T - Alter table space
U - Unload
X - Archive log
Type
Archive Log types:
P - Primary log path
M - Secondary (mirror) log path
N - Archive log command
F - Failover archive path
1 - Primary log archive method
2 - Secondary log archive method
Backup types:
F - Offline
N - Online
I - Incremental offline
O - Incremental online
D - Delta offline
E - Delta online
R - Rebuild
M - Merged
Rollforward types:
E - End of logs
P - Point in time
Load types:
I - Insert
R - Replace
Alter table space types:
C - Add containers
R - Rebalance
Quiesce types:
S - Quiesce share
U - Quiesce update
X - Quiesce exclusive
Z - Quiesce reset