Re: Triggers, Commitment Control and JDBC.

Alan,

Seems like you've pretty much figure out your issues.

During our discussions about your trigger mediator, I believe I may
have mentioned that the IBM manuals recommend *CALLER for trigger
programs.

Included in the trigger buffer is a field that tells you if commitment
control is being used or not. So our trigger programs check that
field then close and reopen the file with or without commitment
control as needed.

HTH,
Charles

On Fri, Sep 25, 2009 at 8:52 PM, Alan Campin <alan0307d@xxxxxxxxx> wrote:

Never mind, I figured it out. (Only a week and a half worth of work!).

The trick is that the program or service program must be have the following
to work.

1. If JDBC or any call from PC environment.

a. Trigger program or service program must run in the same activation group
(*CALLER - The default activation group).

JDBC or PC enviroment programs use a default commitment scope of *ACTGRP so
if your trigger runs in a different activation group all pending commitments
are made when the activation group ends.

b. The program must be compiled or running under the same commitment level
as the caller.

If for example you run your JDBC job under READ UNCOMMITTED and the program
is compiled to *NONE or READ COMMITTED, any database transactions inside the
trigger will get committed when the call to the trigger ends.

2. Running from an AS/400 program that fires a trigger and uses commitment
control.

a. If you use STRCMTCTL CMTSCOPE(*JOB) the activation group does not matter.
If you do not use STRCMTCTL and your trigger runs in another activation
group, the transactions will be committed when the trigger ends.

b. You must still have the commit level the same. If the trigger is at a
different commitment level the system will commit the transactions when the
trigger ends.

One other thing. If you issue the following SQL command before you start
your transactions(PC or AS/400) none of this matters. Why I don't know.

SET TRANSACTION ISOLATION LEVEL <Your level), READ WRITE

This for some reason fixes the problem and it doesn't seem to matter what
the activation group or compile level of the triggers are. Our problem was
because we were using Hibernate we could not issue this command.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.