Trigger Failure Cancels calling transaction

[JohnO]

Hi Folks,

I have an update trigger that fails (it inserts an audit table record)
in some circumstances. This is causing the triggering transaction to
fail and roll back. Is there any way to prevent this? If the trigger
fails I still want the triggering transaction to continue.

Cheers,
JohnO

[Serge Rielau]

JohnO wrote:

Hi Folks,

I have an update trigger that fails (it inserts an audit table record)
in some circumstances. This is causing the triggering transaction to
fail and roll back. Is there any way to prevent this? If the trigger
fails I still want the triggering transaction to continue.

Cheers,
JohnO

You can CALL a proceduer from the trigger (in DB2 V8.2). The procedure
can handle th eexception.

Now I do have a comment: You want to allow the audit to fail, but the
transaction to continue. Does that sound right? Are you 100% sure you
want that?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[JohnO]

Hi and thanks, Serge.

The answer to your question is no - I don't! But it's a difficult
problem. Please allow me to explain:

My trigger is a simple after update trigger used to maintain an audit
trail as shown below.

This trigger usually works just fine (in a JDEdwards OneWorld
environment which normally uses commit-immediate). However when the
triggering application is running within JDE's transaction control, the
trigger fails, with some strange (to me) looking errors. It seems that
the database is unhappy with my trigger when the triggering sql is
running within commitment control. I'm not sure how JDE does this but
from the logs below it doesn't look pretty.

Here's my trigger creation code:

create trigger TESTDTA.tr_F4102_au
after UPDATE on TESTDTA.F4102
referencing old table as oldtable
for each statement
insert into A_TESTDTA.a_F4102 select oldtable.*, user, current
timestamp, 'TESTDTA.tr_F4102_au', ow_audit.getjobname(0) from oldtable
;

And when JDE tries to do an update on the table within a commit
controlled transaction, the following is written by JDE's logging:

Aug 03 09:17:37 ** 3744/3140 UPDATE TESTDTA/F4102 SET
IBITM=1571614.000000,IBMCU='
1312',IBVEND=25748.000000,IBMRPC='1',IBUSER='JDE', IBPID='EP4114',IBJOBN='BUSERP1',IBUPMJ=105215,IBTD AY=91736.000000
WHERE ( IBITM = 1571614.000000 AND IBMCU = ' 1312' )
Aug 03 09:17:41 ** 3744/3140 SUCCESS#Journaling has already been
started for this file. Command: STRJRNPF FILE(TESTDTA/F4102 )
JRN(OWJRNL/OW_JRNL ) IMAGES(*BOTH) OMTJRNE(*OPNCLO).
3744/3140 Wed Aug 03 09:17:41 2005 JDBODBC.C2148
ODB0000183 - SQLExecute failed

3744/3140 Wed Aug 03 09:17:41 2005 JDBODBC.C2148
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0723 - SQL trigger
TR_F4102_AU in TESTDTA failed with SQLCODE -7008 SQLSTATE 55019. -
SQLSTATE: S1000

3744/3140 Wed Aug 03 09:17:41 2005 jdb_drvm.c938
JDB9900401 - Failed to execute db request

3744/3140 Wed Aug 03 09:17:41 2005 jdb_exet.c2593
JDB3600011 - Failed to perform Update for F4102

Aug 03 09:17:41 ** 3744/3140 Entering JDB_CancelUserTransaction
Aug 03 09:17:41 ** 3744/3140 Cancelling Transaction :
1459943178_3744_3860_165816024 Commit Point Number : 1

So I guess JDE is turning on journalling for the table for the duration
of it's transaction, and setting commit points. However I don't
understand what is upsetting my trigger. It seems like my trigger needs
to inherit the triggering SQL's transaction boundaries? I have a
feeling that JDE's control method is a kludge. Hence my earlier comment
about not being so concerned about my trigger failing as long as it
doesn't upset the triggering application.

Any suggestions?

Thanks,
JohnO

[JohnO]

Arghhhhh!

<slaps head>

The trigger is inheriting the transaction boundary of the parent
transaction, but the audit table that the trigger is inserting is not
journalled so is inconpatible with the commitment mode of the parent
transaction. I think I just need to ensure any tables modified by the
trigger are journalled!

Cheers,
JohnO

[JohnO]

.... so the next question is: How can I start journalling on a table
within an SQL procedure? The reason I ask is that I have an SQL
procedure that I use to start auditing on any table by creating and
executing dynamic sql to create the audit table and then to create the
trigger. This procedure just needs to start journalling on the audit
table after it is created.

I know I can use a CL command to do this: STRJRNPF FILE(Lib/File)
JRN(JrnlLib/JrnlFile) IMAGES(*BOTH) OMTJRNE(*OPNCLO). How can I invoke
this from within an SQL procedure? Or do I need to write a CL stored
procedure to do this?

Cheers,
JohnO

[Serge Rielau]

JohnO wrote:

Hi and thanks, Serge.

The answer to your question is no - I don't! But it's a difficult
problem. Please allow me to explain:

My trigger is a simple after update trigger used to maintain an audit
trail as shown below.

This trigger usually works just fine (in a JDEdwards OneWorld
environment which normally uses commit-immediate). However when the
triggering application is running within JDE's transaction control, the
trigger fails, with some strange (to me) looking errors. It seems that
the database is unhappy with my trigger when the triggering sql is
running within commitment control. I'm not sure how JDE does this but
from the logs below it doesn't look pretty.

Here's my trigger creation code:

create trigger TESTDTA.tr_F4102_au
after UPDATE on TESTDTA.F4102
referencing old table as oldtable
for each statement
insert into A_TESTDTA.a_F4102 select oldtable.*, user, current
timestamp, 'TESTDTA.tr_F4102_au', ow_audit.getjobname(0) from oldtable
;

And when JDE tries to do an update on the table within a commit
controlled transaction, the following is written by JDE's logging:

Aug 03 09:17:37 ** 3744/3140 UPDATE TESTDTA/F4102 SET
IBITM=1571614.000000,IBMCU='
1312',IBVEND=25748.000000,IBMRPC='1',IBUSER='JDE', IBPID='EP4114',IBJOBN='BUSERP1',IBUPMJ=105215,IBTD AY=91736.000000
WHERE ( IBITM = 1571614.000000 AND IBMCU = ' 1312' )
Aug 03 09:17:41 ** 3744/3140 SUCCESS#Journaling has already been
started for this file. Command: STRJRNPF FILE(TESTDTA/F4102 )
JRN(OWJRNL/OW_JRNL ) IMAGES(*BOTH) OMTJRNE(*OPNCLO).
3744/3140 Wed Aug 03 09:17:41 2005 JDBODBC.C2148
ODB0000183 - SQLExecute failed

3744/3140 Wed Aug 03 09:17:41 2005 JDBODBC.C2148
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0723 - SQL trigger
TR_F4102_AU in TESTDTA failed with SQLCODE -7008 SQLSTATE 55019. -
SQLSTATE: S1000

3744/3140 Wed Aug 03 09:17:41 2005 jdb_drvm.c938
JDB9900401 - Failed to execute db request

3744/3140 Wed Aug 03 09:17:41 2005 jdb_exet.c2593
JDB3600011 - Failed to perform Update for F4102

Aug 03 09:17:41 ** 3744/3140 Entering JDB_CancelUserTransaction
Aug 03 09:17:41 ** 3744/3140 Cancelling Transaction :
1459943178_3744_3860_165816024 Commit Point Number : 1

So I guess JDE is turning on journalling for the table for the duration
of it's transaction, and setting commit points. However I don't
understand what is upsetting my trigger. It seems like my trigger needs
to inherit the triggering SQL's transaction boundaries? I have a
feeling that JDE's control method is a kludge. Hence my earlier comment
about not being so concerned about my trigger failing as long as it
doesn't upset the triggering application.

Any suggestions?

Hmm, SQLSTATE 55019 means: "table in an invalid state for the
operation". In DB2 for LUW this would imply check pending

I'm neither an expert on iSeries nor JDEdwards, I'm affraid.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[JohnO]

Thanks Serge - That was the clue - I cracked the problem as detailed in
my followups.