DB2 Viper 2 beta - Compatibility features, Need your help

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/info.../v9r5/index.jsp
Enjoy
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto LabSerge Rielau wrote:

Quote:

Originally Posted by

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/info.../v9r5/index.jsp
>
Enjoy
Serge

With little more work and you'll be compatible with Oracle. <g>

Well done.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu (replace x with u to respond)|||DA Morgan wrote:

Quote:

Originally Posted by

Well done.

Not well enough. I hit cancel within 10 seconds of posting to this
group, apparently to late. If you want to flame please do so in c.d.ibm-db2.
Let's leave the SQL Server crowd in peace...

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