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 at 13:49
  • Thanks @mustaccio Do you know how to do this for DBeaver? – Badger Apr 19 at 14:21
  • @Lennart will make your comment into an answer? – Badger Apr 20 at 6:06
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 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 – Lennart Apr 21 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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