Instead of Trigger results into SQL1424N

[William of Ockham]

Hi,

I was asked to recreate a new clean database for our developers because
the current one they use is not entirely up to date. So I created a new
database and I run into the followin strange problem. First some facts:

System: DB2 V8.1 Fixpack5 Redhat Linux 8.0 dual processor
Database A is the current database and all DDL I currently have executes
fine. Database B is the new one and is created on the same instance as A.

The following DDL runs fine on DATABASE A but on B it results into
SQL1424N when the Instead of trigger is created. Is there anybody that
knows what to do to solve this?

Thanks

William

DDL and explaination of SQL1424N

SET CURRENT SCHEMA='TEST';

CREATE TABLE T_MEMO(
MEM_NR BIGINT NOT NULL,
MEM_DATE TIMESTAMP NOT NULL,
MEM_DATEINV BIGINT NOT NULL,
MEM_USER BIGINT NOT NULL,
MEM_SUBJECT VARCHAR(75) NOT NULL,
MEM_MEMO VARCHAR(4096),
MEM_ACTION SMALLINT NOT NULL,
MEM_ACTIONDATE TIMESTAMP,
MEM_ACTIONUSER BIGINT,
MEM_AT TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP
NOT NULL
)IN TAB16
;

CREATE TABLE T_MEMOINDEX(
MEMIDX_SRCNR BIGINT NOT NULL,
MEMIDX_REFNR BIGINT NOT NULL,
MEMIDX_NR BIGINT NOT NULL,
MEMIDX_STATE SMALLINT NOT NULL
)IN TAB04
;
CREATE VIEW V_MEMO_4000(
MEM_NR, MEM_MAINREFNR, MEM_REFNR, MEM_DATE, MEM_DATEINV,
MEM_STATE, MEM_USER, MEM_SUBJECT, MEM_MEMO, MEM_ACTION,
MEM_ACTIONDATE, MEM_ACTIONUSER, MEM_AT
)
AS
SELECT
T_MEMO.MEM_NR, T_MEMOINDEX.MEMIDX_REFNR, T_MEMOINDEX.MEMIDX_REFNR,
T_MEMO.MEM_DATE, T_MEMO.MEM_DATEINV,
T_MEMOINDEX.MEMIDX_STATE, T_MEMO.MEM_USER, T_MEMO.MEM_SUBJECT,
T_MEMO.MEM_MEMO, T_MEMO.MEM_ACTION,
T_MEMO.MEM_ACTIONDATE, T_MEMO.MEM_ACTIONUSER, T_MEMO.MEM_AT
FROM
T_MEMO,T_MEMOINDEX
WHERE
MEMIDX_SRCNR = 4000 AND
MEMIDX_NR = MEM_NR
;

-- INSTEAD OF INSERT
CREATE TRIGGER IOI_V_MEMO_4000
INSTEAD OF INSERT
ON V_MEMO_4000
REFERENCING NEW AS NEXT
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO T_MEMO
(
MEM_NR,
MEM_DATE, MEM_DATEINV, MEM_USER,
MEM_SUBJECT, MEM_MEMO, MEM_ACTION, MEM_ACTIONDATE,
MEM_ACTIONUSER, MEM_AT
)
VALUES
(
NEXT.MEM_NR, NEXT.MEM_DATE,
NEXT.MEM_DATEINV, NEXT.MEM_USER,
NEXT.MEM_SUBJECT, NEXT.MEM_MEMO, NEXT.MEM_ACTION,
NEXT.MEM_ACTIONDATE, NEXT.MEM_ACTIONUSER, NEXT.MEM_AT
);
INSERT INTO T_MEMOINDEX
(
MEMIDX_SRCNR, MEMIDX_REFNR,MEMIDX_NR, MEMIDX_STATE
)
VALUES
(
4000, NEXT.MEM_REFNR, NEXT.MEM_NR, NEXT.MEM_STATE
);
END;

Error Message I get:
SQL1424N Too many references to transition variables and transition table
columns or the row length for these references is too long. Reason
code="2". LINE NUMBER=1. SQLSTATE=54040

Explanation:

The trigger includes a REFERENCING clause that identifies one or
more transition tables and transition variables. The triggered
action of the trigger contains references to transition table
columns or transition variables with one of the following
conditions identified by the reason code:
1 references total more than the limit of the number of columns
in a table

2 sum of the lengths of the references exceeds the maximum
length of a row in a table.

User Response:

Reduce the number of references to transition variables and
transition table columns in the trigger action of the trigger so
that the length is reduced or the total number of such references
is less than the maximum number of columns in a table.

[William of Ockham]

Hi,

I found a bizar solution for the problem I described. After carefully
comparing the Databases A and B I noticed that Database A had a temporary
tablespace of the same page size as the one T_MEMO was located in and B
hadn't! So after saying a little prayer I created the temporary
tablespace for B and the Trigger just run fine. As the problem is solved
I'd still like to know why that temporary tablespace is needed.

William

William of Ockham wrote:

Hi,

I was asked to recreate a new clean database for our developers because
the current one they use is not entirely up to date. So I created a new
database and I run into the followin strange problem. First some facts:

System: DB2 V8.1 Fixpack5 Redhat Linux 8.0 dual processor
Database A is the current database and all DDL I currently have executes
fine. Database B is the new one and is created on the same instance as A.

The following DDL runs fine on DATABASE A but on B it results into
SQL1424N when the Instead of trigger is created. Is there anybody that
knows what to do to solve this?

Thanks

William

DDL and explaination of SQL1424N

SET CURRENT SCHEMA='TEST';

CREATE TABLE T_MEMO(
MEM_NR BIGINT NOT NULL,
MEM_DATE TIMESTAMP NOT NULL,
MEM_DATEINV BIGINT NOT NULL,
MEM_USER BIGINT NOT NULL,
MEM_SUBJECT VARCHAR(75) NOT NULL,
MEM_MEMO VARCHAR(4096),
MEM_ACTION SMALLINT NOT NULL,
MEM_ACTIONDATE TIMESTAMP,
MEM_ACTIONUSER BIGINT,
MEM_AT TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP
NOT NULL
)IN TAB16
;

CREATE TABLE T_MEMOINDEX(
MEMIDX_SRCNR BIGINT NOT NULL,
MEMIDX_REFNR BIGINT NOT NULL,
MEMIDX_NR BIGINT NOT NULL,
MEMIDX_STATE SMALLINT NOT NULL
)IN TAB04
;
CREATE VIEW V_MEMO_4000(
MEM_NR, MEM_MAINREFNR, MEM_REFNR, MEM_DATE, MEM_DATEINV,
MEM_STATE, MEM_USER, MEM_SUBJECT, MEM_MEMO, MEM_ACTION,
MEM_ACTIONDATE, MEM_ACTIONUSER, MEM_AT
)
AS
SELECT
T_MEMO.MEM_NR, T_MEMOINDEX.MEMIDX_REFNR, T_MEMOINDEX.MEMIDX_REFNR,
T_MEMO.MEM_DATE, T_MEMO.MEM_DATEINV,
T_MEMOINDEX.MEMIDX_STATE, T_MEMO.MEM_USER, T_MEMO.MEM_SUBJECT,
T_MEMO.MEM_MEMO, T_MEMO.MEM_ACTION,
T_MEMO.MEM_ACTIONDATE, T_MEMO.MEM_ACTIONUSER, T_MEMO.MEM_AT
FROM
T_MEMO,T_MEMOINDEX
WHERE
MEMIDX_SRCNR = 4000 AND
MEMIDX_NR = MEM_NR
;

-- INSTEAD OF INSERT
CREATE TRIGGER IOI_V_MEMO_4000
INSTEAD OF INSERT
ON V_MEMO_4000
REFERENCING NEW AS NEXT
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO T_MEMO
(
MEM_NR,
MEM_DATE, MEM_DATEINV, MEM_USER,
MEM_SUBJECT, MEM_MEMO, MEM_ACTION, MEM_ACTIONDATE,
MEM_ACTIONUSER, MEM_AT
)
VALUES
(
NEXT.MEM_NR, NEXT.MEM_DATE,
NEXT.MEM_DATEINV, NEXT.MEM_USER,
NEXT.MEM_SUBJECT, NEXT.MEM_MEMO, NEXT.MEM_ACTION,
NEXT.MEM_ACTIONDATE, NEXT.MEM_ACTIONUSER, NEXT.MEM_AT
);
INSERT INTO T_MEMOINDEX
(
MEMIDX_SRCNR, MEMIDX_REFNR,MEMIDX_NR, MEMIDX_STATE
)
VALUES
(
4000, NEXT.MEM_REFNR, NEXT.MEM_NR, NEXT.MEM_STATE
);
END;

Error Message I get:
SQL1424N Too many references to transition variables and transition
table columns or the row length for these references is too long.
Reason code="2". LINE NUMBER=1. SQLSTATE=54040

Explanation:

The trigger includes a REFERENCING clause that identifies one or
more transition tables and transition variables. The triggered
action of the trigger contains references to transition table
columns or transition variables with one of the following
conditions identified by the reason code:
1 references total more than the limit of the number of columns
in a table

2 sum of the lengths of the references exceeds the maximum
length of a row in a table.

User Response:

Reduce the number of references to transition variables and
transition table columns in the trigger action of the trigger so
that the length is reduced or the total number of such references
is less than the maximum number of columns in a table.

[Ian]

William of Ockham wrote:

Hi,

I found a bizar solution for the problem I described. After carefully
comparing the Databases A and B I noticed that Database A had a
temporary tablespace of the same page size as the one T_MEMO was located
in and B hadn't! So after saying a little prayer I created the temporary
tablespace for B and the Trigger just run fine. As the problem is solved
I'd still like to know why that temporary tablespace is needed.

Because any rows that get materialized in a temporary tablespace
will not fit on to a 4k page. Therefore, you needed a temporary
tablespace with a page size of at least 8kb.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[William of Ockham]

So SQL1424N indicates that the transition variables can't be stored
anywhere when the instead of trigger is fired. Right?
Ian wrote:

William of Ockham wrote:

Hi,

I found a bizar solution for the problem I described. After carefully
comparing the Databases A and B I noticed that Database A had a
temporary tablespace of the same page size as the one T_MEMO was
located in and B hadn't! So after saying a little prayer I created the
temporary tablespace for B and the Trigger just run fine. As the
problem is solved I'd still like to know why that temporary tablespace
is needed.

Because any rows that get materialized in a temporary tablespace
will not fit on to a 4k page. Therefore, you needed a temporary
tablespace with a page size of at least 8kb.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Ian]

William of Ockham wrote:

So SQL1424N indicates that the transition variables can't be stored
anywhere when the instead of trigger is fired. Right?

Not the transition variables, the transition table. The "transition
table" is a temporary table, which goes into a temporary tablespace
and has the same requirements for row/page size as standard tables.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Serge Rielau]

DB2 may need to materialiaze the NEW TABLE transition table.
The table gets materialized as a temp.
Given that you ghave a sizable VARCHAR column teh 4k pagesize won't suffize.
I'm curious though: Did you get the message on CREATE TRIGGER or on INSERT?
I wasn't aware that DB2 checks this at CREATE TRIGGER time.

Cheers
Serge