DB2 : everything about UDB universe

Everybody loves the new flavor DB2 introduced to the delete command ,

delete from (select * from table [where ] [fetch first n rows only])

because they can limit the number of rows to be deleted in one shot. It has been of immense help since I frequently delete millions of rows and it alleviates the problem of lock memory and log file usage. I just put this statement in a loop and run that for a specific number of times (or till it gets 0 rows returned, with a little complex code). Now the only setback we have is, we cannot use a select statement that joins tables, and would be staring at SQL0150N error. Now I just set out to solve this and using the mighty 'exists' clause, which has saved me during more than one bad situation, was actually able to mimic join and still limit delete to a set of rows.

The original query was
select t1.* from table1 t1 inner join table2 t2 on
T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2 AND T1.COL3 = T2.COL3;

The following will not work because joins are not allowed in the 'delete from (' clause
delete from (select t1.* from table1 t1 inner join table2 t2 on
T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2 AND T1.COL3 = T2.COL3 fetch first n rows only)
This would throw SQL0150N error.

So I changed the above to
delete from
(
SELECT * FROM table1 T1 where exists
(select 1 from TABLE2 T2 where T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2 AND T1.COL3 = T2.COL3)
);
Works wonderfully.