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.

The schema is QSYS2.

Authorization: No authorization needed.

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.

Table 1. HISTORY_LOG_INFO table function
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. COMPLETION DIAGNOSTIC ESCAPE INFORMATIONAL INQUIRY NOTIFY REPLY REQUEST SENDER
MESSAGE_SUBTYPE	VARCHAR(22)	Subtype of message. The values returned for REPLY messages: FROM EXIT PROGRAM FROM SYSTEM REPLY LIST MESSAGE DEFAULT USED NOT VALIDITY CHECKED SYSTEM DEFAULT USED VALIDITY CHECKED The value returned for some REQUEST messages: WITH PROMPTING 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_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. 1 user-level messages 4 security/authorization messages 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. 1 Alert: Action must be taken immediately 3 Error condition 4 Warning condition 5 Notice: A normal but significant condition 6 Informational message 7 Debug level message 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.

All history log messages return a SYSLOG_FACILITY value of 1 except as noted below. Messages are assigned a SYSLOG_SEVERITY value in the following way:

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)

The Common Event Format key names that are generated within the SYSLOG_EVENT column are:

Table 2. Common Event Format key names
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;
```