on analytics and superaggregation

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.

Oracle – Sun, what has changed ?

Did you go to http://www.sun.com recently? You will land on oracle.com !

But more will come.

The conference JavaOne will be in SanFrancisco with Oracle OpenWorld.

The Sun Developer Network and Bigadmin will be integrated in OTN.

Welcome Sun Developers Ensuring community continuity

MySql and java.sun.com have the Oracle logo.

That was fast! Well, Oracle is quite used to acquisition, only this week they bought AmberPoint and Convergin.

Still MySQL strategy will probably change, I cannot believe Oracle will encourage the users to save millions on Oracle License by migrating to MySQL

http://www.mysql.com/products/enterprise/unlimited.html

what is the type of NULL

I was a bit surprised to see a VARCHAR2(0) column in my schema


select column_name, data_type, data_length 
from user_tab_columns 
where table_name='V';
COLUMN_NAME     DATA_TYPE            DATA_LENGTH
--------------- -------------------- -----------
X               VARCHAR2                       0

What’s this datatype? It is the datatype of NULL !!!

SQL> create or replace view v as select null x from dual
View created.
SQL> desc v
VIEW v
 Name              Null?    Type        
 ----------------- -------- ------------
 X                          VARCHAR2(0)