Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2010
    Posts
    40

    Issues with creating tirggers on federated objects

    Hi there experts,

    I am in a situation here. I need to setup an environment between two database servers where if updates happen in the tables in one database, the same should be replicated to the database on the other server. I am running db9.5.8 and have been successfully able to federate one database with another (Wrapper, server, usermapping and nicknames). Now I am trying to create a trigger to fire updates on the base table so that I can see the updates in the tables in destination database via the nickname. I was not able to create triggers on the base table due to the following
    "SQL30090N Operation invalid for application execution environment. Reason
    code = "22"."

    Assuming that that triggers is not supported by federation I took another approach of creating a federated view with union all between the base table and the nickname, the view works fine and is retrieving all values for both base table and the remote tables . So now I tried to setup the instead of trigger assuming I can update the view instead of the tables but I am not able to create the trigger again for the same reason SQL30090N. Can you guys advice me on this or suggest other alternatives or if I am doing something wrong here or am I way off the track

    Thanks much for all the help

    Smriti

  2. #2
    Join Date
    Mar 2003
    Posts
    279
    I was facing the same issue a while back. All I could come up with was to use dynamic sql in a procedure, and call that in the trigger body. Summary from:

    https://groups.google.com/d/topic/co...NO8/discussion

    >>>

    As a follow up to an earlier discussion in this group on triggers and
    federated tables. This relates to the problem that I'm trying to solve,
    so I'm posting it in this thread:

    It's easy to set up a server for two-phase commit so that one can update
    both databases in the same transaction:

    connect to s ;

    create table t1
    ( x int not null primary key
    , y int not null ) ;

    connect to m ;

    create table t1
    ( x int not null primary key
    , y int not null ) ;

    CREATE WRAPPER DRDA
    OPTIONS( DB2_FENCED 'N');

    CREATE SERVER S TYPE DB2/UDB VERSION 9
    WRAPPER DRDA
    AUTHORIZATION "db2inst1" PASSWORD "********"
    OPTIONS( DBNAME 'S', PASSWORD 'Y', DB2_TWO_PHASE_COMMIT 'Y' );

    CREATE USER MAPPING FOR "db2inst1" SERVER "S"
    OPTIONS ( REMOTE_AUTHID 'db2inst1', REMOTE_PASSWORD '*******');

    CREATE NICKNAME S.T1
    FOR s.db2inst1.t1;


    db2 +c "insert into t1(x,y) values (1,1)"
    DB20000I The SQL command completed successfully.
    [db2inst1@nya-09 ~/nobackup/ltjn/tek122/federation]$ db2 +c "insert into
    s.t1(x,y) values (1,1)"
    DB20000I The SQL command completed successfully.
    [db2inst1@nya-09 ~/nobackup/ltjn/tek122/federation]$ db2 commit
    DB20000I The SQL command completed successfully.


    alles gut. However, it is not possible to directly create a trigger on
    t1 that updates s.t1:

    SQL30090N Operation invalid for application execution environment. Reason
    code = "22". LINE NUMBER=3. SQLSTATE=25000

    Not sure what tha rationale behind this is. Here is a - ugly - way of
    overcoming this restriction:


    create procedure db2inst1.proc (in_x int, in_y int)
    language sql
    begin atomic
    declare stmt varchar(100);
    set stmt = 'insert into s.t1 (x,y) values (' ||
    char(in_x) || ',' || char(in_y) || ')';
    execute immediate stmt;
    end @

    create trigger db2inst1.trg
    after insert on db2inst1.t1
    referencing new as n
    for each row
    call db2inst1.proc(n.x, n.y)
    @


    [db2inst1@nya-09 ~/nobackup/ltjn/tek122/federation]$ db2 +c "insert into
    s.t1(x,y) values (2,2)"
    DB20000I The SQL command completed successfully.
    [db2inst1@nya-09 ~/nobackup/ltjn/tek122/federation]$ db2 commit
    DB20000I The SQL command completed successfully.
    [db2inst1@nya-09 ~/nobackup/ltjn/tek122/federation]$ db2 "select * from
    s.t1"

    X Y
    ----------- -----------
    1 1
    2 2

    2 record(s) selected.


    /Lennart
    --
    Lennart

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,426
    You could try performing a federated operation in a stored procedure that is called by the trigger.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Nov 2010
    Posts
    40
    hey guys thanks for quick reply. I tried the method you have suggested. I also tried your example in my env and it works like a charm. But when I am trying to do the same on my test table I get this error while creating the tirgger

    "create procedure test.procTest (in_x varchar(10), in_y int)
    language sql
    begin atomic
    declare stmt varchar(100);
    set stmt = 'insert into test.test (x,y) values (' ||
    char(in_x) || ',' || char(in_y) || ')';
    execute immediate stmt;
    end
    DB20000I The SQL command completed successfully.

    create trigger test.trgTest
    after insert on test.test
    referencing new as n
    for each row
    call test.procTest(n.x, n.y)

    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 "N.X" is not valid in the context where it is used. LINE NUMBER=3.
    SQLSTATE=42703"

    My test table has the first column as varchar column and second as int which is a primary key. Is this something to do with the definition of the column? Is a different way of handling the char/varchar and nullable columns

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    TEST1 SYSIBM VARCHAR 10 0 Yes
    TEST2 SYSIBM INTEGER 4 0 No


    Can you please help

    Thanks

  5. #5
    Join Date
    Mar 2003
    Posts
    279
    First, your table has columns named test1 and test2, but the trigger expects them to be x and y.
    Second, since one of your columns (x or test1 dependent on where you look) already is a string type, you don't have to cast it to char in the trigger- This probably wont cause an error, but is unnecessary.
    Third, you will have to handle the possibility of test1 being null. A stmt like:
    Code:
    insert into test.test(y) values (10)
    will cause a runtime error.
    Last edited by lelle12; 02-08-13 at 00:15. Reason: Added third problem
    --
    Lennart

  6. #6
    Join Date
    Nov 2010
    Posts
    40
    Sorry I overlooked that....I could create the proc and the trigger, but now when I insert in the table I get a different error. I am not inserting duplicates. Is it the way I am calling the procedure causing this ?

    create procedure test.procTest (in_x varchar(10), in_y int)
    language sql
    begin atomic
    declare stmt varchar(100);
    set stmt = 'insert into test.F_test(x,y) values (' || (in_x) || ',' || char(in_y) || ')';
    execute immediate stmt;
    end
    DB20000I The SQL command completed successfully.

    create trigger test.trgTest
    after insert on test.test
    referencing new as n
    for each row
    call test.procTest(n.test1, n.test2)

    DB20000I The SQL command completed successfully.


    db2 "insert into test.test values('000',8)"
    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0723N An error occurred in a triggered SQL statement in trigger
    "test.TRGTEST". Information returned for the error includes SQLCODE
    "-206", SQLSTATE "42703" and message tokens "X". SQLSTATE=09000

    Smriti

  7. #7
    Join Date
    Mar 2003
    Posts
    279
    Quote Originally Posted by smritidb2 View Post
    Sorry I overlooked that....I could create the proc and the trigger, but now when I insert in the table I get a different error. I am not inserting duplicates. Is it the way I am calling the procedure causing this ?

    create procedure test.procTest (in_x varchar(10), in_y int)
    language sql
    begin atomic
    declare stmt varchar(100);
    set stmt = 'insert into test.F_test(x,y) values (' || (in_x) || ',' || char(in_y) || ')';
    execute immediate stmt;
    end
    DB20000I The SQL command completed successfully.

    create trigger test.trgTest
    after insert on test.test
    referencing new as n
    for each row
    call test.procTest(n.test1, n.test2)

    DB20000I The SQL command completed successfully.


    db2 "insert into test.test values('000',8)"
    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0723N An error occurred in a triggered SQL statement in trigger
    "test.TRGTEST". Information returned for the error includes SQLCODE
    "-206", SQLSTATE "42703" and message tokens "X". SQLSTATE=09000

    Smriti
    What is the definition of test.F_test? The error means that x is undefined
    --
    Lennart

  8. #8
    Join Date
    Nov 2010
    Posts
    40
    F_test is the nickname on the table that resides in the remote database. This database is federated with my source database where I am trying to create all the objects and run the show from. What I am basically trying to accomplish is to create a trigger on the base table in source db that updates the nickname consecutively when an update happens in the tables in the source database. I am trying to achieve this via federation. I started out with the insert statement to test if that is working for me since I had an example in front of me, then I can write a similar updated trigger.

    The definition of F_test

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    TEST1 SYSIBM VARCHAR 10 0 Yes
    TEST2 SYSIBM INTEGER 4 0 No

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,426
    Quote Originally Posted by smritidb2 View Post
    The definition of F_test

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    TEST1 SYSIBM VARCHAR 10 0 Yes
    TEST2 SYSIBM INTEGER 4 0 No
    Instead of
    Code:
    insert into test.F_test(x,y) values ...
    try
    Code:
    insert into test.F_test(abc,def) values ...
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Nov 2010
    Posts
    40
    still the same error

    $ db2 "insert into test.test values('000',8)"
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0723N An error occurred in a triggered SQL statement in trigger
    "test.TRGTEST". Information returned for the error includes SQLCODE
    "-206", SQLSTATE "42703" and message tokens "ABC". SQLSTATE=09000

  11. #11
    Join Date
    Mar 2003
    Posts
    279
    Quote Originally Posted by smritidb2 View Post
    F_test is the nickname on the table that resides in the remote database. This database is federated with my source database where I am trying to create all the objects and run the show from. What I am basically trying to accomplish is to create a trigger on the base table in source db that updates the nickname consecutively when an update happens in the tables in the source database. I am trying to achieve this via federation. I started out with the insert statement to test if that is working for me since I had an example in front of me, then I can write a similar updated trigger.

    The definition of F_test

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    TEST1 SYSIBM VARCHAR 10 0 Yes
    TEST2 SYSIBM INTEGER 4 0 No
    in the body of the procedure you have
    Code:
    set stmt = 'insert into test.F_test(x,y) values (' || (in_x) || ',' || char(in_y) || ')';
    That wont work with this definition of F_TEST.

    FWIW I think you should stop a minute, and look at the code snippet I posted earlier. Don't continue until you fully understand what it does. Dynamic sql is dangerous stuff since the compiler can't help you validate the code, use with caution!
    --
    Lennart

Posting Permissions

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