How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Brian Tkatch on 10 Mar 2006 10:59 >Right now it's WoW. Which server? I finally just got enough gold to get my epic mount. I just need a guildy to buy it for me, so i can save the extra hundred gold. B.
From: Serge Rielau on 10 Mar 2006 11:06 Ian Boyd wrote: > 8. Triggers for update > > My first task with DB2 was to create a table and 3 triggers. i've created > the table - mostly the way i wanted, and i've created two of the three > required triggers: > > AFTER INSERT > AFTER DELETE > > Now i just have to finish my last one, the hardest one, the one with the > most problems coming together. i'm also try to take the advice of people and > use the FOR EACH ROW rather than the simpler more obvious FOR EACH > STATEMENT. > > So, now for my update trigger, and the error that DB2 is giving me. NOTE: i > am intentionally not snipping apparently repetative code, because the error > talks about "too much." Also note, only formatted the first few entries and > the last entry, as OE does not honor tab characters when pasting. The error is related to the semantic temporary table that needs to hold the row. I presume you have created the database using a default 4K page size and not added any other table spaces. Add an 8K SYSTEM TEMPORARY tablespace (and an 8k Buffer pool to serve ) using the CREATE BUFFERPOOL and CREATE TABLESPACE SQL statements. This should solve the problem. If you don't want to manage different tablespace (as I suspect :-) recreate your database with a bigger default page size (assuming you're on FP9 or higher) from the get go. You can go up to 32K if you wish. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
From: Serge Rielau on 10 Mar 2006 11:09 Ian, try c-style comments. They should work across the board. /*... */ Prereq is FP9 Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
From: Gert van der Kooij on 10 Mar 2006 11:12 > > Now the error message i get is: > <error> > Error: 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=524. > SQLSTATE=54040 > (State:54040, Native Code: FFFFFA70) > </error> > > The web-sites explains this error: > 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. Hi Ian, The reason code would mean that the generated rowlength is to long. Just a guess but maybe it helps if you define a temporary tablespace with 36K pages.
From: Serge Rielau on 10 Mar 2006 11:15
Ian Boyd wrote: > How do you give names to the fields returned from a SELECT of a VALUES > table? > > SELECT * FROM (VALUES (1, 2, 3)) AS T(c1, c2, c3) Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab |