DSXchange

Could someone enlighten me how to delete rows from a table when selecting the rows to delete from the same table: my job consists of an ODBC source stage which selects the rows to be deleted from a table, the primary key of the rows is sent through a tansformer stage to a target ODBC stage where I want to delete them.

The problem I encounter is that the delete doesn't work since the rows to be deleted are still locked by the "source" stage. Does a solution exists for this?

Replacing the ODBC stages by a specific database plugin (DB2) where I can set the isolation level also does not work.

Regards,
Ogmios

This may seem a weird answer.
Use user-defined SQL in your source stage (that's right, your source stage) that deletes the rows.
You can use job parameters in the SQL. For example:
DELETE FROM tablename WHERE col1 < '#Criterion#';
(Don't forget that you still have to surround string literals in SQL with single quotes; depending on the DBMS you may also need to surround identifiers (table and column names) with double quotes.)
The job itself will then process 0 rows, which should be quick.