Re: Triggers, Commitment Control and JDBC.

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.