Triggers in DB2 LUW – getting error

by Nov 4, 2017

Following is sample script that works fine in other tools. But in AQUA I get error

CREATE TRIGGER APPDBA.”Test3″
AFTER DELETE ON APPDBA.TEST_PLAN
REFERENCING OLD AS OLD FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
select 1 FROM SYSIBM.SYSDUMMY1;
END
;

>[Error] Script lines: 1-6 ————————–
An unexpected token “END-OF-STATEMENT” was found following “ROM SYSIBM.SYSDUMMY1”. Expected tokens may include: “JOIN “.. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.67.28 

Response

Sachin Prakash over 6 years ago
Hi Mukul,

You should disable these two options :

File->Options->General->[‘;’ Statement separator]
File->Options->Scripts->[DB2 LUW 8.x/9.x/10.x/11.x]->[‘;’ Statement separator]

Once you disable these you will not get the semicolon error. These options are disabled by default.

Mukul Garg over 5 years ago
Hi Sachin,

In our shop all existing tools use ‘;’ as statement separator. That is the reason we enabled these options.

So our prefered way would be to keep using ‘;’. Is this something we can put as feature request?

Nevertheless I did try to disable those options and still got an error.

>[Error] Script lines: 23-29 ————————
An unexpected token “” was found following “”. Expected tokens may include: “SIBM.SYSDUMMY1”.. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.67.28 

Niels Gron over 5 years ago
Hi Mukul,

The default statement separator for Aqua Data Studio is “go” or “/” on a separate line. You have the option to use “;” as a statement separator, but if you enable this you will not be able to use Procedural Code (eg Procedures, Triggers, Functions) because the procedural language for DB2 uses “;” in it. There is a note in the description of the option to enable “;” to warn the user. Each database vendor has a proprietary procedural language, so Aqua Data Studio doesn’t have a custom parser for each of these. We do have in our notes to possibly add support but this has not made our feature pipeline because of cost/benefit.

-Niels