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

This might be better asked on the midrange-l list.

But, quickly, without analyzing your query, I *think* the matching UB & UA entries will *always* be together. So, when you get a UB entry, the next one in the journal should be the matching UA entry. It's been several years since I last did something similar. Do not bet the farm on my answer without someone with more recent experience chiming in.

Also, if TESTLIB/TESTFILE has unique keys, you can match UB & UA entries with the keys.

If you have further questions, please move the thread to midrange-l.

- Dan Bale

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Suren K
Sent: Friday, February 21, 2025 5:58 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: 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

*** CONFIDENTIALITY NOTICE: The information contained in this communication may be confidential, and is intended only for the use of the recipients named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please return it to the sender immediately and delete the original message and any copy of it from your computer system. If you have any questions concerning this message, please contact the sender. ***