DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
The DB_HISTORY administrative view returns information from the history files from all database partitions.
You can use the PRUNE HISTORY command on database partitions to reduce the amount of information returned by the DB_HISTORY view. You can also use the LIST HISTORY command to retrieve history information for select database partitions.
The schema is SYSIBMADM.
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_HISTORYDBPARTITIONNUM EID OPERATION START_TIME ENTRY_STATUS
-------------- -------------------- --------- -------------- ------------
0 1 A 20051109185510 A
1 record(s) selected. | Column name | Data type | Description |
|---|---|---|
| DBPARTITIONNUM | SMALLINT | Database partition number. |
| 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 | Sequence number. |
| END_TIME | VARCHAR(14) | Timestamp marking the end of a logged event. |
| FIRSTLOG | VARCHAR(254) | Name of the earliest transaction log associated with an event. |
| LASTLOG | VARCHAR(254) | 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. |
| CMD_TEXT | CLOB(2 M) | Data definition language associated with a logged event. |
| NUM_TBSPS | INTEGER | Number of table spaces associated with a logged event. |
| 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 range partition 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:
|