If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Issues with creating tirggers on federated objects

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,399
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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.
__________________
--
Lennart

Last edited by lelle12; 02-08-13 at 00:15. Reason: Added third problem
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #9 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,399
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.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On