Triggers and SQLSTATE 428F9

bigmickey · #1 (**permalink**) 11-10-09, 07:40

Hello,

I have problem with triggers. There are two triggers: before (abi) and after (aai) insert. The signalled error is in aai with tokens from abi

Both triggers should work separately and should know nothing about each other. I know there are some exceptions when NEXT VALUE cannot be used but it seems it does not cover my problem. What is wrong and how to solve it then?
(environment: db2 9.5 with no patches on Linux)

create table a (i integer)!
create table b (i integer)!
CREATE SEQUENCE sq AS integer!
CREATE TRIGGER abi BEFORE INSERT ON a REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET N.i = NEXTVAL FOR sq;
END!
CREATE TRIGGER aai AFTER INSERT ON a REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
insert into b (i)
WITH x(xi) AS (values N.i UNION ALL SELECT xi+1 FROM x WHERE xi<N.i+10)
SELECT xi FROM x;
END!
insert into a values (0)!

Error: SQL0723N An error occurred in a triggered SQL statement in trigger "DB2INST1.AAI". Information returned for the error includes SQLCODE "-348", SQLSTATE "428F9" and message tokens "NEXTVAL FOR DB2INST1.SQ"

If we replace a recursive with simple "insert into b (i) values (n.i);" or remove any trigger it will be working properly. Unfortunately on production I have to have a sequence call before and recursive query after insert.

n_i · #2 (**permalink**) 11-10-09, 08:05

I think you can try to look at the INSERT explain plan to see what is it that DB2 is trying to execute.

bigmickey · #3 (**permalink**) 11-12-09, 05:42

Unfortunately the explain plan in such situation is not accessible. I've got the same error.
Any other ideas? Is it known problem? Maybe it is an additional nonsensical exception in DB2 making the programmers to be cross?

sathyaram_s · #4 (**permalink**) 11-12-09, 06:42

Appears to be the way it will/should work ... Sequences are not supported in recursive CTEs.

Open a PMR and confirm with IBM ...

The workaround is to use a Stored Proc in the After Trigger

Cheers
Sathyaram

tonkuma · #5 (**permalink**) 11-12-09, 07:04

It looks worked well on my DB2 9.7 for Windows.

Code:

------------------------------ Commands Entered ------------------------------
connect to SAMPLE ;
------------------------------------------------------------------------------

   Database Connection Information

 Database server        = DB2/NT 9.7.0
 SQL authorization ID   = DB2ADMIN
 Local database alias   = SAMPLE


A JDBC connection to the target has succeeded.
------------------------------ Commands Entered ------------------------------
create table a (i integer)!
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
create table b (i integer)!
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
CREATE SEQUENCE sq AS integer!
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
CREATE TRIGGER abi BEFORE INSERT ON a REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC
SET N.i = NEXTVAL FOR sq;
END!
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
CREATE TRIGGER aai AFTER INSERT ON a REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC 
insert into b (i)
WITH x(xi) AS (values N.i UNION ALL SELECT xi+1 FROM x WHERE xi<N.i+10) 
SELECT xi FROM x;
END!
------------------------------------------------------------------------------
SQL0347W  The recursive common table expression "DB2ADMIN.X" may contain an 
infinite loop.  SQLSTATE=01605

------------------------------ Commands Entered ------------------------------
insert into a values (0)!
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM a!
------------------------------------------------------------------------------

I          
-----------
          1

  1 record(s) selected.


------------------------------ Commands Entered ------------------------------
SELECT * FROM b!
------------------------------------------------------------------------------

I          
-----------
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10
         11

  11 record(s) selected.

bigmickey · #6 (**permalink**) 11-12-09, 11:21

Thank you to both of you. The workaround is working and as for now it's the only solution in my case.

sathyaram_s · #7 (**permalink**) 11-12-09, 12:10

That's interesting ... Can you post the explain plan please, if at all possible ...

Quote:

Originally Posted by tonkuma

It looks worked well on my DB2 9.7 for Windows.

Code:

------------------------------ Commands Entered ------------------------------
connect to SAMPLE ;
------------------------------------------------------------------------------

   Database Connection Information

 Database server        = DB2/NT 9.7.0
 SQL authorization ID   = DB2ADMIN
 Local database alias   = SAMPLE


A JDBC connection to the target has succeeded.
------------------------------ Commands Entered ------------------------------
create table a (i integer)!
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
create table b (i integer)!
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
CREATE SEQUENCE sq AS integer!
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
CREATE TRIGGER abi BEFORE INSERT ON a REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC
SET N.i = NEXTVAL FOR sq;
END!
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
CREATE TRIGGER aai AFTER INSERT ON a REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC 
insert into b (i)
WITH x(xi) AS (values N.i UNION ALL SELECT xi+1 FROM x WHERE xi<N.i+10) 
SELECT xi FROM x;
END!
------------------------------------------------------------------------------
SQL0347W  The recursive common table expression "DB2ADMIN.X" may contain an 
infinite loop.  SQLSTATE=01605

------------------------------ Commands Entered ------------------------------
insert into a values (0)!
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM a!
------------------------------------------------------------------------------

I          
-----------
          1

  1 record(s) selected.


------------------------------ Commands Entered ------------------------------
SELECT * FROM b!
------------------------------------------------------------------------------

I          
-----------
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10
         11

  11 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech