DB2 Viper 2 beta - compatibility features Feedback needed

[Serge Rielau]

Hi folks,

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_COMPATIBILI TY_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/infoc...v9r5/index.jsp

Enjoy
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Lennart]

Serge Rielau wrote:
[...]

CROSSJOIN

Any chance NATURAL JOIN will be implemented in a near future?
/Lennart

[...]

[Serge Rielau]

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... ;-)

So far I haven't seen it in the field much though.
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Lennart]

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 :-). 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_OFFE RING co NATURAL JOIN
NYA.PICKED_COUR SE_OFFERING pco where ...

over:

select ... from NYA.COURSE_OFFE RING co INNER JOIN
NYA.PICKED_COUR SE_OFFERING pco ON
(co.ADMISSIONRO UND_ID,co.EDUCA TIONORG_ID,co.C OURSEOFFERING_I D) =
(pco.ADMISSIONR OUND_ID,pco.EDU CATIONORG_ID,pc o.COURSEOFFERIN G_ID)
where ...

Anyhow, thanks for the info regarding the beta. If I get the time I'll
probably participate.

So far I haven't seen it in the field much though.
Cheers
Serge

[Serge Rielau]

Lennart wrote:

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 :-).

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

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

[Lennart]

Serge Rielau wrote:
[...]

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]

On Jun 15, 7:36 pm, Serge Rielau <[e-mail] .comwrote:

Hi folks,

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_COMPATIBILI TY_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/infoc...v9r5/index.jsp

Enjoy
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

What about:

"Row-comparisons (Bernard's favorite)
CREATE TABLE T1(c1 int, c2 int);
SELECT * FROM T1 WHERE (c1, c2) (?, ?)
"

Bernard (Dhooghe)

[Serge Rielau]

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.

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

[Bernard Dhooghe]

1. Functional indexes could help (temporary workarond).

On Jun 19, 7:07 pm, Serge Rielau <[e-mail] .comwrote:

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 =?UTF-8?B?S292YcSN?=]

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. :))

Best regards,
Kovi

Lennart wrote:

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 :-). 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_OFFE RING co NATURAL JOIN
NYA.PICKED_COUR SE_OFFERING pco where ...

over:

select ... from NYA.COURSE_OFFE RING co INNER JOIN
NYA.PICKED_COUR SE_OFFERING pco ON
(co.ADMISSIONRO UND_ID,co.EDUCA TIONORG_ID,co.C OURSEOFFERING_I D) =
(pco.ADMISSIONR OUND_ID,pco.EDU CATIONORG_ID,pc o.COURSEOFFERIN G_ID)
where ...

Anyhow, thanks for the info regarding the beta. If I get the time I'll
probably participate.

>So far I haven't seen it in the field much though.
Cheers
Serge

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-