DISPLAY_JOURNAL table function

The DISPLAY_JOURNAL table function returns information about journal entries. It returns information similar to what is returned by the Display Journal (DSPJRN) CL command and the Retrieve Journal Entries (QjoRetrieveJournalEntries) API.

Authorization:
  • The caller must have *USE authority to the journal and to all requested journal receivers.
  • *OBJEXIST authority is required to the journal if object-name is omitted or if object-name specifies an object that no longer exists.
  • If object-name is *ALL, the caller must be authorized to every object associated with a journal entry.
    • For objects in the QSYS file system. the caller must have:
      • *USE authority to the object, and
      • *EXECUTE to the library containing the object.
    • For objects in the integrated file system, the caller must have:
      • *R for the object
      • *X for every directory in the object's path.
Read syntax diagramSkip visual syntax diagramDISPLAY_JOURNAL( JOURNAL_LIBRARY =>  journal-library , JOURNAL_NAME =>  journal-name ,STARTING_RECEIVER_LIBRARY => receiver-library,STARTING_RECEIVER_NAME => receiver-name,STARTING_TIMESTAMP => starting-timestamp,STARTING_SEQUENCE => starting-sequence,JOURNAL_CODES => journal-codes,JOURNAL_ENTRY_TYPES => journal-types,OBJECT_LIBRARY => object-library,OBJECT_NAME => object-name,OBJECT_OBJTYPE => object-type,OBJECT_MEMBER => object-member,USER => user,JOB => job,PROGRAM => program,ENDING_RECEIVER_LIBRARY => ending-receiver-library,ENDING_RECEIVER_NAME => ending-receiver-name,ENDING_TIMESTAMP => ending-timestamp,ENDING_SEQUENCE => ending-sequence,GENERATE_SYSLOG => generate-syslog,EOF_DELAY => eof-delay)
The schema is QSYS2.
journal-library
A character or graphic string expression that identifies the name of the library containing the journal. The name cannot be *LIBL or *CURLIB.
journal-name
A character or graphic string expression that identifies the name of the journal.
receiver-library
A character or graphic string expression that identifies the name of the starting journal receiver library. The name can be *LIBL or *CURLIB.
receiver-name
A character or graphic string expression that identifies the name of the starting journal receiver. If one of the special values is specified, the receiver-library value will be ignored. Otherwise, the receiver-name and receiver-library must identify a valid journal receiver.

If no journal receiver is specified, *CURRENT is used.

*CURRENT
The journal receiver that is currently attached when starting to convert journal entries is used.
*CURCHAIN
The journal receiver chain that includes the journal receiver that is currently attached when starting to convert journal entries is used. This receiver chain does not cross a break in the chain. If there is a break in the chain, the receiver range is from the most recent break in the chain through the receiver that is attached when starting to convert journal entries.
*CURAVLCHN
The journal receiver chain that includes the journal receiver that is attached when starting to convert journal entries is used. This receiver chain does not cross a break in the chain. If there is a break in the chain, the receiver range is from the most recent break in the chain through the receiver that is attached when starting to convert journal entries. If journal receivers exist in the receiver chain that are not available because they were saved with the storage freed option, those journal receivers will be ignored and entries will be converted starting with the first available journal receiver in the chain.
*CURSEQCHN
The journal receiver chain that includes the journal receiver that is attached when starting to convert journal entries is used, starting with the most recent journal receiver in which the journal sequence number was reset if the journal sequence number was reset in the receiver chain. This receiver chain does not cross a break in the chain. If there is a break in the chain, the receiver range is from the most recent break in the chain through the receiver that is attached when starting to convert journal entries. If journal receivers exist in the receiver chain that are not available because they were saved with the storage freed option, those journal receivers will be ignored and entries will be converted starting with the first available journal receiver in the chain.
starting-timestamp
A timestamp value that specifies the starting timestamp to use1.

A value cannot be specified for both starting-timestamp and starting-sequence.

If no starting timestamp is specified, *FIRST is used.

starting-sequence
A decimal expression that identifies the starting sequence number to use. If the starting-sequence value is not found in the receiver range, an error is returned.

A value cannot be specified for both starting-timestamp and starting-sequence.

If no starting sequence is specified, *FIRST is used.

journal-codes
A character or graphic string expression that lists the journal codes to return. The string can contain the special values of *ALL or *CTL, or it can be a list of one or more journal codes. Journal codes in the string can be separated by one or more separators. Separators are blank and comma. For example, a valid string can be 'RJ' or 'R J' or 'R,J' or 'R, J'.

If no string is provided, *ALL is used.

journal-types
A character or graphic string expression that lists the journal entry types to return. The string can contain the special values of *ALL or *RCD, or it can be a list of one or more journal entry types. Journal entry types in the string can be separated by one or more separators. Separators are blank and comma. For example, a valid string can be 'JFCT' or 'JF CT' or 'JF,CT' or 'JF, CT'.

If no string is provided, *ALL is used.

object-library
A character or graphic string expression that identifies the name of an object library. The values *LIBL and *CURLIB are allowed.
object-name
Start of changeA character or graphic string expression that contains up to 300 object names. Multiple names can be separated by one or more separators. Separators are blank and comma.End of change

If object-name is the special value of *ALL, object-library must contain a library name and object-type must contain a valid object type.Start of change Otherwise, each object name must identify a valid object using the same object-library, object-type, and object-member parameters.End of change

If no object name is provided, a value of *ALLFILE is used for the journaled file name on the API interface.

object-type
A character or graphic string expression that identifies the system object type for the object. The value must be *DTAARA, *DTAQ, *FILE, or *LIB.
object-member
A character or graphic string expression that identifies the name of a member. It can be a special value of *FIRST, *ALL, or *NONE or a valid member name. If the object type is not *FILE, the member name is ignored.
user
A character or graphic string expression that identifies the user profile name Start of changefor the current user of the jobEnd of change. If user is not specified, *ALL is used.
job
A character or graphic string expression that identifies the name of a job. Two forms of a job name are supported.
  1. Start of changeA fully qualified job name in the form job-number/job-user/job-name.End of change
  2. The first 10 characters are the job name, the second 10 characters are the user name, and the last 6 characters are the job number.
If job is not specified, *ALL is used.
program
A character or graphic string expression that identifies the name of a program. If program is not specified, *ALL is used.
Start of changeending-receiver-libraryEnd of change
Start of changeA character or graphic string expression that identifies the name of the ending journal receiver library. The name can be *LIBL or *CURLIB. If ending-receiver-name is not *CURRENT, a value for ending-receiver-library must be specified.

The value of this parameter is ignored if eof-delay is greater than zero.

End of change
Start of changeending-receiver-nameEnd of change
Start of changeA character or graphic string expression that identifies the name of the ending journal receiver. If the special value *CURRENT is specified, the ending-receiver-library value will be ignored. Otherwise, the ending-receiver-name and ending-receiver-library must identify a valid journal receiver.

If ending-receiver-name is not specified, *CURRENT is used.

The value of this parameter is ignored if eof-delay is greater than zero.

End of change
Start of changeending-timestampEnd of change
Start of changeA timestamp value that specifies the ending timestamp to use1.

A value cannot be specified for both ending-timestamp and ending-sequence. This parameter cannot be specified if eof-delay is greater than zero.

If no ending timestamp is specified, *LAST is used.

End of change
Start of changeending-sequenceEnd of change
Start of changeA decimal expression that identifies the ending sequence number to use. If the ending-sequence value is not found in the receiver range, an error is returned.

A value cannot be specified for both ending-timestamp and ending-sequence. This parameter cannot be specified if eof-delay is greater than zero.

If no ending sequence is specified, *LAST is used.

End of change
Start of changegenerate-syslogEnd of change
Start of changeA character or graphic string expression that indicates whether to transform journal entries 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 if syslog information is defined for the journal entry. 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 if syslog information is defined for the journal entry. 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.

DISPLAY_JOURNAL only returns syslog information for the audit journal. If RFC3164 or RFC5424 is specified, journal-library must be QSYS and journal-name must be QAUDJRN.

If generate-syslog is not specified or is the null value, NO is used.

End of change
Start of changeeof-delayEnd of change
Start of changeAn integer expression that specifies the number of seconds to sleep when all audit journal entries 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 entries 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 audit journal entries 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, the ending-receiver-library and ending-receiver-name are ignored, and the ending-timestamp and ending-sequence parameters cannot be specified with a value other than their default values.

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 DISPLAY_JOURNAL 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.

End of change

The special values supported for the function arguments are the same as for the Display Journal (DSPJRN) CL command.

The result of the function is a table containing rows with the format shown in the following table. All the columns are nullable.
Table 1. DISPLAY_JOURNAL table function
Column Name Data Type Description
ENTRY_TIMESTAMP TIMESTAMP The system date and time when the journal entry was added to the journal receiver1.
SEQUENCE_NUMBER DECIMAL(21,0) A number assigned by the system to each journal entry.
JOURNAL_CODE CHAR(1) The primary category of the journal entry.
JOURNAL_ENTRY_TYPE CHAR(2) Further identifies the type of user-created or system-created entry.
COUNT_OR_RRN BIGINT Contains either the relative record number (RRN) of the record that caused the journal entry or a count that is pertinent to the specific type of journal entry.
ENTRY_DATA BLOB(2G) The entry specific data returned for this journal entry.

See Notes section for row and column access control considerations.

NULL_VALUE_INDICATORS VARCHAR(8000) The null value indicators returned for this journal entry.
OBJECT VARCHAR(30) The name of the object for which the journal entry was added.
OBJECT_TYPE VARCHAR(10) The type of object in the entry.
OBJECT_TYPE_INDICATOR CHAR(1) An indicator with respect to the information in the object field.
FILE_TYPE_INDICATOR CHAR(1) Identifies whether or not this journal entry is associated with a logical file.
JOURNAL_IDENTIFIER VARCHAR(10) The journal identifier (JID) for the object.
CURRENT_USER VARCHAR(10) The name of the effective user profile under which the job was running when the entry was created.
JOB_NAME VARCHAR(10) The name of the job that added the entry.
JOB_USER VARCHAR(10) The user profile name of the user that started the job.
JOB_NUMBER VARCHAR(6) The job number of the job that added the entry.
THREAD BIGINT Identifies the thread within the process that added the journal entry.
PROGRAM_NAME VARCHAR(10) The name of the program that added the entry.
PROGRAM_LIBRARY VARCHAR(10) The name of the library that contains the program that added the journal entry.
PROGRAM_LIBRARY_ASP_DEVICE VARCHAR(10) The name of the ASP device that contains the program.
PROGRAM_LIBRARY_ASP_NUMBER INTEGER The number for the auxiliary storage pool that contains the program that added the journal entry.
COMMIT_CYCLE DECIMAL(21,0) A number that identifies the commit cycle.
NESTED_COMMIT_LEVEL BIGINT Indicates the nesting level of the commit cycle that was open when a journal entry representing an object level change was deposited.
XID VARCHAR(140) The transaction identifier, as defined by the Open Group's XA specification, for commit cycles related to an XA transaction branch.
LUW VARCHAR(39) The logical unit of work identifies entries to be associated with a given unit of work.
REMOTE_PORT INTEGER The port number of the remote address associated with this journal entry.
REMOTE_ADDRESS VARCHAR(46) The remote address associated with the journal entry.
SYSTEM_NAME VARCHAR(8) The name of the system on which the entry is being retrieved.
SYSTEM_SEQUENCE_NUMBER DECIMAL(21,0) The system sequence number indicates the relative sequence of when this journal entry was deposited into the journal.
REFERENTIAL_CONSTRAINT CHAR(1) Whether this entry was recorded for actions that occurred on records that are part of a referential constraint.
TRIGGER CHAR(1) Whether this entry was created as result of a trigger program.
IGNORE_ON_APPLY CHAR(1) Whether this entry is ignored during an Apply Journaled Changes (APYJRNCHG) or Remove Journaled Changed (RMVJRNCHG) command.
MINIMIZED_ENTRY_DATA CHAR(1) Whether this entry has minimized entry specific data as a result of the journal having specified MINENTDTA for the object type of the entry.
MINIMIZED_ON_FIELD_BOUNDARY CHAR(1) Whether this entry has minimized entry specific data on field boundaries as a result of the journal having been specified with MINENTDTA(*FLDBDY).
INDICATOR_FLAG CHAR(1) An indicator for the operation.
RECEIVER_NAME VARCHAR(10) The name of the receiver holding the journal entry.
RECEIVER_LIBRARY VARCHAR(10) The name of the library containing the receiver holding the journal entry.
RECEIVER_ASP_DEVICE VARCHAR(10) The name of the ASP device containing the receiver holding the journal entry.
RECEIVER_ASP_NUMBER INTEGER The number for the auxiliary storage pool containing the receiver holding the journal entry.
ARM_NUMBER INTEGER The number of the disk arm that contains the journal entry.
OBJECT_ASP_DEVICE VARCHAR(10) ASP device name.
OBJECT_ASP_NUMBER INTEGER ASP number.
PARENT_FILE_ID BINARY(16) File ID for parent directory.
OBJECT_FILE_ID BINARY(16) File ID for object.
RELATIVE_DIRECTORY_FILE_ID BINARY(16) File ID of directory containing object in PATH_NAME.
OBJECT_FILE_NAME
VARGRAPHIC(2002)
CCSID 1200
Object name.
PATH_NAME
DBCLOB(16M)
CCSID 1200
Name of IFS path.
DLO_NAME VARCHAR(12) DLO name.
FOLDER_PATH VARCHAR(63) DLO folder path.
Start of changeSYSLOG_EVENTEnd of change Start of changeVARGRAPHIC(2048) CCSID 1200End of change Start of changeThe Common Event Format (CEF) syslog event for the journal entry preceded with 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 audit journal entry types that generate syslog information and the key names returned for journal entries are listed in the Notes section.

Contains the null value if there is no syslog event defined for the journal entry or if NO was specified for the GENERATE_SYSLOG parameter.

End of change
Start of changeSYSLOG_FACILITYEnd of change Start of changeINTEGEREnd of change Start of changeThe syslog facility assigned to the event.
4
Security/authorization messages. This value is returned for all T and U audit journal entries.

Contains the null value if there is no syslog event defined for the journal entry or if NO was specified for the GENERATE_SYSLOG parameter.

End of change
Start of changeSYSLOG_SEVERITYEnd of change Start of changeINTEGEREnd of change Start of changeThe syslog severity assigned to the event.
2
Critical condition
4
Warning condition
5
Notice: A normal but significant condition
6
Informational message

The severity assigned to each journal entry is listed in the Notes section.

Contains the null value if there is no syslog event defined for the journal entry or if NO was specified for the GENERATE_SYSLOG parameter.

End of change
Start of changeSYSLOG_PRIORITYEnd of change Start of changeINTEGEREnd of change Start of changeThe syslog priority number assigned to the event.

Contains the null value if there is no syslog event defined for the journal entry or if NO was specified for the GENERATE_SYSLOG parameter.

End of change
Start of change

Notes

Row and column access control: This table function recognizes whether ROW ACCESS CONTROL or COLUMN ACCESS CONTROL exists and is activated for the target table. If any row or column access control is active for the table, the rule text logic defined for the row permissions and/or column masks is applied before returning the value in ENTRY_DATA. When the rule text for a row permission determines that the user invoking the function should not see the row, the ENTRY_DATA column contains the text NOT AUTHORIZED. If the user is allowed to see the row and a column mask exists, the rule text for the column mask determines the value returned for ENTRY_DATA.

Filtering considerations: When using DISPLAY_JOURNAL to review journal activity for specific objects, there are some considerations to ensure complete results are returned.

  • When querying the audit journal (JOURNAL_LIBRARY => 'QSYS' and JOURNAL_NAME => 'QAUDJRN'), do not use the object filters because they will result in no entries being returned. For an audit journal, use a WHERE clause to limit the rows returned.
  • When querying a data journal, consider whether it's possible that the Journal Identifier (JID) has changed for the object. The object filters use the JID found in the object and will not return any entries with a different JID. If the JID for the object might have changed, avoid using the object filters. In this case, use a WHERE clause to limit the rows returned.
End of change
Start of change

Syslog information: Syslog information is returned for all audit journal entries Start of changewith T and U journal codesEnd of change. Syslog information is also available for history log messages. See HISTORY_LOG_INFO table function for more details.

The following audit journal entries with T journal codes generate syslog information:
AD
Auditing changes
AF
Authority failure
Start of changeAPEnd of change
Start of changeObtaining adopted authorityEnd of change
Start of changeAUEnd of change
Start of changeAttribute changesEnd of change
AX
Row and column access control
CA
Authority changes
CD
Command string audit
CO
Create object
CP
User profile changed, created, or restored
Start of changeCQEnd of change
Start of changeChange of *CRQD objectEnd of change
Start of changeCUEnd of change
Start of changeCluster operationsEnd of change
Start of changeCVEnd of change
Start of changeConnection verificationEnd of change
Start of changeCYEnd of change
Start of changeCryptographic configurationEnd of change
Start of changeDIEnd of change
Start of changeDirectory serverEnd of change
DO
Delete object
DS
DST security password reset
Start of changeEVEnd of change
Start of changeSystem environment variablesEnd of change
GR
Generic record
GS
Socket description was given to another job
Start of changeIMEnd of change
Start of changeIntrusion monitorEnd of change
Start of changeIPEnd of change
Start of changeInterprocess communicationEnd of change
Start of changeIREnd of change
Start of changeIP rules actionsEnd of change
Start of changeISEnd of change
Start of changeInternet security managementEnd of change
Start of changeJDEnd of change
Start of changeChange to user parameter of a job descriptionEnd of change
Start of changeJSEnd of change
Start of changeActions that affect jobsEnd of change
Start of changeKFEnd of change
Start of changeKey ring fileEnd of change
LD
Link, unlink, or look up directory entry
Start of changeMLEnd of change
Start of change Office services mail actionsEnd of change
Start of changeNAEnd of change
Start of changeNetwork attribute changedEnd of change
Start of changeNDEnd of change
Start of changeAPPN directory search filter violationEnd of change
Start of changeNEEnd of change
Start of changeAPPN end point filter violationEnd of change
OM
Object move or rename
OR
Object restore
OW
Object ownership changed
Start of changeO1End of change
Start of changeOptical accessEnd of change
Start of changeO2End of change
Start of changeOptical accessEnd of change
Start of changeO3End of change
Start of changeOptical accessEnd of change
PA
Program changed to adopt authority
Start of changePFEnd of change
Start of changePTF operationsEnd of change
PG
Change of an object’s primary group
Start of changePOEnd of change
Start of changePrinted outputEnd of change
Start of changePSEnd of change
Start of changeProfile swapEnd of change
Start of changePUEnd of change
Start of changePTF object changesEnd of change
PW
Invalid password
RA
Authority change during restore
RJ
Restoring job description with user profile specified
RO
Change of object owner during restore
RP
Restoring adopted authority program
Start of changeRQEnd of change
Start of changeRestoring a *CRQD objectEnd of change
RU
Restoring user profile authority
RZ
Changing a primary group during restore
Start of changeSDEnd of change
Start of changeChanges to system distribution directoryEnd of change
SE
Subsystem routing entry changed
Start of changeSFEnd of change
Start of changeActions to spooled filesEnd of change
Start of changeSGEnd of change
Start of changeAsynchronous signalsEnd of change
Start of changeSKEnd of change
Start of changeSockets connectionsEnd of change
Start of changeSMEnd of change
Start of changeSystems management changesEnd of change
SO
Server security user information actions
ST
Use of service tools
SV
System value changed
Start of changeVOEnd of change
Start of changeValidation list actionsEnd of change
Start of changeVPEnd of change
Start of changeNetwork password errorEnd of change
Start of changeXDEnd of change
Start of changeDirectory server extensionEnd of change
Start of changeX0End of change
Start of changeNetwork authenticationEnd of change
Start of changeX1End of change
Start of changeIdentity tokenEnd of change
Start of changeX2End of change
Start of changeQuery manager profile changesEnd of change
Start of changeYCEnd of change
Start of changeDLO object accessed (change)End of change
Start of changeYREnd of change
Start of change DLO object accessed (read)End of change
ZC
Object accessed (change)
ZR
Object accessed (read)
Audit journal entries with the U journal code are assigned a SYSLOG_SEVERITY of 6. The audit journal entries with T journal codes are assigned a SYSLOG_SEVERITY value in the following way:
  • Severity 2 Critical condition
    • SV - System value when QAUDCTL is changed to *NONE
  • Severity 4 Warning condition
    • AF - Authority failure
    • Start of changeDI - Directory server (operation type 'AF')End of change
    • GR - Generic record, when function usage was checked and failed for a function name with a prefix of QIBM_DB_
    • Start of changeIM - Intrusion monitorEnd of change
    • Start of changeIP - Interprocess communication (entry type 'F')End of change
  • Severity 5 Notice: A normal but significant condition
    • AD - Auditing changes
    • AX - Row and column access control
    • CA - Authority changes
    • CP - User profile changed, created, or restored
    • Start of changeDI - Directory server (operation types 'AD', 'CA', 'CP', 'OM', 'OW', and 'PW')End of change
    • DS - DST security password reset
    • Start of changeIP - Interprocess communication (entry type 'A')End of change
    • Start of changeJD - Change to user parameter of a job descriptionEnd of change
    • Start of changeJS - Actions that affect jobs (entry types 'M' and 'T')End of change
    • OM - Object move or rename
    • OW - Object ownership changed
    • Start of changeO3 - Optical access (entry type 'L')End of change
    • PG - Change of an object’s primary group
    • Start of changePS - Profile swapEnd of change
    • PW - Invalid password
    • RA - Authority change during restore
    • RO - Change of object owner during restore
    • RU - Restoring user profile authority
    • RZ - Change a primary group during restore
    • SO - Server security user information actions
    • Start of changeVO - Validation list actions (entry type 'U')End of change
    • Start of changeVP - Network password errorEnd of change
    • Start of changeX0 - Network authentication (entry types '2' - '6', '8', '9' and 'A' - 'F')End of change
    • Start of changeX1 - Identity token (entry types 'F' and 'U')End of change
    • Start of changeX2 - Query manager profile changesEnd of change
  • Severity 6 Informational message
    • Start of changeAP - Obtaining adopted authorityEnd of change
    • Start of changeAU - Attribute changesEnd of change
    • CD - Command string audit
    • CO - Create object
    • Start of changeCQ - Change of *CRQD objectEnd of change
    • Start of changeCU - Cluster operationsEnd of change
    • Start of changeCV - Connection verificationEnd of change
    • Start of changeCY - Cryptographic configurationEnd of change
    • Start of changeDI - Directory server (all operation types other than 'AD', 'AF', 'CA', 'CP', 'OM', 'OW', and 'PW')End of change
    • DO - Delete object
    • Start of changeEV - System environment variablesEnd of change
    • GR - Generic record, except for the Severity 4 case where function usage was checked and failed
    • GS - Socket description was given to another job
    • Start of changeIP - Interprocess communication (all entry types other than 'A' and 'F')End of change
    • Start of changeIR - IP rules actionsEnd of change
    • Start of changeIS - Internet security managementEnd of change
    • Start of changeJS - Actions that affect jobs (all entry types other than 'M' and 'T')End of change
    • Start of changeKF - Key ring fileEnd of change
    • LD - Link, unlink, or look up directory entry
    • Start of changeML - Office services mail actionsEnd of change
    • Start of changeNA - Network attribute changedEnd of change
    • Start of changeND - APPN directory search filter violationEnd of change
    • Start of changeNE - APPN end point filter violationEnd of change
    • OR - Object restore
    • Start of changeO1 - Optical accessEnd of change
    • Start of changeO2 - Optical accessEnd of change
    • Start of changeO3 - Optical access (all entry types other than 'L')End of change
    • PA - Program changed to adopt authority
    • Start of changePF - PTF operationsEnd of change
    • Start of changePO - Printed outputEnd of change
    • Start of changePU - PTF object changesEnd of change
    • RJ - Restoring job description with user profile specified
    • RP - Restoring adopted authority program
    • Start of changeRQ - Restoring a *CRQD objectEnd of change
    • Start of changeSD - Changes to system distribution directoryEnd of change
    • SE - Subsystem routing entry changed
    • Start of changeSF - Actions to spooled filesEnd of change
    • Start of changeSG - Asynchronous signalsEnd of change
    • Start of changeSK - Sockets connectionsEnd of change
    • Start of changeSM - Systems management changesEnd of change
    • ST - Use of service tools
    • SV - System value changed, except for QAUDCTL severity 2 case
    • Start of changeVO - Validation list actions (all entry types other than 'U')End of change
    • Start of changeXD - Directory server extensionEnd of change
    • Start of changeX0 - Network authentication (all entry types other than '2' - '6', '8', '9' and 'A' - 'F')End of change
    • Start of changeX1 - Identity token (all entry types other than 'F' and 'U')End of change
    • Start of changeYC - DLO object accessed (change)End of change
    • Start of changeYR - DLO object accessed (read)End of change
    • ZC - Object accessed (change)
    • ZR - Object accessed (read)
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
Start of changeattrNameEnd of change Start of changeAttribute name (extracted from ENTRY_DATA column)End of change
Start of changeattrValueEnd of change Start of changeAttribute value (extracted from ENTRY_DATA column)End of change
deviceExternalId Device name (extracted from ENTRY_DATA column)
dloName Document Library Object name (DLO_NAME column)
dloPath Document Library Object folder path (FOLDER_PATH column)
dproc Destination job (process) name (extracted from ENTRY_DATA column)
Start of changedptEnd of change Start of changeDestination port number (extracted from ENTRY_DATA column)End of change
Start of changedstEnd of change Start of changeDestination IP address (extracted from ENTRY_DATA column)End of change
duser Destination user name (extracted from ENTRY_DATA column)
filePath IFS stream file path (PATH_NAME column)
fileType Object type (OBJECT_TYPE column)
fname IFS stream file name (OBJECT_FILE_NAME column)
msg Additional information from the audit record not included in other keys (extracted from ENTRY_DATA column)
objName Object name (OBJECT column)
Start of changeoldAttrValueEnd of change Start of changeAttribute value (before change) (extracted from ENTRY_DATA column)End of change
oldDloName Document Library Object name (before rename) (extracted from ENTRY_DATA column)
oldDloPath Document Library Object folder path (before rename) (extracted from ENTRY_DATA column)
oldFileName IFS stream file name (before rename) (extracted from ENTRY_DATA column)
oldFilePath IFS stream file path (before rename) (extracted from ENTRY_DATA column)
oldObjName Object name (before rename) (extracted from ENTRY_DATA column)
reason Text description of the audit journal entry
shost Source system (host) name (SYSTEM_NAME column)
sproc Source job (process) name (JOB_NAME, JOB_USER, JOB_NUMBER columns)
spt Source port number (REMOTE_PORT column)
src Source IP address (REMOTE_ADDRESS column)
suser Source user name (CURRENT_USER column)
End of change

Examples

  • Select all entries from the *CURRENT receiver of journal TESTLIB/QSQJRN.
    SELECT * FROM TABLE (
                     QSYS2.DISPLAY_JOURNAL( 'TESTLIB', 'QSQJRN')) AS JT;
  • Find all changes made by SUPERUSER against the PRODDATA/SALES table. The first two arguments are passed without names since they correspond with the first two parameters for the function. The other four arguments are passed using the parameter name syntax to avoid specifying a value for the parameters that are not needed.
    SELECT journal_code, journal_entry_type, object, object_type, X.* 
    FROM TABLE (
      QSYS2.Display_Journal(
                'PRODDATA', 'QSQJRN', -- Journal library and name
                OBJECT_LIBRARY=>'PRODDATA', OBJECT_NAME=>'SALES',
                OBJECT_OBJTYPE=>'*FILE', OBJECT_MEMBER=>'SALES' 
        ) ) AS X
    WHERE journal_entry_type in ('DL', 'PT', 'PX', 'UP') AND "CURRENT_USER" = 'SUPERUSER'
    ORDER BY entry_timestamp DESC;  
  • Review audit journal entries for the REQUESTS file in MYCO library. For an audit journal, a predicate is used to designate the object name.
    SELECT journal_code, journal_entry_type, object, object_type, X.* 
      FROM TABLE (QSYS2.Display_Journal('QSYS', 'QAUDJRN') ) AS X 
      WHERE LEFT(OBJECT,20) = CHAR('REQUESTS', 10) CONCAT CHAR('MYCO', 10)
      ORDER BY entry_timestamp DESC;
  • Review changes from the last hour for the REQUESTS file in MYCO library using the current JID. This query will only find entries where the JID of MYCO/REQUESTS *FILE is an exact match to the entry. It filters for the following three journal codes:
    D
    Database File Operation
    F
    Database File Member Operation
    R
    Operation on Specific Record
    SELECT journal_code, journal_entry_type, object, object_type, X.* 
      FROM TABLE (QSYS2.Display_Journal('MYCO', 'QSQJRN', 
                JOURNAL_CODES => 'D,F,R',
                STARTING_RECEIVER_NAME => '*CURCHAIN',                         
                OBJECT_OBJTYPE=>'*FILE',
                OBJECT_LIBRARY=>'MYCO', 
                OBJECT_NAME=>'REQUESTS',
                OBJECT_MEMBER=>'*ALL'
        ) ) AS X 
      WHERE entry_timestamp > CURRENT TIMESTAMP - 1 HOUR  
      ORDER BY entry_timestamp DESC  ;
    
  • Review all changes from the last hour for the REQUESTS file in MYCO library, including any that might have a different Journal ID (JID). To see entries for all JIDs, a predicate is used to designate the object name.
    SELECT journal_code, journal_entry_type, hex( journal_identifier ), 
            object, object_type, X.* 
      FROM TABLE (QSYS2.Display_Journal('MYCO', 'QSQJRN', 
                JOURNAL_CODES => 'D,F,R',
                STARTING_RECEIVER_NAME => '*CURCHAIN'                         
        ) ) AS X 
      WHERE LEFT(OBJECT,20) = CHAR('REQUESTS', 10) CONCAT CHAR('MYCO', 10)
          and entry_timestamp > CURRENT TIMESTAMP - 1 HOUR
      ORDER BY entry_timestamp DESC  ;
    
  • Start of changeSelect entries from the audit journal that return syslog information and format them with an RFC5424 header.
    SELECT syslog_facility, syslog_severity, syslog_event  
      FROM TABLE (QSYS2.DISPLAY_JOURNAL('QSYS', 'QAUDJRN',
                                        GENERATE_SYSLOG =>'RFC5424'
                  ) ) AS X
      WHERE syslog_event IS NOT NULL;
    
    End of change
1 The accuracy of the entry timestamp stored in journal receivers is only accurate to 16 microseconds. Hence, a value passed as a starting_timestamp and ending_timestamp will be truncated such that the actual timestamps being searched for may be from 0 to 15 microseconds less than the specified value.