DB_HISTORY administrative view - Retrieve history file information
The DB_HISTORY administrative view returns information from the history files from all database partitions.
The schema is SYSIBMADM.
Authorization
One of the following authorizations is required:
- SELECT privilege on the DB_HISTORY administrative view
- CONTROL privilege on the DB_HISTORY administrative view
- DATAACCESS authority
- DBADM authority
- SQLADM authority
- ACCESSCTRL authority
- SECADM authority
Default PUBLIC privilege
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
Usage note
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.
Example
Select the database partition number, entry ID, operation, start time, and status
information from the database history files for all the database partitions of the
database to which the client is currently connected.
SELECT DBPARTITIONNUM, EID, OPERATION, START_TIME, ENTRY_STATUS
FROM SYSIBMADM.DB_HISTORYThe following is an example of output for this query.
DBPARTITIONNUM EID OPERATION START_TIME ENTRY_STATUS
-------------- -------------------- --------- -------------- ------------
0 1 A 20051109185510 A
1 record(s) selected.
Information returned
| 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 files, that are associated with logged events. In the case of CLI LOAD or LOAD from CURSOR, the location is an SQL statement. |
| DEVICETYPE | CHAR(1) | Identifier for the device type associated with a logged event. This
field determines how the LOCATION field is interpreted:
|
| ENTRY_STATUS | CHAR(1) | Identifier for the status of an entry in the history file:
|
| 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:
|