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.

Read syntax diagramSkip visual syntax diagramHISTORY_LOG_INFO(START_TIME => start-time ,END_TIME => end-time ,GENERATE_SYSLOG => generate-syslog,EOF_DELAY => eof-delay)
The schema is QSYS2.
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.
Start of changeFROM_JOB_NAMEEnd of change Start of changeVARCHAR(10)End of change Start of changeThe name of the job when the message was sent.End of change
Start of changeFROM_JOB_USEREnd of change Start of changeVARCHAR(10)End of change Start of changeThe user profile that started the job when the message was sent.End of change
Start of changeFROM_JOB_NUMBEREnd of change Start of changeVARCHAR(6)End of change Start of changeThe job number of the job when the message was sent.End of change
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;