Triggers, Commitment Control and JDBC.

This may be a discussion for Java but it seems to cross boundaries.

This is threatening a major development project and my head is on the block.


We have new generation tables that are defined in SQL and we also have our
existing tables in the legacy system.

We make a change to tables in the new generation system we have triggers
wired on that fire and run RPG programs that update the legacy side.

These programs run in the default activation group.

So we have a Java program that does the following:

UPDATE new generation table a.
Trigger fires and updates legacy table b
UPDATE new generation table c
Trigger fires and update legacy table d
UPDATE new generation table d
Error occurs.
ROLLBACK runs.

If we go back and look at the legacy tables we see that the update to table
in the trigger did not occur but the rollback in the new generation tables
did.

Now I have been able to reproduce the problem in Run Sql Scripts in Ops Nav
to make the same thing happen.

If I issue the following

UPDATE COMPANY SET NAME = 'Company Aa' WHERE ID = 1;
ROLLBACK;

when I go look at the database table COMPANY is still at original value of
NAME and the legacy table has still been update.

If I issue the following:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED, READ WRITE;
UPDATE COMPANY SET NAME = 'Company Aa' WHERE ID = 1;
ROLLBACK;

everything rolls back just like it should.

It appears that SQL only scopes the commitment level to tables that are
directly being updated and not to the trigger.

I have no idea why the SET TRANSACTION LEVEL causes it to scope to include
trigger programs. so first question is why does this work?

Our big problem is that we are using Hibernate in Java to do the work and so
we don't control what get issued and anytime we issue an update the changes
to legacy table do not get rolled back.

I have been on the line with IBM for a week and cannot get anything out of
them.

Anybody have any idea how to solve this? I have seen on-line statements by
people on this forum that indicates to me they might have a much better
understanding of how this stuff works. At least that is what I am hoping.