DB2 Viper 2 beta - compatibility features Feedback needed

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/infocenter/db2luw/v9r5/index.jsp

Enjoy
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[...]

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


/Lennart

[...]

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

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.

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

[...]

You've got mail ;-)

/Lennart

What about:

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

Bernard (Dhooghe)

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







     2. So what?

Bernard (Dhooghe)



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












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

Hihi, Kovi wants "self-check out" for joins.
Duly noted.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

 > We couldn't so we went for

You seem very enthusiastic about surrogate keys, have you concidered 
it's drawbacks?

/Lennart

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.

DB2 supports so called "security-plugins".
There is a multipart series explaining security in DB2 9:
 http://www.**--****.com/ 

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

Hi!

Drawbacks? We have been working like this for about 7 years now and they
have served us well.

Best regards,
        Kovi

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