All we need is an easy explanation of the problem, so here it is.
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 : 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
How to solve :
As mustaccio points out, you need to change your statement terminator. In DBeaver you can do that by:
- Right-click on the data source and choose "Edit connection"
- In the leftmost frame at the bottom, there is SQL Processing
- Check the Datasource checkbox
Now you can alter the statement delimiter
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂