A dream of more functional CLP in v8.3
sopr...@gmail.com
I wish CLP would have been more functional. Without living the command
line I would like to get sql return code explanations, scalar function
definitions etc..
Looking forward to see CLP enhancements in v8.3
-mike
Serge Rielau
Have you tried:
? SQL0204
in CLP. ? gives SQL CODE and SQLSTATE explanations.
SELECT TEXT FROM SYSCAT.ROUTINES WHERE ROUTINENAME = 'FOO' and
ROUTINESCHEMA = 'SRIELAU'
Gives you function bodies (or do you mean something else?)
What is etc. ???
I'm collecting requirements for Viper+2, so go wild :-)
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Konstantin Andreev
news:4d8q4cF...@individual.net...
>
> I'm collecting requirements for Viper+2, so go wild :-)
>
Serge, I'm glad to here the such invitation. Could you make public the
currently collected wish list for Viper?
--
Konstantin Andreev.
Serge Rielau
But sure I can do a quick brain-dump of SQL-ish feature requests I'm
aware of (in random order) or ideas that I have on my own:
* row-comparisons (Bernard's favorite)
CREATE TABLE T1(c1 int, c2 int);
SELECT * FROM T1 WHERE (c1, c2) > (?, ?)
* mySQL style OFFSET clause:
SELECT * FROM T FETCH FIRST 5 ROWS OFFSET 10
* improve schema evolution
e.g. allow objects to be dropped and invalidate dependent objects for
auto revalidation. (No more need to tear down the object stack)
* implicit casting.
DB2 appears to be the only DBMS that implements SQL standard strict
typing...
anything from improving NULL, and parameter marker handling to
allowing comparisons between strings and numbers or concatenation of
strings and numbers: 'Hello' || 5 => 'Hello5'
* SQL Standard ARRAY type
* Some sort of "global" variable
* Some sort of "context" (like a login-script)
* Strengthening the concept of a schema
(or introducing SQL standard MODULES)
* "optimistic locking"
* Expose physical ROWID
* CS isolation without readers blocking writers (and vice versa)
* Snapshot Isolation
* SQL PL outside of procedures
(generalizing the BEGIN ATOMIC.. END)
* INTERVAL data type
* Informix style DATETIME
DATE(MONTH TO DAY)
or TIME(HOUR TO MILLISOCOND)
* DECFLOAT data type (exact numeric floating point)
* BOOLEAN data type
* [VAR]BINARY data type
* A way to put a session/server into auto-commit and then support
explicit start transaction (perhaps nested)
* autonomous transactions
* Full SQL PL support in SQL functions
* Full SQL PL support in Triggers
* user defined aggregates
* _CREATE_ GLOBAL TEMPORARY TABLES (like DB2 zOS)
* TRUNCATE TABLE statement
Well... one thing is for sure.. there is always work....
Rhino
"Serge Rielau" <sri...@ca.ibm.com> wrote in message
news:4d9de8F...@individual.net...
You're right about that Serge! It's hard to picture DB2 ever being
completely finished!
I can think of at least one thing that ought to be added to this list, if it
isn't already in Viper:
* some way to debug Java stored procedures and UDFs in the debuggers of
popular IDEs like Eclipse. I have never been able to the get IBM Integrated
Debugger to work and have had to resort to writing lines to files to debug
my code. That is not nearly as nice as using a modern debugger where I can
step into statements and check variable values on the fly.
--
Rhino
Serge Rielau
support DB2 V8.2 as well as DB2 Viper. Presently the beast weighs in a
tad heavy (with its own Eclipse and JRE), but I'm in good spirits you
will be able to plug it into your existing Eclipse IDE before long.
Mehmet Baserdem
I would love to have more Perl-ish "Regular Expressions" seasoning on
"LIKE" predicate:
-ignoring Upper / Lower cases ( Although I am not sure it is currently
doable with other means)
-using metacharacters (i.e. "\d" for digits)
-specifying range of values [a-x]
-using alternative phrases/groups in parentheses in the same pattern
text
-you name it.
Although it is possible to code Stored Procedures in VS.NET-2005 with
Viper, my ultimate dream: coding stored procedures with perl scritps.
Regards,
Mehmet
Serge Rielau
> Serge,
>
> I would love to have more Perl-ish "Regular Expressions" seasoning on
> "LIKE" predicate:
>
> -ignoring Upper / Lower cases ( Although I am not sure it is currently
> doable with other means)
> -using metacharacters (i.e. "\d" for digits)
> -specifying range of values [a-x]
> -using alternative phrases/groups in parentheses in the same pattern
> text
> -you name it.
I wonder whether there is some open source code that can be recycled
into a UDF. I mean reg-exp matching is not new...
>
> Although it is possible to code Stored Procedures in VS.NET-2005 with
> Viper, my ultimate dream: coding stored procedures with perl scritps.
OK that's a new one. Need to be careful though.. next comes PHP, REXX...
Do you need PERL for PERL's sake or is SQL PL missing something?
Mehmet Baserdem
I don't have a concrete comparison in terms of functionalty but it
would be nice to be able to use data structures like Hash and built-in
text processing functions.
Regards,
Mehmet Baserdem
Mehmet Baserdem
Although it may seem not a good practice but when referring to columns
in the stmts, allowing for the use of column numbers instead of their
names might give an extra convenience to the developers who are dealing
with text files.
select columns [1] from some_function_returns_a_table()
Regards,
Mehmet Baserdem
Dave Hughes
> Konstantin Andreev wrote:
> >"Serge Rielau" <sri...@ca.ibm.com> wrote:
> > news:4d8q4cF...@individual.net...
> > > I'm collecting requirements for Viper+2, so go wild :-)
Woohoo!
> > Serge, I'm glad to here the such invitation. Could you make public
> > the currently collected wish list for Viper?
> Well Viper is done and over...
>
> But sure I can do a quick brain-dump of SQL-ish feature requests I'm
> aware of (in random order) or ideas that I have on my own:
> * row-comparisons (Bernard's favorite) CREATE TABLE T1(c1 int, c2
> int); SELECT * FROM T1 WHERE (c1, c2) > (?, ?)
Ooo, nice :-) I can see several uses for that immediately...
> * mySQL style OFFSET clause:
> SELECT * FROM T FETCH FIRST 5 ROWS OFFSET 10
I vaguely recall reading somewhere that the SQL standard now included a
"standard" syntax for this functionality. Something along the lines of
SELECT whatever
FROM wherever
[ORDER BY somefield]
ROWS startrow [TO endrow]
In other words, your example would be:
SELECT * FROM T ROWS 10 TO 15
Any plans to implement this syntax? (seems a bit clearer to me than the
FETCH FIRST + OFFSET combination)
> * improve schema evolution
> e.g. allow objects to be dropped and invalidate dependent objects
> for auto revalidation. (No more need to tear down the object stack)
> * implicit casting.
> DB2 appears to be the only DBMS that implements SQL standard strict
> typing...
> anything from improving NULL, and parameter marker handling to
> allowing comparisons between strings and numbers or concatenation of
> strings and numbers: 'Hello' || 5 => 'Hello5'
> * SQL Standard ARRAY type
> * Some sort of "global" variable
> * Some sort of "context" (like a login-script)
> * Strengthening the concept of a schema
> (or introducing SQL standard MODULES)
> * "optimistic locking"
> * Expose physical ROWID
> * CS isolation without readers blocking writers (and vice versa)
> * Snapshot Isolation
> * SQL PL outside of procedures
> (generalizing the BEGIN ATOMIC.. END)
> * INTERVAL data type
Excellent! I'd *love* to see an interval data type (seems such a shame
to have labeled durations without a neat way to store such a duration
in the database)
> * Informix style DATETIME
> DATE(MONTH TO DAY)
> or TIME(HOUR TO MILLISOCOND)
> * DECFLOAT data type (exact numeric floating point)
> * BOOLEAN data type
One thought on this: presumably it'd involve adding TRUE and FALSE
constants to the SQL dialect? Would some provision be made to allow
configuration of alternate values that could represent TRUE and FALSE
values within the context of a BOOLEAN value (primarily for backward
compatibility purposes).
For example, it's common practice in some DB2 databases to use a
CHAR(1) field limited to 'Y' or 'N' (or maybe 'T' and 'F') for
booleans, whereas others use a SMALLINT with 0 and 1 (or possibly just
non-zero). I could imagine a French database might currently choose to
use CHAR(1) with 'V' and 'F'.
In order to allow a simple migration for legacy applications unaware of
the new BOOLEAN type, it might be an idea to allow for something like:
SET TRUE VALUES 'Y', 'T', 1;
SET FALSE VALUES 'N', 'F', 0;
Or maybe a DB configuration parameter instead (given that such design
decisions are sometimes implemented database-wide). This would allow
legacy applications to perform an operation like:
INSERT INTO sometable (boolcol) VALUES ('Y');
And have 'Y' automagically translated into TRUE.
On the other hand, this might introduce all sorts of horrible
ambiguities, maybe there's a better way, I'm not sure off the top of my
head... Just speculating wildly :-)
> * [VAR]BINARY data type
> * A way to put a session/server into auto-commit and then support
> explicit start transaction (perhaps nested)
> * autonomous transactions
> * Full SQL PL support in SQL functions
> * Full SQL PL support in Triggers
> * user defined aggregates
> * CREATE GLOBAL TEMPORARY TABLES (like DB2 zOS)
> * TRUNCATE TABLE statement
>
> Well... one thing is for sure.. there is always work....
> Cheers
> Serge
Some additions (no fantastic "new functionality" unlike many of the
suggestions above, mostly just ideas that'd make my life that little
bit easier):
* The ability to add comments to routine parameters
I note that SYSCAT.ROUTINEPARMS contains a REMARKS field, but there's
no way to populate it. I've been tinkering with a documentation
generator for DB2, and this would be _seriously_ useful for me (as it
stands I've had to layer some tables and views on top of the SYSCAT
views to enable such comments, and documenting a function without being
able to document its parameters is like documenting a table without
being able to document its fields). Maybe make the syntax similar to
adding comments to the fields of a table/view? Something like:
COMMENT ON MYFUN.MAKEDATE (
YEAR IS 'The year to encode in the date',
MONTH IS 'The month to encode in the date',
DAY IS 'The day to encode in the date'
);
There's a slight problem with this: what to do with parameters which
have no name (e.g. the functions in the SYSFUN schema). Not sure how
one would structure the syntax to deal with this. Anyway, that leads on
to...
* The ability to comment on system functions
Related to the prior suggestion, why is it an error to attach comments
to the functions in the SYSFUN schema? Seems kind of arbitrary to me
(entries for the SYSFUN functions appear in the SYSCAT.ROUTINES table,
but unlike user-defined functions one can't comment on them). Again,
I've had to layer some tables and views on top of the SYSCAT views to
enable such comments with the documentation system I've been tinkering
with.
One problem with this: although one could comment on SYSFUN functions,
it'd still be impossible to comment on the SYSIBM functions given that
they don't even appear in the system catalog (presumably because of the
"polymorphic" nature of their parameter datatypes?)
* FOREIGN KEY REFERENCES table(cols) ON UPDATE CASCADE|SET NULL
Fairly obvious, though I suspect the implementation of ON UPDATE
CASCADE could be nasty...
* ALTER TABLE sometable DROP COLUMN somecol
Yes, one can do the equivalent in the control center (which'll generate
the necessary SQL to export, drop, recreate and reload the table). But
this is one of the few operations where the the control center is
quicker and easier than the command line ... can't be having that ;-)
* RECREATE VIEW someview [view-definition]
Shamelessly stolen from Firebird. If the optional [view-definition] is
specified, creates the view if it does not exist, drops and recreates
the view if it already exists. If the optional [view-definition] is not
specified, and the view exists and is inoperative, recreates the view
using the already stored definition. Could probably do something
similar for SQL functions or procedures?
It's not hard to make an SQL script that'll generate the necessary DROP
VIEW / CREATE VIEW statements in the current DB2 version (something
like SELECT TEXT FROM SYSCAT.VIEWS WHERE VALID = 'X', pipe the output
to a file and run it), but "RECREATE VIEW myview" would just be easier.
* DROP SCHEMA someschema CASCADE
Nicked from PostgreSQL. If you've got a database which allows users to
create stuff in their own schema, and a user no longer requires access
to your database you might want to wipe their personal schema when you
dump their user ID. In my experience of such databases, it's pretty
rare for users to use their personal schema extensively so it's not
usually a huge hassle. However, there's always one who's loaded his
schema with a whole data warehouse or some such :-)
I've got an SQL script somewhere to generate all the necessary DROP
statements, but it'd be so much easier just to issue a "DROP SCHEMA
dave CASCADE" and blow away the whole schema in one easy step (more
satisfying too >:-)
Okay, I'm done ... hopefully there's some useful stuff there.
Dave.
--
4.s...@mail.ru
You can easily create a java UDFs, for example, for regular expressions
using third party package (as I have done) or embedded java 1.4
functionality.
It is really easy because all functionality has been already written
and you have to write only a few-line UDF to use this functionality.
Brian Tkatch
Very nice Serge.
Some comments.
>* mySQL style OFFSET clause:
> SELECT * FROM T FETCH FIRST 5 ROWS OFFSET 10
To me, this is just plain wrong. There is no such thing as a row number
until the page is output from the query, and the order can change from
execution to execution.
Allowing an offset after an ORDER BY, however, would make sense, and be
very helpful, assuming every single COLUMN in the output is listed in
the clause (or, such action is implicit in the ordering of the COLUMNs
in the SELECT clause itself, when using this feature). But, the actual
action is to ignore or skip the first few rows not to "offset" them.
Besides, offset is in itself limiting to a second set of skippage..
Something like:
SELECT * FROM T ORDER BY Col1, Col2, Col3 IGNORE ROWS (1, 3, 5) FETCH
FIRST 5 ROWS
SELECT * FROM T ORDER BY Col1, Col2, Col3 IGNORE FIRST 5 ROWS FETCH
FIRST 5 ROWS
SELECT * FROM T ORDER BY Col1, Col2, Col3 IGNORE EVERY OTHER 5 ROWS
FETCH FIRST 5 ROWS
Or, as used with FETCH FIRST:
SELECT * FROM T ORDER BY Col1, Col2, Col3 FETCH FIRST 5 ROWS IGNORING
ROWS (1, 3, 5)
SELECT * FROM T ORDER BY Col1, Col2, Col3 FETCH FIRST 5 ROWS IGNORING
FIRST 5 ROWS
SELECT * FROM T ORDER BY Col1, Col2, Col3 FETCH FIRST 5 ROWS IGNORING
EVERY OTHER 5 ROWS
Just some thoughts. Most of this can be done with a wrapper, however.
Or something like that.
Taking advice from some application maliciously designed to look like a
database is not a good idea.
>* implicit casting.
> DB2 appears to be the only DBMS that implements SQL standard strict
> typing...
> anything from improving NULL, and parameter marker handling to
Improving NULL to have it be autocasted is a wonderful thing. That is,
if i pass NULL to a PROCEDURE i shouldn't have to cast it. I've never
read the standard (nor do i care to) but NULL should be without a type,
being it itself does not exist. Hmm.. maybe i should ask the question.
What benefit is there to requiring NULL to be cast anyway? Is there a
Var_Type() FUNCTION somewhere that relies on this?
>allowing comparisons between strings and numbers or concatenation of
>strings and numbers: 'Hello' || 5 => 'Hello5'
I don't like this one either. An actual value has a type, and should be
forced to be cast to use in such an operation. CONCAT() requires
characters and the user should provide that. This would be especially
bad for overloading. When do we convert the type, and when do we
overload? (Uh, oh, i think i just found a reason for casting NULLs.
Hmm..)
>* SQL Standard ARRAY type
Very interesting. Would it be a TABLE of sorts?
>* Expose physical ROWID
Very nice. I see a ROWID as the implicit PK on a TABLE, that should
rarely be used. It's nice to have the functionality, just in case.
>* SQL PL outside of procedures
> (generalizing the BEGIN ATOMIC.. END)
Nice.
>* Some sort of "global" variable
Global where? In the CLP? That would be appreciated..
>* Some sort of "context" (like a login-script)
Yeah, nice. Can stick things there like default SCHEMA and the like.
Nice job Serge.
B.
Serge Rielau
> Serge Rielau wrote:
>> * mySQL style OFFSET clause:
>> SELECT * FROM T FETCH FIRST 5 ROWS OFFSET 10
>
> I vaguely recall reading somewhere that the SQL standard now included a
> "standard" syntax for this functionality. Something along the lines of
>
> SELECT whatever
> FROM wherever
> [ORDER BY somefield]
> ROWS startrow [TO endrow]
>
> In other words, your example would be:
>
> SELECT * FROM T ROWS 10 TO 15
>
> Any plans to implement this syntax? (seems a bit clearer to me than the
> FETCH FIRST + OFFSET combination)
standards syntax and yes, if there is something in the SQL standard
we'll adopt.
>> * INTERVAL data type
>
> Excellent! I'd *love* to see an interval data type (seems such a shame
> to have labeled durations without a neat way to store such a duration
> in the database)
Well, labeled durations would go the way of the Dodo...
>> * BOOLEAN data type
>
> One thought on this: presumably it'd involve adding TRUE and FALSE
> constants to the SQL dialect? Would some provision be made to allow
> configuration of alternate values that could represent TRUE and FALSE
> values within the context of a BOOLEAN value (primarily for backward
> compatibility purposes).
>
> For example, it's common practice in some DB2 databases to use a
> CHAR(1) field limited to 'Y' or 'N' (or maybe 'T' and 'F') for
> booleans, whereas others use a SMALLINT with 0 and 1 (or possibly just
> non-zero). I could imagine a French database might currently choose to
> use CHAR(1) with 'V' and 'F'.
>
> In order to allow a simple migration for legacy applications unaware of
> the new BOOLEAN type, it might be an idea to allow for something like:
>
> SET TRUE VALUES 'Y', 'T', 1;
> SET FALSE VALUES 'N', 'F', 0;
>
> Or maybe a DB configuration parameter instead (given that such design
> decisions are sometimes implemented database-wide). This would allow
> legacy applications to perform an operation like:
>
> INSERT INTO sometable (boolcol) VALUES ('Y');
>
> And have 'Y' automagically translated into TRUE.
>
> On the other hand, this might introduce all sorts of horrible
> ambiguities, maybe there's a better way, I'm not sure off the top of my
> head... Just speculating wildly :-)
Duly noted. But keep in mind that we are debating requirements, not
solutions here. Makes sense to allow for some flexibility though (just
like DB2 provides for date-formats).
> Some additions (no fantastic "new functionality" unlike many of the
> suggestions above, mostly just ideas that'd make my life that little
> bit easier):
>
> * The ability to add comments to routine parameters
>
> I note that SYSCAT.ROUTINEPARMS contains a REMARKS field, but there's
> no way to populate it. I've been tinkering with a documentation
> generator for DB2, and this would be _seriously_ useful for me (as it
> stands I've had to layer some tables and views on top of the SYSCAT
> views to enable such comments, and documenting a function without being
> able to document its parameters is like documenting a table without
> being able to document its fields). Maybe make the syntax similar to
> adding comments to the fields of a table/view? Something like:
>
> COMMENT ON MYFUN.MAKEDATE (
> YEAR IS 'The year to encode in the date',
> MONTH IS 'The month to encode in the date',
> DAY IS 'The day to encode in the date'
> );
>
> There's a slight problem with this: what to do with parameters which
> have no name (e.g. the functions in the SYSFUN schema). Not sure how
> one would structure the syntax to deal with this. Anyway, that leads on
> to...
>
Interesting I didn't know that. I know we never added SEQUENCES.
It's what I call spit and polish.
> * The ability to comment on system functions
>
> Related to the prior suggestion, why is it an error to attach comments
> to the functions in the SYSFUN schema? Seems kind of arbitrary to me
> (entries for the SYSFUN functions appear in the SYSCAT.ROUTINES table,
> but unlike user-defined functions one can't comment on them). Again,
> I've had to layer some tables and views on top of the SYSCAT views to
> enable such comments with the documentation system I've been tinkering
> with.
>
> One problem with this: although one could comment on SYSFUN functions,
> it'd still be impossible to comment on the SYSIBM functions given that
> they don't even appear in the system catalog (presumably because of the
> "polymorphic" nature of their parameter datatypes?)
Uhm..OK.. but wouldn't that apply to all system SQL objects then - other
than functions? I admit that request strikes me as esoteric.... (read
hard to get the development $$ back in my lifetime - and I'm young)
> * FOREIGN KEY REFERENCES table(cols) ON UPDATE CASCADE|SET NULL
> Fairly obvious, though I suspect the implementation of ON UPDATE
> CASCADE could be nasty...
Noted, other voices chiming in?
> * ALTER TABLE sometable DROP COLUMN somecol
>
> Yes, one can do the equivalent in the control center (which'll generate
> the necessary SQL to export, drop, recreate and reload the table). But
> this is one of the few operations where the the control center is
> quicker and easier than the command line ... can't be having that ;-)
Done! DB2 Viper. You an also alter the type as long as it's "safe" (bigger).
> * RECREATE VIEW someview [view-definition]
>
> Shamelessly stolen from Firebird.
I've no issues with language theft, as long as you don't type SELECT(R)
FROM(C) INTO(TM). ;-)
> If the optional [view-definition] is
> specified, creates the view if it does not exist, drops and recreates
> the view if it already exists. If the optional [view-definition] is not
> specified, and the view exists and is inoperative, recreates the view
> using the already stored definition. Could probably do something
> similar for SQL functions or procedures?
>
> It's not hard to make an SQL script that'll generate the necessary DROP
> VIEW / CREATE VIEW statements in the current DB2 version (something
> like SELECT TEXT FROM SYSCAT.VIEWS WHERE VALID = 'X', pipe the output
> to a file and run it), but "RECREATE VIEW myview" would just be easier.
I'm not clear on teh purpose of DROP AND CREATE...
Taking the discussion up one level (business problem) this appears to be
part of "schema-evolution". (e.g. modify a table without having to
destroy the stack)
>
> * DROP SCHEMA someschema CASCADE
>
> Nicked from PostgreSQL. If you've got a database which allows users to
> create stuff in their own schema, and a user no longer requires access
> to your database you might want to wipe their personal schema when you
> dump their user ID. In my experience of such databases, it's pretty
> rare for users to use their personal schema extensively so it's not
> usually a huge hassle. However, there's always one who's loaded his
> schema with a whole data warehouse or some such :-)
> I've got an SQL script somewhere to generate all the necessary DROP
> statements, but it'd be so much easier just to issue a "DROP SCHEMA
> dave CASCADE" and blow away the whole schema in one easy step (more
> satisfying too >:-)
There is a DROP_SCHEMA procedure in DB2 Viper 9as well as COPY schema.
And, btw. I posted one on developer works for V8.2
Serge Rielau
have to wave a lot of $$ in front of my bosses to arm twist ;-)
Serge Rielau
> Serge Rielau wrote:
>> * mySQL style OFFSET clause:
>> SELECT * FROM T FETCH FIRST 5 ROWS OFFSET 10
>
> To me, this is just plain wrong. There is no such thing as a row number
> until the page is output from the query, and the order can change from
> execution to execution.
Of course an ORDER BY is semantically essential :-)
Whether it should be allowed without would need to be discussed.
>> * implicit casting.
>> DB2 appears to be the only DBMS that implements SQL standard strict
>> typing...
>> anything from improving NULL, and parameter marker handling to
>
> Improving NULL to have it be autocasted is a wonderful thing. That is,
> if i pass NULL to a PROCEDURE i shouldn't have to cast it. I've never
> read the standard (nor do i care to) but NULL should be without a type,
> being it itself does not exist. Hmm.. maybe i should ask the question.
> What benefit is there to requiring NULL to be cast anyway? Is there a
> Var_Type() FUNCTION somewhere that relies on this?
help? E.g. untyped parameter markers are allowed as arguments to
procedures bt not functions (overloading). You can have them on one side
of comparison, etc.
>
>> allowing comparisons between strings and numbers or concatenation of
>> strings and numbers: 'Hello' || 5 => 'Hello5'
>
> I don't like this one either. An actual value has a type, and should be
> forced to be cast to use in such an operation. CONCAT() requires
> characters and the user should provide that. This would be especially
> bad for overloading. When do we convert the type, and when do we
> overload? (Uh, oh, i think i just found a reason for casting NULLs.
> Hmm..)
>> * SQL Standard ARRAY type
> Very interesting. Would it be a TABLE of sorts?
DECLARE x INTEGER ARRAY[5];
SET x[2] = 7;
SET x[3] = 3;
SELECT i, c1 FROM UNNEST(x) INCLUDE ORDINAL (sp?) AS (c1, i)
>> * Some sort of "global" variable
> Global where? In the CLP? That would be appreciated.
Perhaps defined in the schema, visible within the whole session....
>> * Some sort of "context" (like a login-script)
> Yeah, nice. Can stick things there like default SCHEMA and the like.
Indeed :-)
Dave Hughes
> Dave Hughes wrote:
> > Serge Rielau wrote:
> > > * mySQL style OFFSET clause:
> >> SELECT * FROM T FETCH FIRST 5 ROWS OFFSET 10
> >
> > I vaguely recall reading somewhere that the SQL standard now
> > included a "standard" syntax for this functionality. Something
> > along the lines of
> >
> > SELECT whatever
> > FROM wherever
> > [ORDER BY somefield]
> > ROWS startrow [TO endrow]
> >
> > In other words, your example would be:
> >
> > SELECT * FROM T ROWS 10 TO 15
> >
> > Any plans to implement this syntax? (seems a bit clearer to me than
> > the FETCH FIRST + OFFSET combination)
> That is news to me. Our standards folks have been sent out to provide
> standards syntax and yes, if there is something in the SQL standard
> we'll adopt.
I managed to track down the source of my assertion that this might be
standards based; found the following the Firebird v2 release notes:
"... A more understandable alternative to the FIRST/SKIP clauses, the
ROWS syntax accords with the latest SQL standard and brings some extra
benefits ..."
I'm not sure exactly what they mean when they say "accords with" or
"the latest SQL standard", and I don't have a copy of the SQL standards
to look at, but as you say, the standards folk'll run across it if it's
there.
> > > * INTERVAL data type
> >
> > Excellent! I'd love to see an interval data type (seems such a shame
> > to have labeled durations without a neat way to store such a
> > duration in the database)
> Well, labeled durations would go the way of the Dodo...
As long as I can still do something semantically (although not
necessarily syntactically) equivalent to CURRENT DATE - n DAYS, that's
fine with me :-)
> > * The ability to comment on system functions
> >
> > Related to the prior suggestion, why is it an error to attach
> > comments to the functions in the SYSFUN schema? Seems kind of
> > arbitrary to me (entries for the SYSFUN functions appear in the
> > SYSCAT.ROUTINES table, but unlike user-defined functions one can't
> > comment on them). Again, I've had to layer some tables and views on
> > top of the SYSCAT views to enable such comments with the
> > documentation system I've been tinkering with.
> >
> > One problem with this: although one could comment on SYSFUN
> > functions, it'd still be impossible to comment on the SYSIBM
> > functions given that they don't even appear in the system catalog
> > (presumably because of the "polymorphic" nature of their parameter
> > datatypes?)
>
> Uhm..OK.. but wouldn't that apply to all system SQL objects then -
> other than functions? I admit that request strikes me as esoteric....
> (read hard to get the development $$ back in my lifetime - and I'm
> young)
One can already comment on the SYSCAT and SYSIBM views and tables (and
the columns within them). As part of the aforementioned documentation
system, I grabbed a copy of the Info Center's docs on the SYSCAT views,
ran 'em through a few macros to get the maximum lengths down to 254
characters and remove a lot of the formatting, and wound up with a
large SQL script that adds comments to all the SYSCAT views and fields
(which can then appear alongside the comments attached to user-defined
objects in the final output). Nice :-)
> > * ALTER TABLE sometable DROP COLUMN somecol
> >
> > Yes, one can do the equivalent in the control center (which'll
> > generate the necessary SQL to export, drop, recreate and reload the
> > table). But this is one of the few operations where the the control
> > center is quicker and easier than the command line ... can't be
> > having that ;-)
> Done! DB2 Viper. You an also alter the type as long as it's "safe"
> (bigger).
Excellent! Sorry, I thought I'd checked the DB2 Viper info center on
each of these suggestions to make sure they weren't already there, but
apparently I missed that one.
> > * RECREATE VIEW someview [view-definition]
> >
> > Shamelessly stolen from Firebird.
> I've no issues with language theft, as long as you don't type
> SELECT(R) FROM(C) INTO(TM). ;-)
>
> > If the optional [view-definition] is
> > specified, creates the view if it does not exist, drops and
> > recreates the view if it already exists. If the optional
> > [view-definition] is not specified, and the view exists and is
> > inoperative, recreates the view using the already stored
> > definition. Could probably do something similar for SQL functions
> > or procedures?
> >
> > It's not hard to make an SQL script that'll generate the necessary
> > DROP VIEW / CREATE VIEW statements in the current DB2 version
> > (something like SELECT TEXT FROM SYSCAT.VIEWS WHERE VALID = 'X',
> > pipe the output to a file and run it), but "RECREATE VIEW myview"
> > would just be easier.
> I'm not clear on teh purpose of DROP AND CREATE...
> Taking the discussion up one level (business problem) this appears to
> be part of "schema-evolution". (e.g. modify a table without having to
> destroy the stack)
Ahh ... yes, the "improve schema evolution" item sounds even better
(for some bizarre reason I hadn't equated "view" with "stack")!
> > * DROP SCHEMA someschema CASCADE
> >
> > Nicked from PostgreSQL. If you've got a database which allows users
> > to create stuff in their own schema, and a user no longer requires
> > access to your database you might want to wipe their personal
> > schema when you dump their user ID. In my experience of such
> > databases, it's pretty rare for users to use their personal schema
> > extensively so it's not usually a huge hassle. However, there's
> > always one who's loaded his schema with a whole data warehouse or
> > some such :-) I've got an SQL script somewhere to generate all the
> > necessary DROP statements, but it'd be so much easier just to issue
> > a "DROP SCHEMA dave CASCADE" and blow away the whole schema in one
> > easy step (more satisfying too >:-)
> There is a DROP_SCHEMA procedure in DB2 Viper 9as well as COPY schema.
> And, btw. I posted one on developer works for V8.2
Nice! (Sorry, hadn't noticed that one either)
I think my script for generating the DROP statements might be derived
from the proc you posted on developer works (the reason for not keeping
it as a proc is I occassionally find it useful with databases I can't
create procedures in for one reason or another).
Thanks,
Dave.
--
Brian Tkatch
> Brian Tkatch wrote:
> > Serge Rielau wrote:
> >> * mySQL style OFFSET clause:
> >> SELECT * FROM T FETCH FIRST 5 ROWS OFFSET 10
> >
> > To me, this is just plain wrong. There is no such thing as a row number
> > until the page is output from the query, and the order can change from
> > execution to execution.
> <snip>
> Of course an ORDER BY is semantically essential :-)
> Whether it should be allowed without would need to be discussed.
*Phew*
[that's a good "phew"]
> >> * implicit casting.
> >> DB2 appears to be the only DBMS that implements SQL standard strict
> >> typing...
> >> anything from improving NULL, and parameter marker handling to
> >
> > Improving NULL to have it be autocasted is a wonderful thing. That is,
> > if i pass NULL to a PROCEDURE i shouldn't have to cast it. I've never
> > read the standard (nor do i care to) but NULL should be without a type,
> > being it itself does not exist. Hmm.. maybe i should ask the question.
> > What benefit is there to requiring NULL to be cast anyway? Is there a
> > Var_Type() FUNCTION somewhere that relies on this?
> Counter question: If NULL would follow the same rules as ? would that
> help? E.g. untyped parameter markers are allowed as arguments to
> procedures bt not functions (overloading). You can have them on one side
> of comparison, etc.
That sounds very interesting, assuming overloading is the only time we
need to know NULLs datatype.
Well, perhaps, it could even be allowed in FUNCTIONs, just not when it
is overloaded, the SPECIFIC name is not used, and the db cannot
otherwise determine which version to actually call.
> >> allowing comparisons between strings and numbers or concatenation of
> >> strings and numbers: 'Hello' || 5 => 'Hello5'
> >
> > I don't like this one either. An actual value has a type, and should be
> > forced to be cast to use in such an operation. CONCAT() requires
> > characters and the user should provide that. This would be especially
> > bad for overloading. When do we convert the type, and when do we
> > overload? (Uh, oh, i think i just found a reason for casting NULLs.
> > Hmm..)
> Hehe... I am a believer in strong typing. But beliefs do not sell DB2...
I am also a believer of strong typing. But who cares about selling, i
just figure i can't complain how poor a product is (read: doesn't fit
my personal wishes) if i don't make an effort to comment. :)
> >> * SQL Standard ARRAY type
> > Very interesting. Would it be a TABLE of sorts?
> No, It's defined as a datatype, but you can UNNEST the content into a table.
> DECLARE x INTEGER ARRAY[5];
> SET x[2] = 7;
> SET x[3] = 3;
> SELECT i, c1 FROM UNNEST(x) INCLUDE ORDINAL (sp?) AS (c1, i)
Wow, that's pretty neat.
Except, wouldn't it be VALUES(UNNEST(x))? Unless the array is of type
TABLENAME (which does not seem to be a valid type), the value is just a
value, and would not be valid for the FROM clause. Which means, a
dynamic TABLE must be created, and that is nomrally done via VALUES()
or TABLE().
Does that sound correct?
> >> * Some sort of "global" variable
> > Global where? In the CLP? That would be appreciated.
> Perhaps defined in the schema, visible within the whole session....
IOW, a SESSION variable very similar to a GLOBAL TEMPORARY TABLE.
DECLARE GLOBAL TEMPORARY VARIABLE A [AS] INTEGER;
Which would word just as well in the CLP. And, be very helpful in
playing around with OUT variables from PROCEDUREs.
I don't see why it would need to be SCHEMA-based.
> >> * Some sort of "context" (like a login-script)
> > Yeah, nice. Can stick things there like default SCHEMA and the like.
> Indeed :-)
The main thing i admire about IBM, is their dedication to make the
customers happy.
B.
Udo Weigl
I vote for the ROWID - and please put it into the "Snapshot for Locks". It
would be a very nice feature for our Speedgain Lock-Monitor.
Regards,
Udo
--
Speedgain for DB2 - The DB2 Monitor
http://www.itgain.de/en/index.html
Serge Rielau
> Hi Serge,
>
> I vote for the ROWID - and please put it into the "Snapshot for Locks". It
> would be a very nice feature for our Speedgain Lock-Monitor.
Ian
> Hi Serge,
>
> I vote for the ROWID - and please put it into the "Snapshot for Locks". It
> would be a very nice feature for our Speedgain Lock-Monitor.
>
Isn't this information already available in db2pd output?
Udo
I don't know. But there's no API to use db2pd from another program. You
have to parse the (human readable) output . That's error-prone und
sensitive against changes in the output format. (Nevertheless we do it
in some cases)
Independent from the above, you can get the rowid (via api) from the
"event monitor for locks". But it's useless. You can't issue a "select
* from my_table where rowid = xxx".
Oracle has this feature since years... ;-)
rAinDeEr
Hi,
I want to confirm whether the following statement is valid in DB2 UDB
v8.2
Alter table tablename drop column column_name
In Viper , I am able to do it but if I remember I was getting an error
when i had tried this in v8.2.(But now i have uninstalled v 8.2)
Regards,
RaInDeEr
Gert van der Kooij
tariqr...@gmail.com says...
You can check it yourself in the online docs at
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp
Serge Rielau
drop/recreate)
rAinDeEr
I used to alter using the command centre, But through CLP I was not
able to drop column at one shot. I had to drop and recreate ( Was there
any other way ?). In viper am able to do it in CLP.
rAinDeEr
Serge Rielau
In Viper the ALTER TABLE statement has support. In V8.2 the GUI ran a
script and essentially replaced the table.
P. Adhia
> I'm collecting requirements for Viper+2, so go wild :-)
This is not a really a feature, but I really wish db2 could print out a
message in db2diag.log when db2 rolls back a transaction with -911 due
to lock timeout/deadlock. This will be consistent with DB2 Z/OS.
Thanks
P Adhia
Serge Rielau
You want this at the default diag level?
What's the opinion of others? One may see this as a regression....
Cheers
Serge
Mark A
> Sometimes it's the small requests that make the big differences.
> You want this at the default diag level?
> What's the opinion of others? One may see this as a regression....
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
I believe that we are now seeing lock escalation at level 3, so I would not
mind seeing locktimeout or deadlocks.
P. Adhia
> You want this at the default diag level?
How about DB2 controls it with a (yet another) registry variable which
is defaulted to not show the new message? Probably some won't agree,
but I believe DB2 *should* print a message whenever it alters normal
and expected course of a transaction.
How about generalizing what goes into db2diag.log? A registry variable
that accepts a list of message IDs wih a prefix of + or -, that tells
DB2 which messages should be turned on and off respectively? I could
certainly live without certain messages, say generated by load utility
which finishes normally, which I consider an expected behavior -- but I
am sure someone out there wants to see them.
Thanks
P Adhia
P. Adhia
> certainly live without certain messages, say generated by load utility
> which finishes normally,
I should have qualified it further: load that finishes normally and
doesn't put objects in any of the restricted states. I certainly want
to see a message when a DB2 object is placed in a restricted state.
That brings up two more things on my wish-list. Again I borrowed them
from Z/OS DB2.
1) list utility should continue to display utilities which have ended
abnormally until they are terminated explicitly. Z/OS distinguishes
between "stopped" utilities and the ones that have "ended".
2) ability to list all db2 objects which are in restricted states.
Maybe it's my Z/OS DB2 background, but personally I prefer the way Z/OS
DB2 allows to name utilities, list utilities with filtering by name and
owner. These features become useful as DB2 continues to scale to
support bigger workloads.
Thanks
P Adhia
Mark Yudkin
I've sworn so many times about the lack if information for -911 and -913.
"Serge Rielau" <sri...@ca.ibm.com> wrote in message
news:4ecpluF...@individual.net...
rAinDeEr
Hi,
Does DB2 Viper Support Incremental backup and recovery for LOB
RainDeEr
Serge Rielau
> Does DB2 Viper Support Incremental backup and recovery for LOB
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/
rAinDeEr
In Data Recovery and High Availability Guide and Reference by IBM
Page 28, it says that DB2® now supports incremental backup and
recovery (but not of long field or large object data). Currently Oracle
has Incremental Backup for LOB.
This is pointed out as one of the short comings of DB2 in an oracle
publication.
http://www.oracle.com/technology/deploy/availability/pdf/CWP_9IVSDB2_HA.PDF.
Regards,
RainDeEr
rAinDeEr
I didnt like what Oracle has published..It's a worthless paper..
It shows as if DB2 is behind MS SQL in matters of high availability..
Then how come more Businesses are turning to DB2 UDB ??
lol
Serge Rielau
> rAinDeEr wrote:
>> Hi,
>>
>> In Data Recovery and High Availability Guide and Reference by IBM
>> Page 28, it says that DB2® now supports incremental backup and
>> recovery (but not of long field or large object data). Currently Oracle
>> has Incremental Backup for LOB.
>>
>> This is pointed out as one of the short comings of DB2 in an oracle
>> publication.
>>
>> http://www.oracle.com/technology/deploy/availability/pdf/CWP_9IVSDB2_HA.PDF.
>>
>> Regards,
>> RainDeEr
>
> I didnt like what Oracle has published..It's a worthless paper..
I passed the request along.
Knut Stolze
>
> Hi,
>
> Does DB2 Viper Support Incremental backup and recovery for LOB
Incremental backup _does_ support LOBs today, already!! The only issue is
that a LONG tablespace is always completely backed up (in V8) if any of its
pages has changed.
If this is refined in Viper to only backup the modified pages, I do not
know, however.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany