SQL table use for journal content display

A client recently wanted something that would pull all of the journal entries from the QAUDJRN for a specific user between a pair of dates. Having played around with the DISPLAY_JOURNAL table function I felt it would be ideal for this situation so set about building the SQL to extract the required entries.

One of the main requirements was to pull the data between specific dates so the ability to set the ending time and start time was important. When I reviewed the information on the Developerworks wiki https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM+i+Technology+Updates/page/DISPLAY_JOURNAL+(easier+searches+of+Audit+Journal) I could see that the start time setting was built into the table but not the ending time until a recent PTF group. The information was not clear exactly when the support was added across the versions so a quick note to Scott Forstie confirmed that the ending time capabilities were only added to the V7R2 and V7R3 release of the OS. This client was on V7R1 so the capability does not exist. This required us to write a program that would retrieve all of the entries and write them out to a file for further analysis.

While the program did what it needed and we could extract the required data the time taken was significantly more than if we were able to use the DISPLAY_JOURNAL table function (it took many hours to build and test the code before we could run it on the clients system).
 
This is one of the reasons keeping up to date with IBM i releases is important. We spent over 8 hours building and testing the program and the output it created,  where as it took just over less than 1 hour to build and test the SQL script that would display the same content we dumped to the file.

Here is the code for the SQL, the program code we used for the journal scrape is for internal use only as we use similar technology for our HA products.

SELECT ENTRY_TIMESTAMP as JE_TIME, CURRENT_USER as CURRENT_USR, SEQUENCE_NUMBER, JOURNAL_ENTRY_TYPE,JOB_NAME, JOB_USER, JOB_NUMBER, PROGRAM_NAME, OBJECT,OBJECT_TYPE,PATH_NAME FROM TABLE(QSYS2.DISPLAY_JOURNAL(‘QSYS’,’QAUDJRN’,STARTING_RECEIVER_NAME => ‘*CURCHAIN’,STARTING_TIMESTAMP => TIMESTAMP(‘20160725000000’), ENDING_TIMESTAMP => TIMESTAMP(‘20160725235959’))) as J WHERE JOB_USER = ‘CHRISH’ ORDER BY ENTRY_TIMESTAMP

The program output does allow SQL functions to be run against it and allow other filters to be added, but adding a INSERT to a table for the above would result in the same outcome or you cold amend the SQL above to add additional filters. The program also provides access to a lot more information than we can get from the DISPLAY_JOURNAL table function if we needed to use it.

IBM is making it easier to get to journal information, we have only used it against the QAUDJRN in this instance but it is just as effective against User Journals.

Enjoy.

Chris..