SEO

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

Enjoy
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau wrote:
[...]
> CROSSJOIN

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


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

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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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_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.COURSE OFFERING_ID) =
(pco.ADMISSIONROUND_ID,pco.EDUCATIONORG_ID,pco.COU RSEOFFERING_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
>

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

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

On Jun 15, 7:36 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> 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_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/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)

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

1. Functional indexes could help (temporary workarond).

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.

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_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.COURSE OFFERING_ID) =
> (pco.ADMISSIONROUND_ID,pco.EDUCATIONORG_ID,pco.COU RSEOFFERING_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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-