Creating and using a data journal reader

Every so often IBM delivers something for the IBM i that makes me say "Wow!" Data journal readers are one of these cases. It has changed the way I am thinking of retrieving data from journals.

The CREATE_DATA_JOURNAL_READER SQL scalar function was introduced as part of IBM i 7.6 TR2 and 7.5 TR8, and is very simple to set up. It creates a SQL table function that allows me to see the entries for one of the files whose data is within a journal.

Before I can show examples of this, I need a file and it needs to be journaled.

I created a DDS file, TESTFILE, in my library, MYLIB. It contains nine fields I named FIELD1 to FIELD9. The layout of the file is immaterial to showing how to create and use a data journal reader.

Once the file was created, I needed it to be journaled. Which I did with the following commands:

01 CRTJRNRCV JRNRCV(MYLIB/TESTRCV) TEXT('Test journal receiver') Journal receiver TESTRCV created in library MYLIB. 02 CRTJRN JRN(MYLIB/TESTJRN) JRNRCV(MYLIB/TESTRCV) Journal TESTJRN created in library MYLIB. 03 STRJRNPF FILE(MYLIB/TESTFILE) JRN(MYLIB/TESTJRN) IMAGES(*BOTH) 1 of 1 files have started journaling.

I have included the messages from the job logs too.

Line 3: I decided to capture both the before and after images for the data in the file, hence the IMAGES parameter is both.

I can use the SQL view JOURNALED_OBJECTS to confirm that the file is being journaled by the journal I created:

01 SELECT OBJECT_TYPE,OBJECT_LIBRARY,OBJECT_NAME 02 FROM QSYS2.JOURNALED_OBJECTS 03 WHERE JOURNAL_LIBRARY = 'MYLIB' 04 AND JOURNAL_NAME = 'TESTJRN'

Which returns:

OBJECT_TYPE OBJECT_LIBRARY OBJECT_NAME ----------- -------------- ----------- *JRNRCV MYLIB TESTRCV *FILE MYLIB TESTFILE

I did not include the journal name and library columns in the results, as these were part of the Select statement.

As CREATE_DATA_JOURNAL_READER I can execute the scalar function using a Values SQL statement.

It has three parameters:

• LIBRARY_NAME:  Name of the library that contains the file
• FILE_NAME:  Name of the file
• OUTPUT_LIBRARY:  Output library where the table function will be created in

In this example the file is TESTFILE, in the library MYLIB, and I want to to create the output file in the library MYLIB2.

01 VALUES QSYS2.CREATE_DATA_JOURNAL_READER( 02 LIBRARY_NAME => 'MYLIB', 03 FILE_NAME => 'TESTFILE', 04 OUTPUT_LIBRARY => 'MYLIB2')

When the above statement, like all the other similar scalar functions, is executed it returns a return code:

00001 ------ 1

The value of "1" means that the table function was created.

The table functions created by this scalar function are named in the following format: DISPLAY_JOURNAL_< file_library >_< file name >

In this example the Table function created is called: DISPLAY_JOURNAL_MYLIB_TESTFILE

To check that this was created where I expected, and to learn it short name, I can use the OBJECT_STATISTICS table function:

01 SELECT OBJLONGNAME,OBJNAME,OBJTYPE 02 FROM TABLE(OBJECT_STATISTICS('MYLIB2','ALL')) 03 WHERE OBJLONGNAME LIKE 'DISPLAY_JOURNAL_%'

Which returns:

OBJLONGNAME OBJNAME OBJTYPE ------------------------------ ---------- ------- DISPLAY_JOURNAL_MYLIB_TESTFILE DISPL00001 *SRVPGM

All table functions are service programs.

Now I can use my new table function, as I would use any other, to retrieve information from the journal:

01 SELECT ENTRY_TIMESTAMP,OPERATION,PROGRAM_NAME, 02 FIELD1,FIELD2, ... ,FIELD8,FIELD9 03 FROM TABLE(MYLIB2.DISPLAY_JOURNAL_MYLIB_TESTFILE())

Line 1: I want to return the time the entry was written to the journal, operation that happened to the file, and the name of the program that made that change.

Line 2: I wanted to display all of the file's fields from the journal entry. This is my shortcut to save me having to enter all the names FIELD1 - FIELD9.

Line 3: All of the DISPLAY_JOURNAL_XX_XX table functions have parameters. These are the same parameters as those used by the DISPLAY_JOURNAL table function. In this example I want all the journal entries in my results.

I have included a sample of the results:

PROGRAM ENTRY_TIMESTAMP OPERATION _NAME FIELD1 FIELD2 -------------------------- --------------- ------- -------- ------ 2026-08-18 17:23:05.495296 INSERT EG258R0 FIRST 12345 2026-08-18 17:23:05.495296 INSERT EG258R0 SECOND 2 2026-08-18 17:23:05.495296 INSERT EG258R0 THIRD 3 2026-08-18 17:23:05.507200 UPDATE (before) EG258R1 THIRD 3 2026-08-18 17:23:05.507200 UPDATE (after) EG258R1 THIRD 3 2026-08-18 17:23:05.507504 UPDATE (before) EG258R1 FIFTH 5 2026-08-18 17:23:05.507504 UPDATE (after) EG258R1 FIFTH 5 2026-08-18 17:23:05.509984 DELETE EG258R1 TENTH 10

I like having both the before and after images for an update, and the before image for a delete too, which is why when I added TESTFILE to the journal I captured both images.

As the data journal reader has been created, I can use whenever to get this file's information from the journal, and not have to extract the file's data from a hexadecimal string.

If one journal is capturing information for more than one file, I can build a data journal reader for each file within it.

This is another very good reason to not use the DSPJRN command to retrieve data from journals.

 

You can learn more about this from the IBM website:

• CREATE_DATA_JOURNAL_READER SQL scalar function
• Using a data journal reader

 

This article was written for IBM i 7.6 TR2 and 7.5 TR8.