DB2 – How to Perform UPDATE via Cursor in DBeaver

cursorsdb2dbeaver

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

Best Answer

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

enter image description here

Now you can alter the statement delimiter

Related Question