LEVEL trick that fails

This simple trick works fine in Oracle, but fails in DB2 in Oracle compatible mode:


db2 => select rownum from dual connect by level <= 10;

ROWNUM
--------------------
SQL20451N Cycle detected in a hierarchical query. SQLSTATE=560CO



So, at least for now ( July 2012), Oracle folks would have lost a handy way to generate data in DB2 under compatible mode.

ROWNUM 'error'

Here's a trivial and almost pointless example of 'incompatible' implementation of Oracle's commonly used 'rownum':

Oracle:

SQL> SELECT rownum FROM T1 WHERE ROWNUM = 2;

< no rows returned >



DB2 v10:

db2 => select rownum from T1 where rownum = 2;

ROWNUM
--------------------
1

1 record(s) selected.



See the 'strange-ness'? :)

We have a match for rownum = 2 , but DB2 returns '1' as the resultset.



No, this is not a bug. This is a consequence of the 'workaround' of Oracle's 'rownum' in DB2. This, in practice, is inconsequential, because no Oracle programmer would write something like 'rownum = 2' ;) Ok, maybe some do. Those who do that deserve to get some bugs in their code anyway ;)

LOCK Horror

This post and the next few are intended to address the murky area of Oracle-compatibility in DB2 v10. The biggest change, in my opinion, is the CURRENTLY COMMITTED semantic, which mimics the way Oracle performs non-blocking read.

There are a few gotchas though. Here's the first one:

In Oracle, you can do this:

Session#1:

SQL> LOCK TABLE T1 IN EXCLUSIVE MODE;

Table(s) Locked.


Session#2:

SQL> SELECT * FROM T1;

C1
----------
1
2



Notice how Oracle reads 'around' the locks in this extreme case.



However, in DB2v10,


Session#1:

SQL> LOCK TABLE T1 IN EXCLUSIVE MODE;

Table(s) Locked.


Session#2:

SQL> SELECT * FROM T1;

< this session will wait without any output >



This is one area where DB2 can't read 'around' the locks. This is probably working as designed, but for Oracle folks who are told that it is easy to code in DB2 using their original skills, this can come as a nasty surprise, not least a hidden bug in hiding.