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

Thread View: ibm.software.db2.mvs

4 messages

[T] Back to Threads [V] Tree View [G] Group Articles

4 total messages Started by Andreas Lederer Fri, 11 Oct 2002 12:19

Author: Andreas Lederer
Date: Fri, 11 Oct 2002 12:19

32 lines
2437 bytes

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

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

Author: PM (pm3iinc-nospam)
Date: Fri, 11 Oct 2002 16:14

7 lines
1468 bytes

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

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

Author: Andreas Lederer
Date: Mon, 14 Oct 2002 10:01

35 lines
2737 bytes

PM (pm3iinc-nospam) schrieb:
> 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.
> 

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

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

Author: Andreas Lederer
Date: Mon, 14 Oct 2002 10:23

46 lines
3049 bytes

Andreas Lederer schrieb:
> PM (pm3iinc-nospam) schrieb:
> 
>> 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.
>>
> 
> 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
> 
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

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads