In this scenario it looks like the trigger just does a simple insert to the secondary log table using fields from the trigger.
I can probably share some code next week once we start playing with it.
We just had the discussion this morning and customer was recalling the issue from 4yrs ago.
He hasn’t revisited it since then so much to discover…..
Regards,
Richard Schoen
Web:
http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx<mailto:richard@xxxxxxxxxxxxxxxxx>
From: Daniel Gross <daniel@xxxxxxxx>
Sent: Thursday, April 09, 2026 12:52 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Richard Schoen <richard@xxxxxxxxxxxxxxxxx>
Subject: Re: Question on SQL After Trigger Locking a Record
Hi Richard,
as it is RPG/II I don't assume any commitment control. But the default isolation level could still be something other than NC.
But there is some good stuff to read in IC:
https://www.ibm.com/docs/en/i/7.6.0?topic=statements-create-trigger
The part with "Transaction isolation" has some important stuff.
Maybe you need to change the trigger to MODE DB2ROW and make explicit commits or use WITH NC.
Does the trigger also make an UPDATE on the original table? If not, it might be, that the row gets locked while the trigger is running and must to be unlocked explicitly.
Without some code, I can only guess.
HTH
Daniel
Am 09.04.2026 um 19:31 schrieb Richard Schoen <richard@xxxxxxxxxxxxxxxxx<mailto:richard@xxxxxxxxxxxxxxxxx>>:
This one is even more interesting because it's OLD RPGII.
They want to use a trigger to capture some data for other newer programs to use.
No commitment control I believe.
On the RPGII screen, the users can enter through several screens and each one does an update even if no data has changed.
I haven't seen the code yet, but probably something like this: chain/update/chain/update/etc......
With a trigger in their it would be something like: chain/update/trigger/chain/update/trigger...
I wonder if a delay for a second after update would help.
We're going to be doing a little testing on this concept next week.
I was mainly curious if there was anything obvious about this scenario.
I'm also pretty sure that chain/release has changed since RPGII days.
This might be the first program in the modernization process we convert to RPGLE to test.
Regards,
Richard Schoen
Web:
http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx<mailto:richard@xxxxxxxxxxxxxxxxx>
-----Original Message-----
From: Daniel Gross <daniel@xxxxxxxx<mailto:daniel@xxxxxxxx>>
Sent: Thursday, April 09, 2026 12:19 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Richard Schoen <richard@xxxxxxxxxxxxxxxxx<mailto:richard@xxxxxxxxxxxxxxxxx>>
Subject: Re: Question on SQL After Trigger Locking a Record
This sounds like a transaction problem.
In what isolation mode does your transaction run?
What is the statement that seems to lock?
How does the trigger code look like?
And do you get any messages in the job locks?
Have you looked into the locked records?
In 99% of all cases, this is a transaction isolation problem - often in combination with activation groups and unclear commit boundaries.
Regards,
Daniel
Am 09.04.2026 um 18:56 schrieb Richard Schoen <richard@xxxxxxxxxxxxxxxxx<mailto:richard@xxxxxxxxxxxxxxxxx>>:
OK, today I have a question about an SQL trigger on a DB2 file.
Customer has a table they want to put a trigger on. That all works.
And the trigger simply does an insert to another table.
However as soon as they added his trigger, the end-user programs (Old RPGII interactive rpg programs) that use the table in question experienced lock-wait conditions.
They are potentially still in the same order doing work once the trigger goes off.
When a trigger goes off I thought it just received an image of the record (similar to a journal entry).
This RPGII program does multiple reads, but it also appears to release the records after each read.
Anyone have thoughts on why a trigger could cause the downstream programs to suddenly lock up waiting on records once a trigger is added.
Input appreciated.
Regards,
Richard Schoen
Web:
http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx<mailto:richard@xxxxxxxxxxxxxxxxx<mailto:richard@xxxxxxxxxxxxxxxxx%3cmailto:richard@xxxxxxxxxxxxxxxxx>>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto: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<mailto: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<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
midrange.com
