Mother of all fixpacks: DB2 10.5 LUW FP4

About a year ago, I wrote that DB2 BLU features are not compatible with 'DB2_COMPATIBILITY_VECTOR=ORA', ie, it does not allow the same DB to be 'Oracle compatible' while operating in BLU mode. That was a let down.

Curse no more. FP4 ( aka Cancun ) which was released a few weeks back has fixed that. This fixpack also adds char/varchar datatype to be 'data skip-able'. Now the whole BLU thing feels complete.

This fixpack also adds 'shadow table' to the mix, allowing the same data to be stored in 'row'  *and* 'column' format. That can be really useful in some scenarios. However, to set this up, you will also need CDC, and that makes the entire process rather painful ... hey, can DB2 BLU still claim to be 'easy'? I don't think so ;)

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.