MIRROR_DISPLAY_JOURNAL table function

The MIRROR_DISPLAY_JOURNAL table function formats Db2® Mirror specific audit journal entries as well as returning typical audit journal information.

Authorization: The privileges held by the authorization ID of the statement must include *AUDIT special authority. For the authority needed to use this function, see Authorization.

Read syntax diagramSkip visual syntax diagram MIRROR_DISPLAY_JOURNAL ( STARTING_TIMESTAMP => starting-timestamp,ENDING_TIMESTAMP => ending-timestamp,USER_PROFILE => user-profile,JOURNAL_ENTRY_TYPES => journal-entry-types )
The schema is QSYS2.
Start of changestarting-timestampEnd of change
Start of change

A timestamp value that specifies the starting timestamp to use1.

If no starting timestamp is specified, CURRENT DATE - 1 DAY is used.

End of change
Start of changeending-timestampEnd of change
Start of change

A timestamp value that specifies the ending timestamp to use1.

If no ending timestamp is specified, CURRENT TIMESTAMP is used.

End of change
user-profile

A character or graphic string expression that identifies the user profile name for the current user of the job. If user-profile is not specified, *ALL is used.

journal-entry-types

A character or graphic string expression that lists the journal entry types to return. The supported journal-entry-types are: M0, M6, M7, M8, and M9. Multiple entry types can be requested for a single invocation by separating the entry type values with a blank or a comma. For example: 'M6', 'M0, M9', or 'M7 M8 M9'. See Layout of audit journal entries for detailed information about these journal entries.

If no journal-entry-types are provided, all Db2 Mirror journal entry types are returned.

The result of the function is a table containing rows with the format shown in the following table. All columns are nullable.

Table 1. MIRROR_DISPLAY_JOURNAL table function
Column Name Data Type Description
ENTRY_TIMESTAMP TIMESTAMP The system date and time when the audit journal entry was added to the journal receiver.
Start of changeACTIONEnd of change Start of changeVARCHAR(200)End of change Start of changeDescription of this journal entry. End of change
DETAIL VARCHAR(200) The details from the audit journal entry. In some cases it includes information formatted as topic = value.
TARGET VARCHAR(10) Name of the independent ASP group associated with this audit journal entry. Contains either the name of the ASP group or the following special value
*SYSBAS
This rule is associated with objects stored in either the system ASP (ASP 1) and any basic user ASPs (ASPs 2-32).
Target describes whether the entry pertains to *SYSBAS, or a specific IASP.
Start of changeREASONEnd of change Start of changeVARCHAR(200)End of change Start of changeThe cause of the action, if available.End of change
SEQUENCE_NUMBER DECIMAL(21,0) A number assigned by the system to each journal entry.
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.
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.
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.
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.
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.
ARM_NUMBER INTEGER The number of the disk arm that contains the journal entry.

Notes

Start of changeInformation from the supported Db2 Mirror journal-entry-types can also be examined using the following SQL table functions: End of change

Examples

  • Review all Db2 Mirror audit journal entries from yesterday and today.
    SELECT * FROM TABLE(QSYS2.MIRROR_DISPLAY_JOURNAL()) 
  ORDER BY ENTRY_TIMESTAMP DESC;
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.