Create SQL Views to interpret QSYS2.DISPLAY_JOURNAL

Last Updated on 10 March 2023 by Roberto De Pedrini

The sources (SQL statement) of this post are available on Github at this link: https://github.com/Faq400Git/Create_Display_Journal_Table_View

Personally I am a big fan of IBM i Services, that series of SQL Functions (UDF and UDTF) and Stored Procedures which, with each Technology Refresh, is enriched in number and functionality.

The QSYS2.DISPLAY_JOURNAL is a UDTF now present since 7.1 of the operating system, and is an excellent alternative to the DSPJRN command to query the journal receiver entries.

Querying Journal events has never been so simple: a SQL statement is able to extract from a Journal (or rather from the Journal Receivers of a Journal) all the events concerning one or more tables under control.

As long as it is a question of querying information about the event on the table (for example event type (insert/delete/update..), event date/time, user, job etc) QSYS2.DISPLAY_JOURNAL is really great, but if we need interpreting the contents of the ENTRY_DATA field (the Blob field with the image of the record) is not exactly that immediate.

Googling here and there I found several ways to read/interpret the contents of this ENTRY_DATA field:

• The official IBM site recommends using the SQL INTEPRET function, mapping the fields of interest to the ENTRY_DATA Blob : IBM Support – How to extract and search for ENTRY_DATA in DISPLAY_JOURNAL table function : excellent, INTERPRET allows us to extract our information from that BLOB field, whether they are in CHAR, VARCHAR, DECIMAL, NUMERIC fields, etc. The problem is to retrieve the “offsets” and “lengths” of each field and set the INTERPRET statement accordingly
• The great Simon Hutchinson, in his mail “Extracting data from journals using SQL” Instead, he explains how to do it from RPG, reading the DISPLAY_JOURNAL records and mapping the ENTRY_DATA field in a DS with the same structure as our table.
• Or fall back to the good old QjoRetrieveJournalEntries API: “ Retrieve Journal Entries (QjoRetrieveJournalEntries) API “
• Even this technique proposed by Sam Lennon could be an alternative: “ Journal Entries Exposed! JOESD Made Readable ! “

Despite my searches on Google I have never found anyone who allows me to read and interpret the Journal Entries directly with SQL without going crazy in mapping field to field according to the table of my interest.

So here’s the idea: create a SQL Stored Procedure that reads the names, types, lengths and offsets of the fields from the SYSCOLUMNS catalog and automatically creates a SQL View on the QSYS2.DISPLAY_JOURNAL table function … in short, something that does the dirty work for me of mapping the entire ENTRY_DATA field according to the table concerned.

Once the Stored Procedure has been created, it will be sufficient to recall it by passing it the following parameters:

• Journal library
• Table name (System Name or SQL Name)
• Journal library
• Journal name
• Library where you want to create the View
• Name of the View
• Flag Y/N for any REPLACE of the View
• Global Variable (or hsot variable from SQL Embedded) where to return the SQL statement of the CREATE VIEW (optional)

call FAQ400.CREATE_DISPLAY_JOURNAL_TABLE_VIEW(MYTABLE_LIBRARY => 'FAQ400JOU', MYTABLE_NAME => 'MYSAMPLETABLE', MYJOURNAL_LIBRARY => 'FAQ400JOU', MYJOURNAL_NAME => 'QSQJRN', MYVIEW_LIBRARY => 'FAQ400JOU', MYVIEW_NANDACEREPAMPL2 => 'V__TABLEMYS 'Y', MYCMD => FAQ400.GV_VARCHAR);

And then query the view to see the journal entries for the relevant table:

select * from FAQ400JOU.V_MYSAMPLETABLE_AUDIT;

Getting something similar to these two images

The source of the CREATE_DISPLAY_JOURNAL_TABLE_VIEW stored procedure, and some examples of use, can be found in my Github at this link: https://github.com/Faq400Git/Create_Display_Journal_Table_View

--- Roberto De Pedrini Faq400.com