MANAGE_AUDIT_JOURNAL_DATA_MART procedure

The MANAGE_AUDIT_JOURNAL_DATA_MART procedure populates a table with audit journal data for a specific audit journal entry type from a specified time period, or deletes an existing data mart table.

The table is created in the specified library with the name AUDIT_JOURNAL_xx where xx corresponds to the specified audit journal entry type. For example, when creating a data mart for the PW (Password) audit journal entry, a table named AUDIT_JOURNAL_PW is created. The procedure uses Audit journal entry services to populate the table. The result of the call to the procedure, including any failure message, is returned by the QSYS2.AUDIT_JOURNAL_DATA_MART_INFO view.

Authorization:

To call this procedure when data-mart-action is ADD, the caller must have:

*AUDIT special authority
*USE authority to the library that contains the audit journal data mart
*OBJOPR, *READ, and *ADD authority to the AUDIT_JOURNAL_xx table
*USE and *OBJEXIST authority to the audit journal
*USE authority to all requested journal receivers

To call this procedure when data-mart-action is CREATE, the caller must have:

*AUDIT special authority.
*OBJOPR, *READ, *ADD, and *EXECUTE authority to the library that contains the audit journal data mart
*USE and *OBJEXIST authority to the audit journal
*USE authority to all requested journal receivers

To call this procedure when data-mart-action is DROP, the caller must have:

*AUDIT special authority
*USE authority to the library that contains the audit journal data mart
*OBJOPR and *OBJEXIST authority to the AUDIT_JOURNAL_xx table

To call this procedure when data-mart-action is REPLACE, the caller must have:

*AUDIT special authority
*OBJOPR, *READ, *ADD, and *EXECUTE authority to the library that will contain the audit journal data mart
*OBJOPR, *OBJMGT, *OBJEXIST, *READ, *ADD, and *DLT authority to the AUDIT_JOURNAL_xx table
*USE and *OBJEXIST authority to the audit journal
*USE authority to all requested journal receivers

The schema is QSYS2.

journal-entry-type

A character or graphic string expression that identifies the journal entry type to include in the data mart. Only journal entry types that have corresponding Audit journal entry services are supported.

data-mart-library

A character or graphic string expression that identifies the library that contains the data mart table or where the data mart table is to be created.

data-mart-library must exist.

starting-timestamp

A character or graphic string expression that identifies the starting timestamp to use.

If no timestamp is provided,

*CONTINUE is used when data-mart-action is ADD.
*FIRST is used when data-mart-action is CREATE or REPLACE.
starting-timestamp will be ignored when data-mart-action is DROP.

*CONTINUE: A timestamp just after the last value for ENTRY_TIMESTAMP in the AUDIT_JOURNAL_xx table is used.
*FIRST: The attach time for the oldest, attached journal receiver is used.

ending-timestamp

A timestamp value that specifies the ending timestamp to use.

If no timestamp is provided,

the current timestamp will be used when data-mart-action is ADD, CREATE, or REPLACE.
ending-timestamp will be ignored when data-mart-action is DROP.

data-mart-action

A character or graphic string expression that indicates the type of action to perform. If a value is not provided, CREATE is used.

ADD: Inserts data into an existing data mart table and adds an entry for this data-mart-library and journal-entry-type to the AUDIT_JOURNAL_DATA_MART_INFO table. It uses the value of data-mart-filter that was specified when the data-mart-action of CREATE or REPLACE was run.; If the table does not exist, an error is returned.; When an AUDIT_JOURNAL_xx table function is enhanced to have additional columns, the related data mart table will be modified to add the new columns the next time an ADD action is performed. The existing data is maintained. This processing requires additional authority to the data mart table, matching the REPLACE action.
CREATE: Creates and populates a new data mart table and adds an entry for this data-mart-library and journal-entry-type to the AUDIT_JOURNAL_DATA_MART_INFO table.; If the table exists, an error is returned.
DROP: Deletes the data mart table and removes the corresponding entry from the AUDIT_JOURNAL_DATA_MART_INFO table.; If the table does not exist, an error is returned. If there is an entry in the AUDIT_JOURNAL_DATA_MART_INFO table, it is removed.
REPLACE: Creates and populates a new data mart table and adds an entry for this data-mart-library and journal-entry-type to the AUDIT_JOURNAL_DATA_MART_INFO table.; If the table exists, the current rows will be deleted.; If the table does not exist, it is created.

data-mart-filter

A character or graphic string expression up to 1000 characters long that limits the rows added to the data mart table. The string must consist of one or more predicates that will be appended to the SQL request to return data from the AUDIT_JOURNAL_xx table function. The predicate string is not validated in any way. If it is incorrect, an SQL syntax error will be returned. Columns referenced in the predicate must exist as return columns from the AUDIT_JOURNAL_xx table function.

If this parameter is omitted all information for the audit journal type with timestamps in the specified timestamp range will be added to the data mart table.

This parameter is ignored when data-mart-action is ADD or DROP.

Notes

The resulting AUDIT_JOURNAL_xx table will have the same format as the corresponding Audit journal entry services table function. The table is owned by the caller who first creates the table and *PUBLIC is configured with *EXCLUDE authority. The system name of the table is AJ_xx. The table is not managed by the system. It is up to the user to grant access to other users and to maintain the table.

To view an AUDIT_JOURNAL_xx table, the caller must have:

*USE authority to the library that contains the AUDIT_JOURNAL_xx table.
*OBJOPR and *READ authority to the AUDIT_JOURNAL_xx table.

Examples

Build a data mart for PW audit journal entries from the last month in the DMARTLIB library.

CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART(JOURNAL_ENTRY_TYPE => 'PW', 
                       DATA_MART_LIBRARY => 'DMARTLIB',
                       STARTING_TIMESTAMP => CURRENT DATE - 1 MONTH, 
                       ENDING_TIMESTAMP => CURRENT TIMESTAMP);

Build a data mart for AF audit journal entries from the last month in the DMARTLIB library. Only include A (not authorized to object) entries that are for objects not in a library that starts with Q.

CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART(JOURNAL_ENTRY_TYPE => 'AF', 
                       DATA_MART_LIBRARY => 'DMARTLIB',
                       STARTING_TIMESTAMP => CURRENT DATE - 1 MONTH, 
                       ENDING_TIMESTAMP => CURRENT TIMESTAMP,
                       DATA_MART_FILTER => 'VIOLATION_TYPE = ''A'' AND OBJECT_LIBRARY NOT LIKE ''Q%''');

Add journal data for PW entries to the existing data mart in DMARTLIB.

CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART(JOURNAL_ENTRY_TYPE => 'PW',
                            DATA_MART_LIBRARY => 'DMARTLIB', 
                            STARTING_TIMESTAMP => '*CONTINUE',
                            ENDING_TIMESTAMP => CURRENT TIMESTAMP,
                            DATA_MART_ACTION => 'ADD'
                            );

Drop the DMARTLIB.AUDIT_JOURNAL_PW table.

CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART(JOURNAL_ENTRY_TYPE => 'PW',
                            DATA_MART_LIBRARY => 'DMARTLIB',
                            DATA_MART_ACTION => 'DROP'
                            );