Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Ian Boyd on 10 Mar 2006 11:45 > 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. DB2 is running as "out of the box" as possible. i'm leary of doing these odd things, since we will not be allowed to manage the customer's database. They can only be sent scripts. And seeing how often scripts that the Command Center generate to perform operations, and the number of times we've lost tables because of it, i'd be very hesitant to send the customer scripts that perform wholesale database modifications. Can you go into detail the problem is? If "old row" and "new row" are held each held in temporary tables, each on a 4k table space, what is exceeding that 4k?
From: Ian Boyd on 10 Mar 2006 11:50 > 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. i just asked Serge, but no harm in re-posting :) What is the nature of this "generated" row? What, for example, would the pseudo-create statement of this temporary table? Is one table holding the old and new values for every column? If so, it must only generate it based on the columns you ask for in teh trigger, otherwise any table that is 4k wide could never be used by an update trigger. But i also tried moving the auditing of the longest column to it's own trigger --Description varchar(2048) IF ... (d.Description <> i.Description) THEN INSERT INTO Audit_Log( RowID, ... OldValue, NewValue) VALUES ( i.Daily_Log_ID, ... CAST(d.Description AS varchar(1000)), CAST(d.Description AS varchar(1000)) ); END IF; and it still fails.
From: Brian Tkatch on 10 Mar 2006 12:07 Heh. I'm a wimp sticking to Stormrage. :) Though i got to level 4 on Earthen Ring when Stormrage was down at one point. Hmm.. i wionder what db server *they* use. :) B.
From: Dave Hughes on 10 Mar 2006 12:56 Ian Boyd wrote: > > Ian, > > try c-style comments. They should work across the board. > > /*... */ > > Prereq is FP9 > > Excellent! That works. i don't know what FP9 is, but what do i care, > it works! FP9 = FixPak 9 Run db2level from a command window and it should show you what fixpak level you've got installed (obviously you've got FP9 or above in this case, but in case you need to know for the future). Also works on pure client installations. For example, from my Windows box (which only has the client installed): DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08010" with level identifier "01010106". Informational tokens are "DB2 v8.1.0.36", "s021023", "", and FixPak "0". Product is installed at "C:\PROGRA~1\IBM\SQLLIB". HTH, Dave. --
From: Gert van der Kooij on 10 Mar 2006 13:29
In article <dusalu02ddm(a)enews2.newsguy.com>, Ian Boyd (ian.msnews010 @avatopia.com) says... > > 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. > > i just asked Serge, but no harm in re-posting :) > > What is the nature of this "generated" row? What, for example, would the > pseudo-create statement of this temporary table? Is one table holding > the old and new values for every column? If so, it must only generate it > based on the columns you ask for in teh trigger, otherwise any table > that is 4k wide could never be used by an update trigger. > > But i also tried moving the auditing of the longest column to it's own > trigger > > > --Description varchar(2048) > IF ... (d.Description <> i.Description) THEN > INSERT INTO Audit_Log( > RowID, > ... > OldValue, > NewValue) > VALUES ( > i.Daily_Log_ID, > ... > CAST(d.Description AS varchar(1000)), > CAST(d.Description AS varchar(1000)) > ); > END IF; > > and it still fails. > > > I guess DB2 is calculating the max total length using the max length of the referenced columns. This means both Desciption reference columns don't fit on a 4096 bytes page (every pages has contains some overhead). When looking at the error message it seems like DB2 generates a row with all referenced columns. This means a system temporary tablespace is needed to fit that row. If I counted it correctly you have 29 references to old or new records so as Serge suggested you most likely need a pagesize of 8K. To create a system temporary tablespace you can run the command "create system temporary tablespace TMPSPACE_8K pagesize 8K managed by system using ('TMPSPACE_8K')" |