Trigger fails when stored procedure is called two times in a row

[Antanas]

I am puzzled by the following behavior.
If stored procedure PROC1 is called 2 times in trigger TRIG1, then I
get error during trigger firing:

SQL0723N An error occurred in a triggered SQL statement in trigger
"DB2ADMIN.TRIG1". Information returned for the error includes SQLCODE
"-746", SQLSTATE "57053" and message tokens
"DB2ADMIN.PROC1|SQL051130081021635|TRIG". SQLSTATE=09000
CREATE TRIGGER TRIG1 AFTER UPDATE OF USER ON TABLE1
REFERENCING NEW AS TriggeredRow
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
CALL DB2ADMIN.PROC1(TriggeredRow.ID, TriggeredRow.USER, 'Situation 1');
CALL DB2ADMIN.PROC1(TriggeredRow.ID, TriggeredRow.USER, 'Situation
2');
END
$

PROC1 is SQL stored procedure that inserts records into TABLE2.
If I call PROC1 just once in TRIG1, then it works fine. Why?

[Brian Tkatch]

db2 => ? sql-746
SQL0746N Routine "<routine-name>" (specific name
"<specific-name>") violated nested SQL statement rules
when attempting to perform operation "<operation>" on
table "<table-name>".

Explanation:

The routine "<routine-name>" (specific name
"<specific-name>") attempted to perform the operation
"<operation>" on table "<table-name>". This operation conflicts
with other uses of the table by either the application, or a
routine invoked directly or indirectly from that application.

If the operation is "READ", then the table "<table-name>" is
currently being written to by either the application or another
routine.

If the operation is "MODIFY", then the table "<table-name>" is
already being read from or written to by the application or another
routine.

If "<table-name>" references an explain table and the statement
that receives this error message is either a PREPARE statement or
an EXECUTE IMMEDIATE statement, then a conflict occurred when
inserting explain information into the explain table.

User Response:

The operation may be successful if retried. Redesign either the
application or the routine to avoid the conflict.

If the conflict occurred when inserting explain information for
a dynamic statement, then disable explain for dynamic statements
and try the PREPARE statement or EXECUTE IMMEDIATE statement
again.

sqlcode : -746

sqlstate : 57053

---

It sounds to me as the two operations cannot both be done at the same
time.

B.

[Antanas]

But why?

[Brian Tkatch]

The error message says why.

SQL0746N Routine "<routine-name>" (specific name
"<specific-name>") violated nested SQL statement rules
when attempting to perform operation "<operation>" on
table "<table-name>".

So you have that error message (with the variables filled in)?

[Serge Rielau]

Antanas wrote:

I am puzzled by the following behavior.
If stored procedure PROC1 is called 2 times in trigger TRIG1, then I
get error during trigger firing:

SQL0723N An error occurred in a triggered SQL statement in trigger
"DB2ADMIN.TRIG1". Information returned for the error includes SQLCODE
"-746", SQLSTATE "57053" and message tokens
"DB2ADMIN.PROC1|SQL051130081021635|TRIG". SQLSTATE=09000
CREATE TRIGGER TRIG1 AFTER UPDATE OF USER ON TABLE1
REFERENCING NEW AS TriggeredRow
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
CALL DB2ADMIN.PROC1(TriggeredRow.ID, TriggeredRow.USER, 'Situation 1');
CALL DB2ADMIN.PROC1(TriggeredRow.ID, TriggeredRow.USER, 'Situation
2');
END
$

PROC1 is SQL stored procedure that inserts records into TABLE2.
If I call PROC1 just once in TRIG1, then it works fine. Why?

This is working as designed see error message for -723.
Its being worked on for a V8 fixpack. Feel free to open a PMR for
further assistance.

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

[Serge Rielau]

Antanas wrote:

But why?

In the SQL Standard AFTER triggers are defined to fire after ALL ros
have been UPDATES/INSERTED/DELETED.
DB2 analyzes the body of the trigger to find out whether it can
streamline the trigger to execute per row as the row gets modified.
Since the CALL statement does not tell which tables are modifyed in teh
stored procedure DB2 flags all changed tables to prevent breakage of the
SQL Standard semantics. If read/wrote or write/write conflict (aka.
mutating table conflict) is detected you get this error.

Turns out this error is encountered more often than anticipated and
development is working on a fix. Feel free to open a PMR.

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

[Antanas]

Thanks, Serge.