Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56

    Unanswered: "instead of" delete

    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

  2. #2
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rogue49 View Post
    Ignore the last ; after the end, GUI is set to use $.
    Did you try running the script from the command line?

  4. #4
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    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

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You are not specifying "$" as the statement delimiter on the command line. I suspect you have the same problem with your GUI as well.

  6. #6
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    As I said, I did that...still not working.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Try this:
    Code:
    db2 -td@ "CREATE TRIGGER ...; END@"
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •