OK, got a nice one for you...

Scenario is, have a view, unioning two tables. (MAIN and ARCHIVE)
It handles attachments for a particular app, one field is a clob.
Instead of inserts work, and instead of updates work.
However, the instead of deletes are having trouble processing.

It is supposed to when deleting through the main view,
insert into a history table (just in case)
delete from main table if existing there
delete from the archive table if existing there...

The interface I'm going through has been set with a terminating character, "$"
so this is the DDL and the errors I'm getting.

------------------------------ Commands Entered ------------------------------
CREATE TRIGGER COPCON.DELETE_ATTACHMENTDATA INSTEAD OF DELETE
ON COPCON.ATTACHMENTDATA REFERENCING OLD AS o
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO COPSYS.ATTACHDATA_HISTORY VALUES(o.ATTACHMENTDATAID, o.ATTVERSION, o.DATA, o.ATTACHMENTID);
DELETE FROM COPCON.ATTACHMENTDATA_MAIN WHERE ATTACHMENTDATAID = o.ATTACHMENTDATAID;
DELETE FROM COPCON.COMARCT_ATTACHMENTDATA WHERE ATTACHMENTDATAID = o.ATTACHMENTDATAID;
END;
------------------------------------------------------------------------------
CREATE TRIGGER COPCON.DELETE_ATTACHMENTDATA INSTEAD OF DELETE
ON COPCON.ATTACHMENTDATA REFERENCING OLD AS o
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO COPSYS.ATTACHDATA_HISTORY VALUES(o.ATTACHMENTDATAID, o.ATTVERSION, o.DATA, o.ATTACHMENTID)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "ATA,
o.ATTACHMENTID)". Expected tokens may include: "<delim_semicolon>". LINE
NUMBER=5. SQLSTATE=42601

DELETE FROM COPCON.ATTACHMENTDATA_MAIN WHERE ATTACHMENTDATAID = o.ATTACHMENTDATAID
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N "O.ATTACHMENTDATAID" is not valid in the context where it is used.
SQLSTATE=42703

DELETE FROM COPCON.COMARCT_ATTACHMENTDATA WHERE ATTACHMENTDATAID = o.ATTACHMENTDATAID
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N "O.ATTACHMENTDATAID" is not valid in the context where it is used.
SQLSTATE=42703

END
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

Explanation:

A syntax error in the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure was detected at the specified token
following the text "<text>". The "<text>" field indicates the 20
characters of the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

As an aid, a partial list of valid tokens is provided in the SQLERRM
field of the SQLCA as "<token-list>". This list assumes the statement is
correct to that point.

The statement cannot be processed.

User response:

Examine and correct the statement in the area of the specified token.

sqlcode: -104

sqlstate: 42601

Ignore the last ; after the end, GUI is set to use $.

Any feedback would be greatly appreciated,
I think I'm close...just having a brainfart.

Did you try running the script from the command line?

From command line...

[]$ db2 "CREATE TRIGGER COPCON.DELETE_ATTACHMENTDATA INSTEAD OF DELETE
> ON COPCON.ATTACHMENTDATA REFERENCING OLD AS o
> FOR EACH ROW MODE DB2SQL
> BEGIN ATOMIC
> INSERT INTO COPSYS.ATTACHDATA_HISTORY VALUES(o.ATTACHMENTDATAID, o.ATTVERSION, o.DATA, o.ATTACHMENTID);
> DELETE FROM COPCON.ATTACHMENTDATA_MAIN WHERE ATTACHMENTDATAID = o.ATTACHMENTDATAID;
> DELETE FROM COPCON.COMARCT_ATTACHMENTDATA WHERE ATTACHMENTDATAID = o.ATTACHMENTDATAID;
> END$"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"ACHMENTDATAID; END$ ". Expected tokens may include: "JOIN <joined_table>".
LINE NUMBER=8. SQLSTATE=42601

and if using the ; after END...
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token ";" was found following "TTACHMENTDATAID; END".
Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=8.
SQLSTATE=42601

You are not specifying "$" as the statement delimiter on the command line. I suspect you have the same problem with your GUI as well.

As I said, I did that...still not working.

Try this:
That will use '@' as a statement terminator. Using '$' on a shell is typically not a good idea (at least on UNIX systems) because the shell interprets that first.

I did this before...
Got a better statement from doing it in db2cc...

I simplified it even more, getting rid of the insert statement.
And my apologies, I forgot to mention that one of the "tables" it is deleting from is a nickname to a federated table.

And so...
CREATE TRIGGER DELETE_ATTACHMENTDATA
INSTEAD OF DELETE ON COPCON.ATTACHMENTDATA
REFERENCING OLD AS o FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DELETE FROM COPCON.ATTACHMENTDATA_MAIN WHERE ATTACHMENTDATAID = o.ATTACHMENTDATAID;
DELETE FROM COPCON.COMARCT_ATTACHMENTDATA WHERE ATTACHMENTDATAID = o.ATTACHMENTDATAID;
END
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL30090N Operation invalid for application execution environment. Reason
code = "22". LINE NUMBER=6. SQLSTATE=25000

SQL30090N Operation invalid for application execution environment. Reason code = "22 ".
22 A federated insert, update, or delete operation, or a call to
federated procedure with an SQL data access indication of
MODIFIES SQL DATA is invalid in a function, a
data-change-table-reference, a dynamic compound statement, a
trigger, and an application execution environment where a
* SAVEPOINT is in effect
* scrollable cursor is used
* target view contains multiple tables or nicknames

Once I comment out the 2nd delete, the trigger creates with no issue.
So now I've got to figure out how to still delete from the 2nd table.

hmm...perhaps trying to call a procedure and passing along the id?