How can I use COMMIT in stored procedure in DB2 LUW

Question

CREATE TABLE T1(c1 varchar(10));

CREATE OR REPLACE PROCEDURE FOO()
BEGIN
FOR C AS WITH TT (C1) AS (VALUES (1) , (2) , (3)) SELECT C1 FROM TT
DO
INSERT INTO T1 VALUES ('aaa');
COMMIT;
END FOR;
END
@

When I execute this stored procedure, I get this error:

db2 "call foo()"
SQL0501N The cursor specified in a FETCH statement or CLOSE statement is not open or a cursor variable in a cursor scalar function reference is not open.

How can I do commit for each insert?

Answer 1

COMMIT closes all open cursors that were declared without the HOLD option, including the cursor implicitly created by the FOR statement.

Here's the change you need to make:

FOR C AS cur1 CURSOR WITH HOLD FOR
WITH TT (C1) AS (VALUES (1) , (2) , (3)) SELECT C1 FROM TT
DO
  INSERT INTO T1 VALUES ('aaa');
  COMMIT;
END FOR;

Documentation reference.