The DB_HISTORY administrative view returns information from the history files from all database partitions.
The schema is SYSIBMADM.
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
When a data partitioned table is reorganized, one record for each reorganized data partition is returned. If only a specific data partition of a data partitioned table is reorganized, only a record the for the partition is returned.
SELECT DBPARTITIONNUM, EID, OPERATION, START_TIME, ENTRY_STATUS
FROM SYSIBMADM.DB_HISTORY
DBPARTITIONNUM EID OPERATION START_TIME ENTRY_STATUS
-------------- -------------------- --------- -------------- ------------
0 1 A 20051109185510 A
1 record(s) selected.
Column name | Data type | Description |
---|---|---|
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
EID | BIGINT | Number that uniquely identifies an entry in the history file. |
START_TIME | VARCHAR(14) | start_time - Event Start Time monitor element |
SEQNUM | SMALLINT | Positive integer which identifies the various parts of an image, such as sequences or sessions. |
END_TIME | VARCHAR(14) | Timestamp marking the end of a logged event. |
NUM_LOG_ELEMS | INTEGER | Number of log streams that will be returned in the lists of log stream data in the FIRSTLOG and LASTLOG columns. |
FIRSTLOG | VARCHAR(4000) | Name of the earliest transaction log associated with an event. |
LASTLOG | VARCHAR(4000) | Name of the latest transaction log associated with an event. |
BACKUP_ID | VARCHAR(24) | Backup identifier or unique table identifier. |
TABSCHEMA | VARCHAR(128) | table_schema - Table schema name monitor element |
TABNAME | VARCHAR(128) | table_name - Table name monitor element |
COMMENT | VARCHAR(254) | System-generated comment text
associated with a logged
event. Note: Only the first 30 characters are stored in the
history file
|
CMD_TEXT | CLOB(2 M) | Data definition language associated with a logged event. |
NUM_TBSPS | INTEGER | num_tbsps - Number of table spaces monitor element |
TBSPNAMES | CLOB(5 M) | Names of the table spaces associated with a logged event. |
OPERATION | CHAR(1) | Operation identifier. See Table 2 for possible values. |
OPERATIONTYPE | CHAR(1) | Action identifier for an operation. See Table 2 for possible values. |
OBJECTTYPE | CHAR(1) | Identifier for the target object of an operation. The possible values are: D for full database, I for index, P for table space, R for partitioned table, and T for table. |
LOCATION | VARCHAR(255) | Full path name for files, such as backup images or load input file, that are associated with logged events. |
DEVICETYPE | CHAR(1) | Identifier for the device type associated with a logged event. This field determines how the LOCATION field is interpreted. The possible values are: A for TSM, C for client, D for disk, F for snapshot backup, K for diskette, L for local, N (generated internally by DB2®), O for other (for other vendor device support), P for pipe, Q for cursor, R for remote fetch data, S for server, T for tape, U for user exit, and X for X/Open XBSA interface. |
ENTRY_STATUS | CHAR(1) | Identifier for the status of an entry in the history file. The possible values are: A for active, D for deleted (future use), E for expired, I for inactive, N for not yet committed, Y for committed or active. |
SQLCAID | VARCHAR(8) | An "eye catcher" for storage dumps containing 'SQLCA', as it appears in the SQLCAID field of the SQL communications area (SQLCA). |
SQLCABC | INTEGER | Length of the SQLCA, as it appears in the SQLCABC field of the SQLCA. |
SQLCODE | INTEGER | SQL return code, as it appears in the SQLCODE field of the SQLCA. |
SQLERRML | SMALLINT | Length indicator for SQLERRMC, as it appears in the SQLERRML field of the SQLCA. |
SQLERRMC | VARCHAR(70) | Contains one or more tokens, separated by X'FF', as they appear in the SQLERRMC field of the SQLCA. These tokens are substituted for variables in the descriptions of error conditions. |
SQLERRP | VARCHAR(8) | A three-letter identifier indicating the product, followed by five alphanumeric characters indicating the version, release, and modification level of the product, as they appear in the SQLERRP field of the SQLCA. |
SQLERRD1 | INTEGER | See SQLCA (SQL communications area). |
SQLERRD2 | INTEGER | See SQLCA (SQL communications area). |
SQLERRD3 | INTEGER | See SQLCA (SQL communications area). |
SQLERRD4 | INTEGER | See SQLCA (SQL communications area). |
SQLERRD5 | INTEGER | See SQLCA (SQL communications area). |
SQLERRD6 | INTEGER | See SQLCA (SQL communications area). |
SQLWARN | VARCHAR(11) | A set of warning indicators, each containing a blank or 'W'. See SQLCA (SQL communications area). |
SQLSTATE | VARCHAR(5) | A return code that indicates the outcome of the most recently executed SQL statement, as it appears in the SQLSTATE field of the SQLCA. |
Operation value | Operation value description | Operation type |
---|---|---|
A | Add table space | None |
B | Backup | Operation types are:
|
C | Load copy | None |
D | Dropped table | None |
F | Rollforward | Operation types are:
|
G | Reorganize table | Operation types are:
|
L | Load | Operation types are:
|
N | Rename table space | None |
O | Drop table space | None |
Q | Quiesce | Operation types are:
|
R | Restore | Operation types are:
|
T | Alter table space | Operation types are:
|
U | Unload | None |
X | Archive logs | Operation types are:
|