How to resolve slqerror -1424 too many references to transition variables and transition table columns or the row....

[Andreas Lederer]

hallo!

i have a simple table:
create table tec_synca1(a_key integer not null, a_varchar varchar(3000),
primary key(a_key));

i have created this table in a 4kB tablespace.

now i want to create a trigger with:
CREATE TRIGGER GTS03.TG_U_GTS03_TEC0003 AFTER UPDATE ON GTS03.TEC_SYNCA1
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL INSERT
INTO GTS03.TG_SYNCTESTGCT (tabname, command, retkey, commitet, fetched)
VALUES ('GTS03.TEC_SYNCA1',(case when (new.A_VARCHAR<>old.A_VARCHAR)
then ('hhhhh') else ('') end),0,0,0);

which leads to the following error:

ERROR: -1424: 54040 : [IBM][CLI Driver][DB2/NT] SQL1424N Zu viele
Verweise auf Uebergangsvariablen und Spalten in Uebergangstabellen oder
die Zeilen dieser Verweise sind zu lang. Ursachencode: "2".LINE
NUMBER=1. SQLSTATE=54040

Ursachencode: "2" means: sum of the lengths of the references exceeds
the maximum length of a row in a table.

the table tg_synctestgct is on a 32kB tablespace.

how can i resolve this problem?
i need the comparison of old and new value!

thanks
andreas lederer

[PM (pm3iinc-nospam)]

My gut feeling (bow in the dark)

DDL of GTS03.TG_SYNCTESTGCT can't fit the Command column vs varchar(3000).
or
try the tec_synca1 in a 32k TS.

PM

[Andreas Lederer]

PM (pm3iinc-nospam) schrieb:

The definition of the command column in GCT is long varchar and there
are no problems when i use a trigger like:

CREATE TRIGGER GTS03.TG_U_GTS03_TEC0003 AFTER UPDATE ON GTS03.TEC_SYNCA1
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL INSERT
INTO GTS03.TG_SYNCTESTGCT (tabname, command, retkey, commitet, fetched)

VALUES ('GTS03.TEC_SYNCA1',new.A_VARCHAR || new.A_VARCHAR,0,0,0);

but when i use

CREATE TRIGGER GTS03.TG_U_GTS03_TEC0003 AFTER UPDATE ON GTS03.TEC_SYNCA1
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL INSERT
INTO GTS03.TG_SYNCTESTGCT (tabname, command, retkey, commitet, fetched)

VALUES ('GTS03.TEC_SYNCA1',new.A_VARCHAR || old.A_VARCHAR,0,0,0);

i get the same error message as in my first posting.

tec_synca1 is only a testtable. i know that it will work if i move it to
a 8k tablespace but this can't be done with the tables i use in production.

the question is why db2 limits the size of transition variables to the
size of the tec_synca1 ts.

is there a solution to bypass this limitation??

thanks
andreas lederer

[Andreas Lederer]

Andreas Lederer schrieb:

i think i have found the solution for the problem: the table
TG_SYNCTESTGCT resides in a 32k tablespace but there was no 32k temp
tablespace. after generating a 32k temp tablespace i can create the
triggers above.

thanks
andreas lederer