Reg: Help with Fetching Before and After Images Using Journal in IBM i (DISPLAY_JOURNAL)

Hi All,

I need to write a query to retrieve the before and after images of a record
when it gets updated using the journal. I'm using the DISPLAY_JOURNAL view
and attempting a self-join on the same view to achieve this. However, I’m
unable to find a column with the same value for both UB (before update) and
UP (after update) records to properly match them

Here’s the query I’m working with:

SELECT A.ENTRY_TIMESTAMP, A.JOURNAL_ENTRY_TYPE,
SUBSTRING(A.OBJECT,1,10) AS OBJECT_NAME,
CAST(SUBSTR(A.ENTRY_DATA,1,147) AS CHAR(147)) AS BEFORE_IMAGE,
CAST(SUBSTR(B.ENTRY_DATA,1,147) AS CHAR(147)) AS AFTER_IMAGE
FROM
TABLE(qsys2.display_journal('TESTLIB', 'TESTJRN',
OBJECT_LIBRARY=>'TESTLIB', OBJECT_NAME=>'TESTFILE',
OBJECT_OBJTYPE=> '*FILE', OBJECT_MEMBER=>'FILEMBR',
JOURNAL_ENTRY_TYPES=>'UB' )) AS A
INNER JOIN
TABLE(qsys2.display_journal('TESTLIB', 'TESTJRN',
OBJECT_LIBRARY=>'TESTLIB', OBJECT_NAME=>'TESTFILE',
OBJECT_OBJTYPE=> '*FILE', OBJECT_MEMBER=>'FILEMBR',
JOURNAL_ENTRY_TYPES=>'UP' )) AS B
ON A.JOURNAL_IDENTIFIER = B.JOURNAL_IDENTIFIER;


I initially assumed that JOURNAL_IDENTIFIER would be unique for all records
except UP/UB rows, but it appears to be unique across files rather than
within the same file. I also tried using the COUNT_OR_RRN field, but it
only shows the total record count instead of the Relative Record Number
(RRN).

I noticed that ENTRY_TIMESTAMP has the same value for the UP and UB
records, but I am unsure whether relying on this field for matching is
correct.

Does anyone know of another field I could use to accurately match UB and UP
records? Any suggestions would be greatly appreciated!

Regards,
Suren