02a - SQL (EN)

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:

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

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

--- Roberto De Pedrini Faq400.com
About author

Founder of Faq400 Srl, IBM Champion, creator of Faq400.com and blog.faq400.com web sites. RPG developer since I was wearing shorts, strong IBM i supporter, I have always tried to share my knowledge with others through forums, events and courses. Now, with my company Faq400 Srl, I help companies to make the most of this great platform IBM i.

3 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *