Laurent Schneider

When I wrote my book, I did not expect having the advanced Oracle sql features available on other dabatase engine.

This week-end I downloaded for fun a try of db2, v9.7.
1) download db2 linux64bit trial on ibm.com
2) install enterprise edition (next-next-install principle)
3) create the instance
4) create the sample database (where database and instance means something different than in Oracle)

$ db2 create db test pagesize 32 k
$ db2 connect to test
$ db2 create user temporary tablespace temp
$ db2 connect reset

5) connect with clpplus, which looks similar to sqlplus, even cooler at first look
$ clpplus db2inst1/***@vaio:50001/TEST

I tried a few examples from my book. Left, full and cross join works, but not partitioned outer join.

Scalar, Inline and nested query works too.

Surprisely many function that I thought Oracle specific work too, like sysdate and to_char.

In aggegation, the KEEP FIRST/LAST does not work,
In Super aggregations, all GROUPING SETS, CUBE, ROLLUP do work.
No Pivot or Unpivot.

No dual, but a powerful TABLE function

SQL> select * from table(values(1),(2));       
          1
-----------
          1
          2

Analytics works, With range, rows, order by partition, all the fancy stuff.

I have not checked at XML, but it sounds to be differently implemented.
Some things will work however

SQL> select xmlquery('1 to 10') from dual;

1                   
--------------------
1 2 3 4 5 6 7 8 9 10

No CONNECT BY, but as in Oracle 11gR2, recursive query factoring does build the hierarchy

No Model, obviously.