Data Change Table Reference (DCTR)

Here's a 'weird' feature that can be put to good use. Let's delete a few rows from a table *AND* retrieve it within a single statement:

db2 => select * from t1 ;

V1
------------------------
2
3
4
5
6
7
8
100
10

9 record(s) selected.

db2 => select * from old table ( delete from t1 where v1 > 8);

V1
------------------------
100
10

2 record(s) selected.





You can see how the 'deleted' rows are being retrieved. The other two options are 'NEW TABLE' and 'FINAL TABLE', which work for INSERT/UPDATE. The advantage of using this? -> Less locks! Locks are scarce commodity in DB2. The less yo need it, the more scalable the DB can be.

I Wish ...

I wish one day, one RDBMS would implement something similar to the *combination* of Oracle's in-statement hints, and DB2's XML optimization profile. I call it 'best of both worlds' ;)

Reasons:

Oracle's hints make it easy to do testing if you have full source control, but a real pain or close to impossible to fix embedded SQL statements that you have no access to.

DB2's XML profile allows you to fix pretty much everything, but a real pain to use, especially when all you need is to whip out some simple test cases.

ps: Oracle's stored outline( which is, sadly, not supported in 11g anymore) has great potential, but the lack of proper/easy way to manipulate it makes it even more pain in the neck to use than DB2's XML profile. Why do these designers like to make something useful so painfully difficult to use, and waste so much time making almost useless things (eg, Enterprise Manager) so easy to use?

MOD and MOD

Take note when you use MOD in either of these environment, especially if you need the fractional part of the final arithmetic.


Here's the behaviour of the MOD function in Oracle:


SQL> select mod(10.1,3) from dual ;

MOD(10.1,3)
-----------
1.1

SQL>




And here is how DB2 does it:

db2 => select mod(10.1,3) from sysibm.sysdummy1;

1
-----------
1

1 record(s) selected.




I shall emphasize that *BOTH* implementations are 'correct'.

Philosophy

The next time you encounter a problem ( in anything), instead of asking "why doesn't it work the way I want it?" , you should ask "what don't I know to make it work?"

More often than not, technology/tool is not the problem; the problem is YOU! ;)

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.