How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Serge Rielau on 10 Mar 2006 14:49 Gert van der Kooij wrote: > In article <dusi8k01tl3(a)enews1.newsguy.com>, Ian Boyd (ian.msnews010 > @avatopia.com) says... >>> 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')" >> Error: SQL1582N The PAGESIZE of the table space "TMPSPACE_8K" does not match >> the PAGESIZE of the bufferpool "IBMDEFAULTBP" 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? >> >> > > Sorry about that, I forgot you also have to create a 8K bufferpool. > If you start all over again using 8K as default, the bufferpool would > also be 8K automatically. > And yes, tempspace is per db. Thread up to here is correct. The old and new columns are flowing in the same row. You are correct that only the referenced columns are relevant, but it's all the column referenced by any trigger, RI and check constraint and of course the UPDATE itself. AFAIK SQL Server uses actual size per row. As a result you could get runtime errors. DB2 is consciously more conservative. If the worst case won't fit DB2 will fail the trigger definition or at latest the compilation of the UPDATE statement. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
From: Ian Boyd on 10 Mar 2006 15:10 > To get the information from SQL do: > SELECT * FROM TABLE(ENV_GET_INST_INFO()) AS X Error: SQL0440N No authorized routine named "ENV_GET_INST_INFO" of type "FUNCTION" having compatible arguments was found It's not important.
From: Dave Hughes on 10 Mar 2006 15:34 Ian Boyd wrote: > > Run db2level from a command window > [snip lots of errors] > > > Ummm...i'll run it later :) Sorry -- I meant an *ordinary* command window (like, Start|Run|cmd) Cheers, Dave. --
From: Serge Rielau on 10 Mar 2006 16:03 Ian Boyd wrote: >> To get the information from SQL do: >> SELECT * FROM TABLE(ENV_GET_INST_INFO()) AS X > > Error: SQL0440N No authorized routine named "ENV_GET_INST_INFO" of type > "FUNCTION" having compatible arguments was found So much for you being on FP9 :-( -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
From: Jeroen van den Broek on 10 Mar 2006 18:13
"Ian Boyd" <ian.msnews010(a)avatopia.com> schreef in bericht news:dus9nq02cem(a)enews2.newsguy.com... >> It's called PL/SQL. > >>>i hope they aren't case-sensitive >> >> For object names, databases are case-sensitive. > > *shudder* Let's hope i never have to use Oracle. > >> 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. > > Breaks what? SQL Server isn't case sensitive. > >>>i hope they allow identifiers to be longer than 18 characters. >> >> IIRC, it's 30. > > 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 It's indeed limited to 30. According to the Oracle10g docs it's a limitation of the PL/SQL-compiler. Might have something to do with the fact that PL/SQL is based on ADA. -- Jeroen |