delete from tab1 a where cast(a.col as BIGINT) not in (select cast(s.col as bigint) from tab2 s).

Does anyone has any idea to optimize this delete. I want to delete the data from tab1 on the basis of column which doesnt have any equivalent row in tab2.

Thanks

You do have indexes on TAB2.COL and TAB1.COL? Other than that, there is not much to optimize.

The main issue here will be logging anyway, i.e. if there are many rows to be deleted, the before image of each row must be written to the log. So you may want to consider turning logging off with ALTER TABLE ... NOT LOGGED INITIALLY.

Using the CAST function on a predicate may reduce (or eliminate) the chances of DB2 using an index on that column.

make sure, that s.col is NOT NULL in tab2. If a NULL-value occures, the DELETE fails.

For performance reasons, omit the CAST() funcion ( at least in the WHERE-clause of the outer statement )