0

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

3
  • ;-- for an in-block statement terminator only works in the CLP. You need to choose a different terminator for the block and configure your client accordingly
    – mustaccio
    Apr 19, 2021 at 13:49
  • Thanks @mustaccio Do you know how to do this for DBeaver?
    – Badger
    Apr 19, 2021 at 14:21
  • @Lennart will make your comment into an answer?
    – Badger
    Apr 20, 2021 at 6:06

1 Answer 1

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

enter image description here

Now you can alter the statement delimiter

2
  • Thanks @lennart I'll accept the answer as a workaround. I don't think it is possible to change the 'in-block' terminator this solution changes the 'regular' terminator. I changed "Statements delimiter" to @, "Ignore native delimiter" and "Blank line is statement delimiter" is checked, then my statement works by replacing only the last ; to @ or by leaving a blank line
    – Badger
    Apr 21, 2021 at 18:37
  • Yes, the statement separator inside for example a procedure can not be changed, it has to be; One therefor change the statement terminator to something different, I usually choose @ for this Apr 21, 2021 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.