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.