From: Ian Boyd on
> 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
> 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
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
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
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')"

First  |  Prev  |  Next  |  Last
Pages: 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE