How to do...well...anything...in DB2 SQL

[Serge Rielau]

Re: How to do...well...any thing...in DB2 SQL

Ian,
try c-style comments. They should work across the board.
/*... */
Prereq is FP9

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Gert van der Kooij]

Re: How to do...well...any thing...in DB2 SQL

>[color=blue]
> 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.[/color]


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.

[Serge Rielau]

Re: How to do...well...any thing...in DB2 SQL

Ian Boyd wrote:[color=blue]
> How do you give names to the fields returned from a SELECT of a VALUES
> table?
>
>[/color]
SELECT * FROM (VALUES (1, 2, 3)) AS T(c1, c2, c3)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

Re: How to do...well...any thing...in DB2 SQL

Ian Boyd wrote:[color=blue]
> VALUES is a cool construct. i don't know if it's in SQL92/99/etc, but
> SQL Server should have something like it - even if i can't think of good
> uses for it right now :)[/color]
Using VALUES you can PIVOT tables and you can also simplify that
UPDATE trigger of yours into a single INSERT statement.

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

>> VALUES is a cool construct. i don't know if it's in SQL92/99/etc, but[color=blue][color=green]
>> SQL Server should have something like it - even if i can't think of good
>> uses for it right now :)[/color]
> Using VALUES you can PIVOT tables and you can also simplify that
> UPDATE trigger of yours into a single INSERT statement.[/color]

Whoa, whoa, slow down.

Let me figure out how to do SELECTs first :)

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> It's called PL/SQL.
[color=blue][color=green]
>>i hope they aren't case-sensitive[/color]
>
> For object names, databases are case-sensitive.[/color]

*shudder* Let's hope i never have to use Oracle.
[color=blue]
> However, if not
> enclosed in double-quotes, it is implicitly uppercased before being
> evaluated. Therefore, it appears as case-insensitive.
> SQL Server breaks this, however, and is rather annoying.[/color]

Breaks what? SQL Server isn't case sensitive.
[color=blue][color=green]
>>i hope they allow identifiers to be longer than 18 characters.[/color]
>
> IIRC, it's 30.[/color]

i tried to do a quick check: At *least* 2000 identifiers we would have
to shorten if we're limited to 30 characters. Some database names,
some table names, index, keys, foreign-keys, etc

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> >Right now it's WoW.[color=blue]
>
> 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.[/color]

Elune (PvE).

We were on Ner'zhul (PvP), but jumped ship when the honor system came out.

Our guild on Elune is <Honor System Refugees>

Not too many people get it, but i think it's clever.

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> Ian,[color=blue]
> try c-style comments. They should work across the board.
> /*... */
> Prereq is FP9[/color]

Excellent! That works. i don't know what FP9 is, but what do i care, it
works!

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> The error is related to the semantic temporary table that needs to hold[color=blue]
> 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.[/color]

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?

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> Hi Ian,[color=blue]
>
> 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.[/color]

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.Descript ion AS varchar(1000)),
CAST(d.Descript ion AS varchar(1000))
);
END IF;

and it still fails.

[Brian Tkatch]

Re: How to do...well...any thing...in DB2 SQL

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.

[Dave Hughes]

Re: How to do...well...any thing...in DB2 SQL

Ian Boyd wrote:
[color=blue][color=green]
> > Ian,
> > try c-style comments. They should work across the board.
> > /*... */
> > Prereq is FP9[/color]
>
> Excellent! That works. i don't know what FP9 is, but what do i care,
> it works![/color]

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\IB M\SQLLIB".


HTH,

Dave.
--

[Gert van der Kooij]

Re: How to do...well...any thing...in DB2 SQL

In article <dusalu02ddm@en ews2.newsguy.co m>, Ian Boyd (ian.msnews010
@avatopia.com) says...[color=blue][color=green]
> > 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.[/color]
>
> 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.Descript ion AS varchar(1000)),
> CAST(d.Descript ion AS varchar(1000))
> );
> END IF;
>
> and it still fails.
>
>
>[/color]

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') "

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> Heh.[color=blue]
>
> 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. :)[/color]

Bliz's website had job opening for people experienced in VLDB's on Oracle.
Maybe they were switching to Oracle :)

They are soooo ready for Oracle.

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> you most[color=blue]
> 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') "[/color]

Error: SQL1582N The PAGESIZE of the table space "TMPSPACE_8 K" does not match
the PAGESIZE of the bufferpool "IBMDEFAULT BP" associated with the table
space. SQLSTATE=428CB
(State:428CB, Native Code: FFFFF9D2)

If we dumped the database and started over with 8k, would the system's temp
space also now be 8k? Is tempspace per database?