DB2 Error Code -495 while we try deleting records counts more than 400k

Question

We have following query to get the records deleted on fk_data_table, and been found we weren’t able to, as because of 400k records.

Delete FROM BOM_LINK WHERE TEST_OBJECT_OID IN (SELECT DISTINCT TESTOBJECT_OID FROM TESTOBJECT WHERE TESTOBJECT.TESTOBJECTTYPE_OID = 3);

DB2 Error Code -495 https://www.sqlerror.de/db2_sql_error_-495_sqlstate_57051.html

I think what we can do is,

1. We can write a function or procedure
2. We can retrieve data by writing query by SELECT and CURSOR options, instead directly deleting
3. While iterating CURSOR we can try deleting it. (I am not sure that in this way we can delete the row, anyway lets find this out)
4. We can do periodical commit, instead of doing commit after extensive amount of deletion.

Could someone help us sorting out this issue which we are facing. Pointing to some SQL code snippets will help us a lot.

Answer 1

Unfortunately, Db2 for Z/OS doesn't allow to delete from a subselect.
I don't have Db2 for Z/OS at hand to check, but you may try the following:

CREATE VIEW BOM_LINK_V AS
SELECT *
FROM BOM_LINK B
WHERE EXISTS
(
  SELECT 1
  FROM TESTOBJECT T
  WHERE T.TESTOBJECT_OID = B.TEST_OBJECT_OID
  AND T.TESTOBJECTTYPE_OID = 3
)
FETCH FIRST 1000 ROWS ONLY;

Run DELETE FROM BOM_LINK_V until you get SQLSTATE = '02000' (no rows affected).

Update:
The DELETE statement since v12 supports the FETCH FIRST clause.
So, you may run the following without creating a view until you get SQLSTATE = '02000':

DELETE FROM BOM_LINK B
WHERE EXISTS
(
  SELECT 1
  FROM TESTOBJECT T
  WHERE T.TESTOBJECT_OID = B.TEST_OBJECT_OID
  AND T.TESTOBJECTTYPE_OID = 3
)
FETCH FIRST 1000 ROWS ONLY;