A dream of more functional CLP in v8.3

[sopranos2]

Dear IBM DB2 support,

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]

so*******@gmail .com wrote:

Dear IBM DB2 support,

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

DB2 V8.3? There will be no such thing.

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]

"Serge Rielau" <sr*****@ca.ibm .com> wrote:
news:4d******** *****@individua l.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]

Konstantin Andreev wrote:

"Serge Rielau" <sr*****@ca.ibm .com> wrote:
news:4d******** *****@individua l.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?

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) > (?, ?)
* 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....
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Rhino]

"Serge Rielau" <sr*****@ca.ibm .com> wrote in message
news:4d******** *****@individua l.net...

Konstantin Andreev wrote:

"Serge Rielau" <sr*****@ca.ibm .com> wrote:
news:4d******** *****@individua l.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?

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) > (?, ?)
* 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....

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]

Rhino wrote:

"Serge Rielau" <sr*****@ca.ibm .com> wrote in message
news:4d******** *****@individua l.net...

Konstantin Andreev wrote:

"Serge Rielau" <sr*****@ca.ibm .com> wrote:
news:4d******** *****@individua l.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?

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) > (?, ?)
* 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....

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.

Done! Download the Developer Workbench (beta) of the Viper site. It will
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.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Mehmet Baserdem]

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.

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]

Mehmet Baserdem wrote:

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) I think collation deserves to be separated out. This is beyond LIKE.
-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?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Mehmet Baserdem]

Serge,

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]

Serge,

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_r eturns_a_table( )

Regards,

Mehmet Baserdem