SQL0101N and SQL0723N - Recursive Triggers Should Not be Happening

[Michael]

I want to insert a row into an ITEM table if certain SKUs are inserted.
There are two triggers where each looks for a particular SKU and inserts the
appropriate matching row in the same table. However when a record is
inserted, the following error message occurs:

Under DB2 UDB v7.2
[IBM][CLI Driver][DB2/NT] SQL0101N The statement is too long or too
complex. LINE NUMBER=2. SQLSTATE=54001

Under DB2 UDB v8.1
[IBM][CLI Driver][DB2/NT] SQL0723N An error occurred in a triggered SQL
statement in trigger "DBA.ITEM_B". Information returned for the error
includes SQLCODE "-101", SQLSTATE "54001" and message tokens "".
SQLSTATE=09000

Both messages point to a recursion of triggers. This seems to be confirmed
by dropping either of the two triggers. The trouble is the condition for
the triggers should never happen. I've provided sample SQL statements below
to recreate the situation. Why is this happening? Thanks in advance.

CREATE TABLE ITEM (
ORDER_NO CHARACTER(10) NOT NULL,
SKU_NO CHARACTER(10) NOT NULL,
UNITS INTEGER NOT NULL
)
;

CREATE TRIGGER ITEM_A AFTER INSERT ON ITEM REFERENCING NEW AS N_ROW FOR EACH
ROW MODE DB2SQL WHEN
(
n_row.sku_no = 'A1'
)
begin atomic
INSERT INTO ITEM
(ORDER_NO, SKU_NO, UNITS)
VALUES (N_ROW.ORDER_NO, 'A2', 1);
end

CREATE TRIGGER ITEM_B AFTER INSERT ON ITEM REFERENCING NEW AS N_ROW FOR EACH
ROW MODE DB2SQL WHEN
(
n_row.sku_no = 'B1'
)
begin atomic
INSERT INTO ITEM
(ORDER_NO, SKU_NO, UNITS)
VALUES (N_ROW.ORDER_NO, 'B2', 1);
end

INSERT INTO ITEM
(ORDER_NO, SKU_NO, UNITS)
VALUES
('100', 'C1', 1);

[Serge Rielau]

Michael wrote:

I want to insert a row into an ITEM table if certain SKUs are inserted.
There are two triggers where each looks for a particular SKU and inserts the
appropriate matching row in the same table. However when a record is
inserted, the following error message occurs:

Under DB2 UDB v7.2
[IBM][CLI Driver][DB2/NT] SQL0101N The statement is too long or too
complex. LINE NUMBER=2. SQLSTATE=54001

Under DB2 UDB v8.1
[IBM][CLI Driver][DB2/NT] SQL0723N An error occurred in a triggered SQL
statement in trigger "DBA.ITEM_B". Information returned for the error
includes SQLCODE "-101", SQLSTATE "54001" and message tokens "".
SQLSTATE=09000

Both messages point to a recursion of triggers. This seems to be confirmed
by dropping either of the two triggers. The trouble is the condition for
the triggers should never happen. I've provided sample SQL statements below
to recreate the situation. Why is this happening? Thanks in advance.

CREATE TABLE ITEM (
ORDER_NO CHARACTER(10) NOT NULL,
SKU_NO CHARACTER(10) NOT NULL,
UNITS INTEGER NOT NULL
)
;

CREATE TRIGGER ITEM_A AFTER INSERT ON ITEM REFERENCING NEW AS N_ROW FOR EACH
ROW MODE DB2SQL WHEN
(
n_row.sku_no = 'A1'
)
begin atomic
INSERT INTO ITEM
(ORDER_NO, SKU_NO, UNITS)
VALUES (N_ROW.ORDER_NO, 'A2', 1);
end

CREATE TRIGGER ITEM_B AFTER INSERT ON ITEM REFERENCING NEW AS N_ROW FOR EACH
ROW MODE DB2SQL WHEN
(
n_row.sku_no = 'B1'
)
begin atomic
INSERT INTO ITEM
(ORDER_NO, SKU_NO, UNITS)
VALUES (N_ROW.ORDER_NO, 'B2', 1);
end

INSERT INTO ITEM
(ORDER_NO, SKU_NO, UNITS)
VALUES
('100', 'C1', 1);

The problem is not one of runtime (i.e. whether DB2 enters teh recursion.
It is about compile time. DB2 tries to expand all _possible_ recursions
16 levels deep.
So the one insert can trigger two inserts which can trigger two inserts.
All in all you end up with 32,000 _possible_ INSERTS. No wonder it blows.
In your example teh fix is simple:
Unify teh two INSERT statements by unifying the two triggers.
I.e. INSERT INTO item VALUES (<row>), (<row>);
This way there is no fan out and DB2 expands only for 16 inserts.
If your real trigger is a lot bigger you may still need to increase the
statement heap a bit, but it will be managable.

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