DB2 Viper 2 beta - compatibility features Feedback needed
Serge Rielau
My fellow team mates had some extra time on their hands so we decided to
spice up DB2 with a grab-bag of compatibility features.
We wouldn't mind help validating the semantics match....
So for those of you blessed or cursed with a competitive DBMS here is a
list of what we've added:
1. ** DUAL
Always works without prefixing a schema
2. ** ROWNUM
3. ** (+) outer join syntax
4. LEAST/GREATEST/NVL/DECODE
5. TO_DATE/TO_CHAR improvement
DB2 supports most common patterns except those requiring language
awareness
6. ** CONNECT BY
This is a function drop, performance drop will follow
7. A slew of syntactic sugar like:
Seq.NEXTVAL and seq.CURRVAL notation
UNIQUE instead of DISTINCT
MINUS instead of EXCEPT
Unnamed nested subqueries (aka inline views)
"SELECT * FROM (SELECT * FROM T)"
CROSSJOIN
8. BITAND/BITOR/.....
The features marked with ** require a registry setting:
db2set DB2_COMPATIBILITY_VECTOR=3F
should switch everything on.
There are other features those porting apps will value:
GLOBAL VARIABLES
A new ARRAY data type
A new RID() function can be used to map ROWID
Docs: https://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp
Enjoy
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Lennart
[...]
> CROSSJOIN
Any chance NATURAL JOIN will be implemented in a near future?
/Lennart
[...]
Serge Rielau
> Serge Rielau wrote:
> [...]
>> CROSSJOIN
> Any chance NATURAL JOIN will be implemented in a near future?
so I bailed ;-)
If we find that NATURAL JOIN becomes popular then it is quite possible.
Not exactly an engineering feat to line up columns by name... ;-)
So far I haven't seen it in the field much though.
Cheers
Lennart
> Lennart wrote:
>> Serge Rielau wrote:
>> [...]
>>> CROSSJOIN
>> Any chance NATURAL JOIN will be implemented in a near future?
> I toyed with it. But it took more than a day to prototype,
> so I bailed ;-)
> If we find that NATURAL JOIN becomes popular then it is quite possible.
> Not exactly an engineering feat to line up columns by name... ;-)
>
Just tell me where to vote :-). Seriously though it would save me (and I
guess others) a tremendous amount of typing. As a bonus the sql will
become less cluttered with the obvious. As an example I would prefer:
select ... from NYA.COURSE_OFFERING co NATURAL JOIN
NYA.PICKED_COURSE_OFFERING pco where ...
over:
select ... from NYA.COURSE_OFFERING co INNER JOIN
NYA.PICKED_COURSE_OFFERING pco ON
(co.ADMISSIONROUND_ID,co.EDUCATIONORG_ID,co.COURSEOFFERING_ID) =
(pco.ADMISSIONROUND_ID,pco.EDUCATIONORG_ID,pco.COURSEOFFERING_ID)
where ...
Anyhow, thanks for the info regarding the beta. If I get the time I'll
probably participate.
Serge Rielau
> Serge Rielau wrote:
>> Lennart wrote:
>>> Serge Rielau wrote:
>>> [...]
>>>> CROSSJOIN
>>> Any chance NATURAL JOIN will be implemented in a near future?
>> I toyed with it. But it took more than a day to prototype,
>> so I bailed ;-)
>> If we find that NATURAL JOIN becomes popular then it is quite
>> possible. Not exactly an engineering feat to line up columns by
>> name... ;-)
>>
>
> Just tell me where to vote :-).
are you attached to some company/ISV? Drop me an email with the info and
I'll log the request.
That has more pull than: "Lennart from usenet wants it" :-)
Lennart
[...]
> Lennard,
> are you attached to some company/ISV? Drop me an email with the info and
> I'll log the request.
> That has more pull than: "Lennart from usenet wants it" :-)
>
You've got mail ;-)
/Lennart
Bernard Dhooghe
What about:
"Row-comparisons (Bernard's favorite)
CREATE TABLE T1(c1 int, c2 int);
SELECT * FROM T1 WHERE (c1, c2) > (?, ?)
"
Bernard (Dhooghe)
Serge Rielau
> "Row-comparisons (Bernard's favorite)
> CREATE TABLE T1(c1 int, c2 int);
> SELECT * FROM T1 WHERE (c1, c2) > (?, ?)
> "
However, I dare say that this is off-topic.
None of DB2's major competitors supports this feature to the best of my
knowledge.
Cheers
Bernard Dhooghe
On Jun 19, 7:07 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> Bernard Dhooghe wrote:
> > "Row-comparisons (Bernard's favorite)
> > CREATE TABLE T1(c1 int, c2 int);
> > SELECT * FROM T1 WHERE (c1, c2) > (?, ?)
> > "
>
> Well, I have to admit you preach a consistent story.
> However, I dare say that this is off-topic.
> None of DB2's major competitors supports this feature to the best of my
> knowledge.
>
2. So what?
Bernard (Dhooghe)
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
2.
Gregor Kovač
In my company we had a project that would do joins like you do. It was on
about 30 tables. Each table has a natural key for its primary key and when
you got three levels dows the foreign key tree your joins were
humungus. :))
Imagine doing that on a 2000 tables database. :) We couldn't so we went for
one primary key (from a sequence generated column) and a unique key as a
natural key. So joins are now simpler and MUCH easier to read. :))
Best regards,
Kovi
Lennart wrote:
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Serge Rielau
Duly noted.
Lennart
> Hi!
>
> In my company we had a project that would do joins like you do. It was on
> about 30 tables. Each table has a natural key for its primary key and when
> you got three levels dows the foreign key tree your joins were
> humungus. :))
> Imagine doing that on a 2000 tables database. :)
> We couldn't so we went for
> one primary key (from a sequence generated column) and a unique key as a
> natural key. So joins are now simpler and MUCH easier to read. :))
>
You seem very enthusiastic about surrogate keys, have you concidered
it's drawbacks?
/Lennart
janman
Sergi,
Is it possible to have another authentication other than OS
auth...the reason being that auditors don't allow remote logins for
common accounts.
This stops DB2 from autheticating.
Serge Rielau
> Is it possible to have another authentication other than OS
> auth...the reason being that auditors don't allow remote logins for
> common accounts.
>
> This stops DB2 from autheticating.
>
There is a multipart series explaining security in DB2 9:
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0508wasserman/
Gregor Kovač
Hi!
Drawbacks? We have been working like this for about 7 years now and they
have served us well.
Best regards,
Kovi
nikolay...@googlemail.com
>
> - -
You can easily download DB2 LDAP plugin from IBM site. And this plugin
already work for DB2 8 and 9
nikolay...@googlemail.com
Well event monitor is good but
1) He did't show bind variables
2) Event analyzer have ugly interface
We have a lot of migrations and this can really help.
nikolay...@googlemail.com
Also is really good combite in this tool things what you done in sqlpl
profiler and tracer (debugger)
Troels Arvin
> You can easily download DB2 LDAP plugin from IBM site. And this plugin
> already work for DB2 8 and 9
I wonder why the LDAP (and Kerberos) plugins aren't being joined into
Viper 2 instead of being separately distributed. During installation, the
installer should offer to configure them, IMHO. In 2007, such features
can hardly be seen as exotic.
--
Regards,
Troels Arvin <tro...@arvin.dk>
http://troels.arvin.dk/
Troels Arvin
> I wonder why the LDAP (and Kerberos) plugins aren't being joined into
> Viper 2 instead of being separately distributed. During installation,
> the installer should offer to configure them, IMHO. In 2007, such
> features can hardly be seen as exotic.
Ah - my mistake. It seems that the LDAP security-plugins are actually
part of Viper 2?
It's a shame that even Viper 2's Kerberos plugins seem to link to an
ancient version of the MIT Kerberos 5 libraries.
hri...@gmail.com
How about
List function
instr function
and partitioning indexes just like tables ?
regards
Hrishy
Serge Rielau
> How about
> List function
OK, I have no clue what that function does. Can you explain and/or pass
a link?
> instr function
DB2 calls it LOCATE. I agree this is one more unnecessary mismatch worth
removing.
> and partitioning indexes just like tables ?
oftentimes the requirement for local indexes in rooted in fear of
lengthy attach and detach processing due to global index rebuild. DB2
does not suffer from that issue since global index maintenance is
performed asynchronously in the background.
Cheers
Serge Rielau
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0707rielau/
Bernard Dhooghe
> The article I threatened to write on the topic has been completedhttp://www.ibm.com/developerworks/db2/library/techarticle/dm-0707rielau/
>
> Enjoy
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
Please note on AIX for rownum enablement: db2set
DB2_COMPATIBILITY_VECTOR=2 (not db2set DB2_COMPATIBILITY_VECTOR=02)
Bernard Dhooghe
Bernard Dhooghe
>
> Enjoy
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
Rownum and product idiosyncrasies:
drop table x;
create table x (a char(10), b char(20));
insert into x values ('1','z');
insert into x values ('2','y');
insert into x values ('3','x');
insert into x values ('4','q');
insert into x values ('5','p');
select a,b from x order by b asc fetch first 1 rows only;
select a,b from x where rownum = 1 order by b asc;
select a,b from (select a,b from x order by b asc) where rownum = 1;
Run it on db2 command line.
Give it to sqlplus.
See the difference.
Remark: Oracle in the last query executes the subselect first and then
limits the resultset. On very big tables this tends to become very
slow, even when there's an index on b. If there is a solution on
Oracle, we would be happy to hear of it.
Bernard Dhooghe based on the input of Andy Heynderickx (Belgium)
Serge Rielau
> Please note on AIX for rownum enablement: db2set
> DB2_COMPATIBILITY_VECTOR=2 (not db2set DB2_COMPATIBILITY_VECTOR=02)
The bit for ROWNUM should(!) be x01. x02 enables DUAL.
The leading is optional. I just like hex characters paired to 8bit...
Cheers
Serge Rielau
> Give it to sqlplus.
I can't! Please post what you see.
Cheers
Bernard Dhooghe
Here it is:
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 31 18:17:14 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production
SQL>
Table dropped.
SQL>
Table created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL> select a,b from x order by b asc fetch first 1 rows only
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL>
A B
---------- --------------------
1 z
SQL>
A B
---------- --------------------
5 p
Bernard Dhooghe
Bernard Dhooghe
expressivity and is outside the base select (as limit in mySQL and
PostgreSQL.), rownum in this case not.
So unless Oracle has a now a clause that has the same capability, the
workaround with rownum and inner select is just a way to have correct
results but semantics are gone and also performance.
Bernard Dhooghe
Knut Stolze
> select a,b from (select a,b from x order by b asc) where rownum = 1;
Question out of curiosity: the query is effectively the same as
SELECT a,b FROM x WHERE rownum = 1
Or does Oracle actually imply some ordering for the outer select? According
to SQL, it doesn't have to do that, of course.
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Serge Rielau
> Bernard Dhooghe wrote:
>
>> select a,b from (select a,b from x order by b asc) where rownum = 1;
>
> Question out of curiosity: the query is effectively the same as
>
> SELECT a,b FROM x WHERE rownum = 1
> Or does Oracle actually imply some ordering for the outer select? According
> to SQL, it doesn't have to do that, of course.
Good discussions, Bernard, Knut,
Let me re-inforce the disclaimer that ROWNUM is definitely not of my
choosing or liking.
We are presently working with the SQL Standard on a clause
similar to FETCH FIRST or LIMIT/OFFSET which must come after ORDER BY.
Alas, things are as they are.
The way how we have defined it is that:
ROWNUM is just a synonym for ROW_NUMBER() OVER().
If there is an ORDER BY present in the same subquery (i.e. after!) we
will copy the order by clause "down" into the OVER() clause.
That certainly is going a bit against the relational grain, but it my
experience it matches what customers expect.
The Ruby crowd would call this the "principle of least surprise".
Now, as noted in the WHERE clause things get truly hairy.
Obviously the ROWNUM in SELECT and WHERE cannot be shared.
The rule DB2 chose "naturally" and which I'm told matches Oracle is that
any boolean factors that are independent of the ROWNUM are being
executed first. That is only if the WHERE clause survives these
predicates the rows start ticking...
W.r.t. performance. It is dangerous to make performance assumptions
across products and versions. E.g. DB2 Viper 2 greatly improves
optimizations around FETCH FIRST and there are some (albeit not yet
sufficient IMHO) rules which link ROW_NUMBER() to FETCH FIRST
Lastly, on inheriting order up, striktly speaking DB2 does not do that.
In reality once a result set is ordered, why would DB2 mess it up?
A dangerous assumptions, but who are we to force an Oracle app to be
re-written properly just because they want to migrate to DB2.
DB2 will remain as pure as possible, but I must admit that those people
who love shooting themselves in the foot, must be allowed to keep doing
so if they insist as long as I don't force it upon those who don't.
"Your freedom stops where it starts intruding on mine" as they say :-)
Just like in Germany you can drive 200km/h and in many US states you can
ride a motorcycle without a helmet.
"It's a free country. You're free to die any which way you want." ;-)
Cheers
Serge Rielau
Bit is hex: 10
Time to download the beta refresh
> The features marked with ** require a registry setting:
> db2set DB2_COMPATIBILITY_VECTOR=3F
> should switch everything on.
Either be patient or go on a treasure hunt...
Bernard Dhooghe
Concerning SQL Standards, would it be possible to have a better split
between set composition and set processing when a cursor is involved?
Here is part of what I already posted in this newsgroup:
"
Key-indexed files where good for at on-line browsing and scrolling
access and there is no reason a relational dbms could not handle the
feature.
In key-indexed files, the set can not be composed and it is based on
just one file/table, but the cursor can be set and scrolling can go
up
and down.
In a rdbms the set can be composed but cursor position is weak and
most of the time/always(?) at the beginning of the set. So row value
constructors are just a partial solution but would be of great help.
Remark: I would see it as follows: split roles in set composition and
cursor positioning (orthogonality):
select * from ... where ... order by c1, c2, c3,... position cursor
at (c1,c2,c3,...) <= > | >= | < | <=) ('...', ...)
but this is no SQL standard as far as I know.
"
meaning:
declare cursor for select * from ... where ... order by c1, c2,
c3,...
open cursor position cursor at c1,c2,c3,...) <= > | >= | < | <=)
('...', ...)
The query can involve one or more tables and can be as complex as
needed. Here the order by is (again) essential.
So yes to Fetch first or Limit/Offset ... but this is part of the
world, it's use is oriented towards stateless retrieval, the statefull
cursor model is still valid.
Remark: When scrolling cursors were introduced in SQL92, the model of
the fetch-forward only cursors was maintained for the cursor position
at open (beginning of the set only), it makes sense if going up and
down makes sense, to be able to start somewhere in the composed set.
Bernard Dhooghe
Serge Rielau
I know where you're coming from. After all we have been talking about it
now for nearly 10 years....
I don't think to get what you want required to go through new language.
A simple:
SELECT * FROM T WHERE (pk1, pk2) > (:lastpk1, :lastpk2)
ORDER BY pk1, pk2 FETCH FIRST 30 ROWS ONLY
Does the job. And it is in the standard today. (FFnR has been accepted)
Alas, it ain't helping me migrate those customers using ROWNUM. Neither
will it help migrating mySQL or Postgres customers
Bernard Dhooghe
I a stateless model, fetch first, limit ... is a valid approach.
But why not finish the job on the other model?
Bernard Dhooghe
Bernard Dhooghe
Analyzing your comment:
SELECT * FROM T WHERE (pk1, pk2) > (:lastpk1, :lastpk2) ...
does it mean DB2 UDB will have row-value constuctor support in the
where clause?
Bernard Dhooghe
Serge Rielau
> Serge
>
> Analyzing your comment:
>
> SELECT * FROM T WHERE (pk1, pk2) > (:lastpk1, :lastpk2) ...
>
> does it mean DB2 UDB will have row-value constuctor support in the
> where clause?
Dave Hughes
> 9. ** NUMBER support
> Bit is hex: 10
> Time to download the beta refresh
>> The features marked with ** require a registry setting: db2set
>> DB2_COMPATIBILITY_VECTOR=3F
>> should switch everything on.
> For those smarties who wonder about hex 20.. Either be patient or go on
> a treasure hunt...
>
> Cheers
> Serge
Sadly I don't have time to play with Viper 2 just at the moment, but I
could make a couple of wild guesses that others might want to check...
* A tweak to make VARCHAR2 a synonym for VARCHAR?
* A tweak to make '' a synonym for NULL? (the horror!)
There are a couple of other things I can think of where Oracle differs
(unique constraints that accept nullable columns, unique indexes that
accept multiple nulls), but given that making them compatible would
require more than syntactic sugar I doubt 0x20 tweaks those :-)
Cheers,
Dave.
Serge Rielau
> On Tue, 07 Aug 2007 08:20:29 -0400, Serge Rielau scribbled:
>
>> 9. ** NUMBER support
>> Bit is hex: 10
>> Time to download the beta refresh
>>> The features marked with ** require a registry setting: db2set
>>> DB2_COMPATIBILITY_VECTOR=3F
>>> should switch everything on.
>> For those smarties who wonder about hex 20.. Either be patient or go on
>> a treasure hunt...
VARCHAR2. I mean - who would do that??? Oh, never mind, ...
A measily "2" sure ain't worth a precious bit.
Mark A
news:5ihpmqF...@mid.individual.net...
> Now, now, that would be a cheap shot, like making VARCHAR a synonym for
> VARCHAR2. I mean - who would do that??? Oh, never mind, ...
> A measily "2" sure ain't worth a precious bit.
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
If you go down that road, you will have to make following idiotic changes:
columnA = '' (2 single ticks) is the same as columnA is null, and not the
same as columnA = ' ' (tick, blank, tick).
Serge Rielau
> "Serge Rielau" <sri...@ca.ibm.com> wrote in message
> news:5ihpmqF...@mid.individual.net...
>> Now, now, that would be a cheap shot, like making VARCHAR a synonym for
>> VARCHAR2. I mean - who would do that??? Oh, never mind, ...
>> A measily "2" sure ain't worth a precious bit.
> columnA = '' (2 single ticks) is the same as columnA is null, and not the
> same as columnA = ' ' (tick, blank, tick).
what behavior would you expect if, say an empty string is LOADed into a
CHAR column? Would that be a NULL or a blank?
Presumably there must also be some functions which treat NULL as empty
string.
E.g. what about TRANSLATE?
Cheers
Serge
Mark A
> Hmm, OK, let's - hypothetically - assume DB2 were to make this change,
> what behavior would you expect if, say an empty string is LOADed into a
> CHAR column? Would that be a NULL or a blank?
> Presumably there must also be some functions which treat NULL as empty
> string.
> E.g. what about TRANSLATE?
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
The problem with VARCHAR2 is that if you use '' (tick, tick) for a column
value in a SET statement or a WHERE clause, an SQL exception is thrown if
the column is not nullable. If '' meant null if the column where nullable,
or blank when the column is defined as not null, that might be another
story.
In Oracle, programmers are loath to use CHAR in any situation because ' '
(tick, blank, tick) does not test equal to ' ' (tick, blank, blank, tick).
Of course, as already mentioned, neither of these two would test the same as
'' (tick, tick) which is shorthand for null in Oracle, and will throw an
exception if used on a not null column).
At least the VARCHAR2 gets rid of the trailing blanks, so that string
comparisons will test equal when they are the same. DB2 doesn't care about
trailing blanks, and will test 'ABC' equal to 'ABC ' even in a CHAR column.
So for a LOAD into a CHAR column, it would be OK if an empty string meant
null, but only if the column is nullable. It should mean ' ' (blank) if the
column is not null (and not throw an exception).
Excuse my ignorance, but I am not sure what the reference to TRANSLATE
means.
Knut Stolze
> * A tweak to make '' a synonym for NULL? (the horror!)
But only if this would not be the default or can be turned off.
Serge Rielau
> Excuse my ignorance, but I am not sure what the reference to TRANSLATE
> means.
characters. In DB2 when the target set in '' (i.e. an empty string) that
means that the found characters are being squeezed out.
In general bot Oracle and DB2 claim that NULL in mans NULL out.
So I wonder whether this is one of those cases where Oracle treats a
NULL as an empty string, hence behaves the same as DB2 (aside from
having the arguments reversed I'm told...)
With these compatibility thingies the devil is in the detail.. :-(