Yeah a data queue might be a good approach or perhaps journal scraping as you mentioned in our other conversation.
Regards,
Richard Schoen
Web:
http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx
----------------------------------------------------------------------
message: 1
date: Fri, 10 Apr 2026 02:41:20 +0000 (UTC)
from: Mark Waterbury via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
subject: Re: Question on SQL After Trigger Locking a Record
Or just have the "after" trigger (after inert, after change?) write a copy of the relevant data to a data queue, and have a completely separate job that listens to the data queue and writes the data to the relevant LOG FILE.? ?That way, it has its own job, locks, activation groups, open files, etc., and so is totally independent of whatever the ancient RPG-II code is doing.
I have used this approach when interfacing with really old applications, without having to do any "major surgery" to the old code, with much success.
On Thursday, April 9, 2026 at 10:02:46 PM EDT, x y <xy6581@xxxxxxxxx> wrote:
I'm with Daniel: this sounds like a commitment control ("transaction")
issue and SQL loves to commit.? Mixing OPM and SQL seems like a dog's
breakfast but sometimes such situations are out of our control.
Ideas:
1) Add a COMMIT=*NO to the SQL trigger program (but don't know if you can
do it).
2) Create the trigger program with the *DFT activation group so the trigger
runs in the same activation group as the application (don't know if you can
do this either).
3) Rewrite the trigger in RPG and get your activation groups straight.
4) Execute STRCMTCTL LCKLVL(*CHG) CMTSCOPE(*JOB) when the job initiates.
On Thu, Apr 9, 2026 at 12:08?PM Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
On Thu, Apr 9, 2026 at 11:31?AM Richard Schoen <richard@xxxxxxxxxxxxxxxxx>
wrote:
They are potentially still in the same order doing work once the trigger
goes off.
<snip>
I wonder if a delay for a second after update would help.
I believe you're under the mistaken impression that the trigger is run "out
of band" or asynchronous to the RPG program.
This is not the case.? When the RPG WRITE/UPDATE op-code is hit, and
control passes to Db2, control will not return to the RPG program until the
trigger is finished.
Like others, I suspect the program is a mismatch in isolation levels, which
shouldn't occur as an SQL Trigger normally inherits the isolation level of
the application.
With an RPG trigger, you have to have code to check the "Commit Lock Level"
field of the trigger buffer passed in.
It'd be helpful for you to confirm which table/record is being waited on.
Also, it'd be helpful to see the code for the trigger.
Charles
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
