Results 1 to 6 of 6

Thread: db2 trigger

  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: db2 trigger

    Hi,

    Our environment: db2 ese server 8.1.4a on win2k.

    We have a db2 trigger and need to update some values of a table contained in different database. To begin with, both these databases (db1 and db2) are held in the same server.
    For eg: db1 (db2 ese server)
    |
    db1.<table1>.trigger
    |
    ----------------------------------------
    db2 (db2 ese server)
    |
    update table2

    Just wanted to check if there is a easier way other than "federated database objects".

    Thanks in advance.

  2. #2
    Join Date
    Sep 2003
    Posts
    218
    I have successfully set up federated server. This includes creation of wrapper -> server -> user mapping -> nicknames etc. As per the requirement, I created a trigger on database1 to insert/update some values on database2.table2 and obtained the following error.

    [IBM][CLI Driver][DB2/NT] SQL30090N Operation invalid for
    application execution environment. Reason code = "22". LINE
    NUMBER=2. SQLSTATE=25000

    However, if I go through db2 command window, then execute command manually, then it goes through fine.

    Any ideas?? Please treat this as urgent.

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    If this were a urgent matter it would help if you would post the trigger definition itself for us to look at ..........
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by dr_suresh20
    I have successfully set up federated server. This includes creation of wrapper -> server -> user mapping -> nicknames etc. As per the requirement, I created a trigger on database1 to insert/update some values on database2.table2 and obtained the following error.

    [IBM][CLI Driver][DB2/NT] SQL30090N Operation invalid for
    application execution environment. Reason code = "22". LINE
    NUMBER=2. SQLSTATE=25000

    However, if I go through db2 command window, then execute command manually, then it goes through fine.

    Any ideas?? Please treat this as urgent.
    db2 ? sql30090

    22 A federated insert, update, or delete operation is invalid in
    a function, a data-change-table-reference, a dynamic compound
    statement, a trigger, and an application execution environment
    where a

    o SAVEPOINT is in effect

    o scrollable cursor is used

    o target view contains multiple tables or nicknames
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Sep 2003
    Posts
    218
    Thanks for the response.

    Here's the trigger definition:

    CREATE TRIGGER DB2ADMIN.T1 AFTER INSERT ON DB2ADMIN.TAB1 FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
    insert into T2 values ('Remote Insert Test');
    END

  6. #6
    Join Date
    Sep 2003
    Posts
    218
    Ok....for those of you who have a similar situation -- please note that "triggers" are not supported using federated database objects!! in present release of db2 udb ese.

    Hope this helps.

Posting Permissions

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