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.