New parameter added to Audit Data Mart procedure

Audit Data Marts were added to IBM i in the previous round of TRs, and in the latest round of TRs comes what I can think is a significant improvement.

IMHO Audit Data Marts are a big improvement in the way I can manage Audit journal data. It allows me to copy the data from the QAUDJRN journal into a series of Audit Data Mart Tables. Each Audit Data Mart Table contains the data for one particular audit journal entry types. I am not going to go into more detail about creating Audit Data Marts as I wrote, in detail, about how to use them in my post: Audit Journal Data Mart in October of last year.

An additional parameter was added to the procedure MANAGE_AUDIT_JOURNAL_DATA_MART that makes it possible to select the information when I create or reload the Audit Data Mart. It is the DATA_MART_FILTER parameter. With this I can add additional selection criteria, in SQL format, to the MANAGE_AUDIT_JOURNAL_DATA_MART statement. Up to a thousand characters can be entered. Be warned the statement entered here is not validated, therefore, if what you enter is not valid it will cause an SQL syntax error when the statement is executed.

For my examples today I will be working with the audit journal entry CA, authority changes.

I would normally create the CA Audit Data Mart with the following statement:

01 CALL QSYS2.MANAGE_DATA_JOURNAL_DATA_MART( 02 JOURNAL_ENTRY_TYPE => 'CA', 03 DATA_MART_LIBRARY => 'MYLIB', 04 STARTING_TIMESTAMP => '*FIRST', 05 DATA_MART_ACTION => 'CREATE') ;

Line 2: This is the audit journal entry type I want to extract.

Line 3: The library where I want the Audit Data Mart created.

Line 4: I want the data to start at the first available, oldest, CA entry. As there is no ending timestamp the data will be copied up to the current timestamp.

Line 5: I am creating the Audit Data Mart table.

For today's examples this statement will not give consistent results. If I run the statement again, I will get a different number of rows as the jobs running in this IBM i partition will keep creating CA entries in QAUDJRN.

What I need to do is to use the ending timestamp. Rather than hard code the statement I am going to create a Global Variable to contain a timestamp value which I will use in all the following examples.

This statement creates the Global Variable I will be using:

01 CREATE OR REPLACE VARIABLE MYLIB.MY_TIMESTAMP 02 FOR SYSTEM NAME "MYTSTAMP" 03 TIMESTAMP ;

Line 1: Wherever possible I always like to use the CREATE OR REPLACE. I have created the Global Variable in my library.

Line 2: As the SQL (long) name is more than ten characters I am giving a system (short) name too.

Line 3: The Global Variable is a timestamp. The default for the Global Variable will be null until I set it.

This is how I can set the timestamp to be the current timestamp:

01 SET MYLIB.MY_TIMESTAMP = CURRENT_TIMESTAMP ;

I can then check the contents of the Global Variable with the following statement:

01 VALUES MYLIB.MY_TIMESTAMP ;

Which returns:

00001 -------------------------- 2025-02-26 07:55:02.287294

My statement to generate the Audit Data Mart is changed to be:

01 CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART( 02 JOURNAL_ENTRY_TYPE => 'CA', 03 DATA_MART_LIBRARY => 'MYLIB', 04 STARTING_TIMESTAMP => '*FIRST', 05 ENDING_TIMESTAMP => MYLIB.MY_TIMESTAMP, 06 DATA_MART_ACTION => 'CREATE') ;

Line 5: I have added this line for the ending timestamp. It uses the value in the timestamp in the MY_TIMESTAMP Global Variable.

The details of the data I have retrieved is not relevant to what I am showing in this post. What is relevant is the number of rows I retrieved from QAUDJRN and populated the Audit Data Mart table with. In the statement below I am getting a count of the number of rows for each Command Type:

01 SELECT COMMAND_TYPE, 02 TO_CHAR(COUNT(*),'999G999') AS "Count" 03 FROM MYLIB.AUDIT_JOURNAL_CA 04 GROUP BY COMMAND_TYPE 05 ORDER BY COMMAND_TYPE ;

Line 2: As I know the results will be in the thousands, I am using the TO_CHAR Scalar Function to format the number.

Lines 4 and 5: I am grouping and ordering the results by the Command Type.

The results are:

COMMAND _TYPE Count ------- ------- GRANT 272,601 REPLACE 773,200 REVOKE 10,006

I take the statement that created the Audit Data Mart and refine the selection by adding the new DATA_MART_FILTER parameter:

01 CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART( 02 JOURNAL_ENTRY_TYPE => 'CA', 03 DATA_MART_LIBRARY => 'MYLIB', 04 STARTING_TIMESTAMP => '*FIRST', 05 ENDING_TIMESTAMP => MYLIB.MY_TIMESTAMP, 06 DATA_MART_FILTER => 'COMMAND_TYPE IN (''GRANT'',''REPLACE'')', 07 DATA_MART_ACTION => 'REPLACE') ;

Line 6: Here is the DATA_MART_FILTER. You can see that the selection criteria I am using is like any other SQL WHERE IN, just without the WHERE.

Line 7: I am not going to create the data mart again, I am going to replace the data within it.

The results should have the same number of rows for GRANT and REPLACE, and there should be no other Command Types. I use the same statement as I did before to perform the counts:

COMMAND _TYPE Count ------- ------- GRANT 272,601 REPLACE 773,200

The results show that only the two Command Types I desired are in the Audit Mart Table.

In my last example I am going to add to the Data Mart Filter to further refine my selection:

01 CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART( 02 JOURNAL_ENTRY_TYPE => 'CA', 03 DATA_MART_LIBRARY => 'MYLIB', 04 STARTING_TIMESTAMP => '*FIRST', 05 ENDING_TIMESTAMP => MYLIB.MY_TIMESTAMP, 06 DATA_MART_FILTER => 'COMMAND_TYPE IN (''GRANT'',''REPLACE'') 07 AND USER_PROFILE_NAME = ''*PUBLIC'' ', 08 DATA_MART_ACTION => 'REPLACE') ;

Lines 6 and 7: My filter extends to a second line, where I only want to select QAUDJRN entries for the user profile *PUBLIC.

After executing the above, I execute the SQL statement to count the rows in the data mart again:

COMMAND _TYPE Count ------- ------- GRANT 14,094 REPLACE 503,390

Both counts have been reduced as I am only interest in the entries for *PUBLIC, the other values in USER_PROFILE_NAME are omitted.

This new parameter is a very useful refinement to an already very useful function.

 

You can learn more about the changes to MANAGE_AUDIT_JOURNAL_DATA_MART procedure from the IBM website here.

 

This article was written for IBM i 7.5 TR5 and 7.4 TR11.