How do I get DBeaver to do a DB2 UPDATE via Cursor

Question

The following query works when run from the console via db2 command. But fails when running via DBeaver.

BEGIN NOT ATOMIC
  FOR R AS C CURSOR WITH HOLD FOR
    SELECT ROW_NUMBER() OVER() AS INDEX, ID
    FROM A_TABLE a
    WHERE a.ID BETWEEN 1 AND 1000
    ORDER BY a.ID
  DO
    UPDATE A_TABLE SET A_COL = 1 WHERE ID = R.ID;--
    IF MOD(R.INDEX, 100) = 0 THEN
      COMMIT;--
    END IF;--
  END FOR;--
  COMMIT;--
END;

DBeaver gives the following result

SQL Error [42601]: An unexpected token "END-OF-STATEMENT" was found following "WHERE ID = R.ID". Expected tokens may include: "<psm_semicolon>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14

Answer 1

As mustaccio points out, you need to change your statement terminator. In DBeaver you can do that by:

1. Right-click on the data source and choose "Edit connection"
2. In the leftmost frame at the bottom, there is SQL Processing
3. Check the Datasource checkbox

Now you can alter the statement delimiter