HISTORY_LOG_INFO table function
The HISTORY_LOG_INFO table function returns one row for each message in the history log based on the timestamp range specified. It returns information similar to what is returned by the Display Log (DSPLOG) CL command and the Open List of History Log Messages (QMHOLHST) API.
Authorization: None required.
- start-time
- A timestamp expression that indicates the starting timestamp to use when returning history log
information.
If this parameter is omitted, the default of CURRENT DATE - 1 DAY is used.
- end-time
- A timestamp expression that indicates the ending timestamp to use when returning history log
information.
If this parameter is omitted, the default of '9999-12-30-00.00.00.000000' is used.
- generate-syslog
- A character or graphic string expression that indicates whether to transform history log
messages into syslog formatted detail. Values are:
- NO
- No syslog information will be returned. The SYSLOG_EVENT, SYSLOG_FACILITY, SYSLOG_SEVERITY, and SYSLOG_PRIORITY columns will contain the null value.
- RFC3164
- Values will be returned for the SYSLOG_EVENT, SYSLOG_FACILITY, SYSLOG_SEVERITY, and SYSLOG_PRIORITY columns for each history log message. The SYSLOG_EVENT column will contain a syslog header that matches the RFC3164 format as described by the Internet Engineering Task Force (IETF) Request For Comments (RFC) 3164.
- RFC5424
- Values will be returned for the SYSLOG_EVENT, SYSLOG_FACILITY, SYSLOG_SEVERITY, and SYSLOG_PRIORITY columns for each history log message. The SYSLOG_EVENT column will contain a syslog header that matches the RFC5424 format as described by the Internet Engineering Task Force (IETF) Request For Comments (RFC) 5424.
If generate-syslog is not specified or is the null value, NO is used.
- eof-delay
- An integer expression that specifies the number of seconds to sleep when all history log
messages have been read. This delay allows the caller to establish a polling service that will
continually return rows, sleeping for the specified interval whenever all messages have been
processed.
A value of zero indicates no delay is used and a finite set of rows will be returned. A value greater than zero indicates that the table function will sleep, as needed, to wait for new history log messages and never end. If eof-delay is not specified or is the null value, zero is used.
If this parameter has a value greater than zero, the generate-syslog parameter must be RFC3164 or RFC5424, and the end-time parameter cannot be specified with a value other than its default value.
When using a non-zero eof-delay parameter, avoid using query clauses that depend on returning a finite number of rows. For example, using the FETCH FIRST n ROWS clause can cause the query to end when the requested number of rows has been satisfied. A query using the HISTORY_LOG_INFO function with a non-zero eof-delay parameter does not allow data to be copied (ALWCPYDTA(*NO)). This means that a query requiring a copy of data, such as one using an ORDER BY clause or UNION DISTINCT, will issue an error and not be allowed. When using eof-delay, consider using a simple query to avoid blocking of rows. When rows are blocked for data transport efficiency, rows won't be returned until the block is full. Therefore, you should decide whether you favor data transport efficiency or moving events as soon as they occur.
The result of the function is a table containing multiple rows with the format shown in the following table. All the columns are nullable.
Column Name | Data Type | Description |
---|---|---|
ORDINAL_POSITION | INTEGER | A unique number for each row that indicates the time order of messages in the job log. The first (oldest) message returned from the history log will have a value of 1. Subsequent messages will have a value one greater than the previous message. Since these values are assigned when this catalog is queried, there will be no gaps in values. |
MESSAGE_ID | VARCHAR(7) | The message ID for this message. Contains the null value if this is an impromptu message or MESSAGE_TYPE is REPLY. |
MESSAGE_TYPE | VARCHAR(13) | Type of message.
|
MESSAGE_SUBTYPE | VARCHAR(22) | Subtype of message. The values returned for REPLY messages:
The value returned for some REQUEST messages:
Contains the null value for other message types. |
SEVERITY | SMALLINT | The severity assigned to the message. |
MESSAGE_TIMESTAMP | TIMESTAMP | The timestamp when the message was sent. |
FROM_USER | VARCHAR(10) | The current user of the job when the message was sent. |
FROM_JOB | VARCHAR(28) | The qualified job name when the message was sent. |
FROM_JOB_NAME | VARCHAR(10) | The name of the job when the message was sent. |
FROM_JOB_USER | VARCHAR(10) | The user profile that started the job when the message was sent. |
FROM_JOB_NUMBER | VARCHAR(6) | The job number of the job when the message was sent. |
FROM_PROGRAM | VARCHAR(10) | The program that sent the message. |
MESSAGE_LIBRARY | VARCHAR(10) | The name of the library containing
the message file. Contains the null value if MESSAGE_ID is null. |
MESSAGE_FILE | VARCHAR(10) | The message file containing the
message. Contains the null value if MESSAGE_ID is null. |
MESSAGE_TOKENS | VARCHAR(4096) FOR BIT DATA | The message token string. If the value is longer than 4096
characters, it will be truncated with no warning. Contains the null value if there are no message tokens. |
MESSAGE_TEXT | VARGRAPHIC(1024) CCSID 1200 | The first level text of the message
including tokens, or the impromptu message text. Contains the null value if MESSAGE_ID is null or if the message file could not be accessed. |
MESSAGE_SECOND_LEVEL_TEXT | VARGRAPHIC(4096) CCSID 1200 | The second level text of the message
including tokens. Contains the null value if MESSAGE_ID is null or if the message has no second level text or if the message file could not be accessed. |
SYSLOG_EVENT | VARGRAPHIC(2048) CCSID 1200 | The Common Event Format (CEF) syslog event for the message
preceded by a header of the requested type. If a header-type of RFC3164 is requested, the maximum
length is 1024 characters. If a header-type of RFC5424 is requested, the maximum length is 2048
characters. The string will be truncated with no warning if it exceeds the maximum length. The key names returned for history log information are listed in the Notes® section. Contains the null value if NO was specified for the GENERATE_SYSLOG parameter. |
SYSLOG_FACILITY | INTEGER | The syslog facility assigned to the event.
The facility assigned is defined in the Notes section. Contains the null value if NO was specified for the GENERATE_SYSLOG parameter. |
SYSLOG_SEVERITY | INTEGER | The syslog severity assigned to the event.
The severity assigned is listed in the Notes section. Contains the null value if NO was specified for the GENERATE_SYSLOG parameter. |
SYSLOG_PRIORITY | INTEGER | The syslog priority number assigned to the event. Contains the null value if NO was specified for the GENERATE_SYSLOG parameter. |
Notes
Syslog information: Syslog information is returned for all messages in the history log. Syslog information is also available for audit journal entries. See DISPLAY_JOURNAL table function for more details.
- Severity 1 Alert: Action must be taken immediately
- MESSAGE_TYPE contains a value of INQUIRY, NOTIFY, or REPLY
- Severity 3 Error condition
- MESSAGE_ID contains a value of CPF1164 with a job ending code value in the MESSAGE_TEXT column of 30 or higher
- MESSAGE_TYPE contains a value of ESCAPE when the SEVERITY column contains a value of 50 or greater
- Severity 4 Warning condition
- MESSAGE_ID contains a value of CPF1393. The SYSLOG_FACILITY column is set to 4.
- MESSAGE_ID contains a value of CPF1164 with a job ending code value in the MESSAGE_TEXT column of 20
- MESSAGE_TYPE contains a value of ESCAPE when the SEVERITY column contains a value of 30 or greater but less than 50
- Severity 5 Notice: A normal but significant condition
- MESSAGE_ID contains a value of CPF1164 with a job ending code value in the MESSAGE_TEXT column of 10
- MESSAGE_TYPE contains a value of INFORMATIONAL, COMPLETION, DIAGNOSTIC, or REQUEST when the SEVERITY column contains a value of 50 or greater
- Severity 6 Informational message
- MESSAGE_ID contains a value of CPF1164 with a job ending code value in the MESSAGE_TEXT column of 0
- MESSAGE_TYPE contains a value of ESCAPE when the SEVERITY column contains a value less than 30
- MESSAGE_TYPE contains a value of SENDER
- MESSAGE_TYPE contains a value of INFORMATIONAL, COMPLETION, DIAGNOSTIC, or REQUEST when the SEVERITY column contains a value less than 50
- Severity 7 Debug level message
- MESSAGE_ID contains a value of CPF9897 or CPF9898 (regardless of severity or message type)
Common Event Format key name | Description |
---|---|
msg | The message text (MESSAGE_TEXT column) from the history log message |
reason | Text description of the history log message |
sproc | The qualified job name (FROM_JOB column) from the history log message |
suser | Current user name (FROM_USER column) from the history log message |
Examples
- Return a list of history log messages for all of yesterday and
today.
SELECT * FROM TABLE(QSYS2.HISTORY_LOG_INFO()) X
- Return a list of all history log messages for the last 24 hours.
SELECT * FROM TABLE(QSYS2.HISTORY_LOG_INFO(CURRENT TIMESTAMP - 1 DAY)) X
- Return history log information since the last IPL, assuming that
the last IPL timestamp is in a global variable named LAST_IPL_TIME.
SELECT * FROM TABLE(QSYS2.HISTORY_LOG_INFO(LAST_IPL_TIME, CURRENT TIMESTAMP)) A
- Return syslog information formatted with an RFC3164 header for all history log messages from the
start of today forward into the future. When all history log messages have been returned to the
caller, the query will pause for 5 minutes (300 seconds) before checking again for
messages.
SELECT syslog_facility, syslog_severity, syslog_event FROM TABLE (QSYS2.HISTORY_LOG_INFO(START_TIME => CURRENT DATE, GENERATE_SYSLOG =>'RFC3164', EOF_DELAY => 300 ) ) AS X;