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

Usually this is working:

left outer JOIN TABLE (There are occasional gaps in the sequence numbers
because the system uses internal journal entries for control purposes)

ON A.SEQUENCE_NUMBER = B.SEQUENCE_NUMBER - 1
AND a.COUNT_OR_RRN = b.COUNT_OR_RRN;

HTH

--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco


Il giorno ven 21 feb 2025 alle ore 11:57 Suren K <suren7437@xxxxxxxxx> ha
scritto:

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
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.