Db2 for I :How to Select rows while deleting?

Question

I've found this article that explains how to get the deleted record with the OLD TABLE keywords.

However, the instruction doesn't seem to work in Db2 for I-series (version 7.2)

Do you know any alternatives to get the same result? Thanks

Article is for db2 for Z/OS much different than db2 for Iseries. You say you are looking for an alternative to get the same result. Can you explain a little more what you are trying to do? — GenericDisplayName
SELECT * INTO :record FROM OLD TABLE (DELETE FROM EMP WHERE JOB = 'OPERATOR');. I want to get the record I'm about to delete, in order to save it in a log file — Bayron Daniel Mendoza Tovar
You may use so called positioned delete inserting a row you are going to delete into some global temporary table prior to each delete from mytab where current of mycursor. But you need a stored procedure for this... — Mark Barinstein

jmarkmurphy jmarkmurphy · Accepted Answer · 2019-08-02T14:42:04

As you have discovered, this syntax is not valid for DB2 for i. But, I can think of a couple ways to do what you want.

You can use two statements, one to retrieve records to be deleted into a temporary table, then one to perform the delete (just use the same where clause for both). Unfortunately, this has the opportunity, however small, that you will delete more than you read. If additional records that match your where clause are inserted between the time you select, and the time you delete, then your log will not be accurate.
You can use a delete trigger to insert records into the log as they are deleted from the table. This might be the best way, as it will always log deletes, no matter how the records are deleted. But it will always log deletes, no matter how the records are deleted, and if you only want those records logged within certain processes, then you will need to build dependencies between your trigger and those processes making both more complex.
You can use a stored procedure with a cursor and a positioned delete as mentioned by Mark Bairinstein in the comments above. This will allow you to delete records with logging, and also prevent the issue with the first option. But this leaves users the opportunity to delete records in a way that is not logged. May be good or bad depending on your requirements.