Errror executing dynamic delete query with Prepare statement inside a DB2 Stored Procedure in IBM DB2 LUW

Question

I am trying to Delete records from a tables with huge volume of records.

Now the error is

'Cursor C1 identifies a prepared statement that is not a SELECT or VALUES'

1-Added COMMIT,Without using COMMIT the 'Transaction Logs' are getting Full. 2-Added WITH HOLD statement for Cursor to remain active.

Lines with '--NEW ADDED' shows the new added code to a already working delete without COMMIT and WITH HOLD

CREATE PROCEDURE
TABLE1.MYPROC1 ( IN A VARCHAR(100),IN B INTEGER,IN C INTEGER)
LANGUAGE SQL
BEGIN
DECLARE SQLCODE int;
DECLARE V_CREATE_QUERY VARCHAR(1024);
DECLARE V_DELETE_STATEMENT STATEMENT;
DECLARE C1 CURSOR WITH HOLD FOR V_DELETE_STATEMENT;--NEW ADDED

SET V_CREATE_QUERY='DELETE FROM (SELECT * FROM '||a||' WHERE KEY         
=='||RTRIM(CHAR(B)||' ' FETCH FIRST ' || RTRIM (CHAR(C)||' ROWS ONLY ) AS 
PURGE_TABLE';

PREPARE V_DELETE_STATEMENT FROM V_CREATE_QUERY;
OPEN C1;  --NEW ADDED
WHILE(SQLCODE <>1000)
 DO
 EXECUTE V_DELETE_STATEMENT;
 COMMIT;  --NEW ADDED
END WHILE;
CLOSE C1  --NEW ADDED
end

Please let me know how to delete using COMMIT and WITH HOLD

Possible duplicate of [DB2: Purge large number of records from table](https://stackoverflow.com/questions/16426289/db2-purge-large-number-of-records-from-table) — mao, Jun 06 '19 at 14:27
@mao https://stackoverflow.com/questions/16426289/db2-purge-large-number-of-records-from-table is different as this is a stored Procedure — KKS, Jun 06 '19 at 14:33
I think the error message is pretty clear; you cannot open a cursor for a `delete` statement. — mustaccio, Jun 06 '19 at 14:57
I get that for the error message,but let me know what are the other methods to correct this and make this delete happen — KKS, Jun 06 '19 at 15:07
@mao the accepted answer is for bulk delete but for a different scenario..please let me know if you can help with this — KKS, Jun 06 '19 at 15:09
Hi @PaulVernon,i wanted to delete based on a 'where' criteria,not truncate the full table — KKS, Jun 08 '19 at 16:32

Mark Barinstein · Accepted Answer · 2019-06-09T08:45:26.037

0

A number of errors.
You don't need any cursor for delete.
SQLCODE=100 (and not 1000) is returned, when no rows deleted. You must check it after the corresponding statement (delete in your case) immediately, and not after another statement (commit ?? in your example).

Should be something like this:

CREATE OR REPLACE PROCEDURE
TABLE1.MYPROC1 ( IN A VARCHAR(100),IN B INTEGER,IN C INTEGER)
LANGUAGE SQL
BEGIN
  DECLARE SQLCODE int;
  DECLARE V_CREATE_QUERY VARCHAR(1024);
  DECLARE V_DELETE_STATEMENT STATEMENT;

  SET V_CREATE_QUERY=
    'DELETE FROM (SELECT * FROM '||a
  ||' WHERE KEY='||RTRIM(CHAR(B)
  ||' FETCH FIRST ' || RTRIM (CHAR(C)||' ROWS ONLY ) AS PURGE_TABLE';

  PREPARE V_DELETE_STATEMENT FROM V_CREATE_QUERY;
  DEL_LOOP: LOOP
    EXECUTE V_DELETE_STATEMENT;
    IF (SQLCODE = 100) THEN LEAVE DEL_LOOP; END IF;
    COMMIT;
  END LOOP DEL_LOOP;
  COMMIT;
end

edited Jun 09 '19 at 08:45

answered Jun 06 '19 at 16:22

Mark Barinstein

11,456
2
8
16

Hello Mark,thank you so much for the solution.I have made and small correction to the above SP. I have added an 'END IF;' after the 'COMMIT;'. This solution works for all the tables up till 400M rows,but it fails for a table with +500M rows with error 'Transaction logs getting full' .And i believe that this should not be the case as we are already doing a COMMIT after every 10K . Any suggestions please? – KKS Jun 08 '19 at 16:38
@KKS I really forgot to close the `IF` statement, but it must be closed before the `COMMIT` statement, not after it. The code doesn’t commit properly otherwise. It must commit after every `DELETE`, but if you place `END IF;` after `COMMIT`, it commits only once after all deletes. Check the updated example. – Mark Barinstein Jun 09 '19 at 08:53

Errror executing dynamic delete query with Prepare statement inside a DB2 Stored Procedure in IBM DB2 LUW

1 Answers1