SEO

Hello,

Sorry if my question is elementary. I've searched google, but can't
seem to find exactly what I'm looking for.

My client is using db2 8.2. They have 2 companies and each company has
a different database running under the same instance. I have created a
stored procedure in one database that takes data and inserts it into
the other database. It works perfect when I call the procedure this way
in the CLP "CALL DATACOPY(12345)"...I need some way of automating this
data transfer. I tried putting the call into a trigger to no avail.
When searching google I read that this functionality is a current
limitation of DB2. One post talked about using the words "FEDERATED"
when creating the procedure. However, the db2 release notes say this
functionality was taken out of the docs as it is unsupported. I
couldn't get it to work either. In an ideal world, being able to run
this code from a trigger would solve all my problems. This is my
current declaration for the SP: (Is there something I can magically
include to make this whole system work?)

CREATE PROCEDURE DATACOPY(
IN ORIGINAL_ID INT
)
LANGUAGE SQL
MAIN : BEGIN

I guess my main question is, am I wasting my time...is it just not
possible to in any way trigger events which use federated objects?

Thank you in advance for any suggestions or applicable links.

mas5@sfu.ca wrote:
> Hello,
>
> Sorry if my question is elementary. I've searched google, but can't
> seem to find exactly what I'm looking for.
>
> My client is using db2 8.2. They have 2 companies and each company has
> a different database running under the same instance. I have created a
> stored procedure in one database that takes data and inserts it into
> the other database. It works perfect when I call the procedure this way
> in the CLP "CALL DATACOPY(12345)"...I need some way of automating this
> data transfer. I tried putting the call into a trigger to no avail.
> When searching google I read that this functionality is a current
> limitation of DB2. One post talked about using the words "FEDERATED"
> when creating the procedure. However, the db2 release notes say this
> functionality was taken out of the docs as it is unsupported. I
> couldn't get it to work either. In an ideal world, being able to run
> this code from a trigger would solve all my problems. This is my
> current declaration for the SP: (Is there something I can magically
> include to make this whole system work?)
>
> CREATE PROCEDURE DATACOPY(
> IN ORIGINAL_ID INT
> )
> LANGUAGE SQL
> MAIN : BEGIN
>
> I guess my main question is, am I wasting my time...is it just not
> possible to in any way trigger events which use federated objects?
>
> Thank you in advance for any suggestions or applicable links.
>
Yes, you are wasting your time. The problem is that your trigger
operates on your local database and the procedure operates on the
'remote" database. All this has to be ATOMIC (hence BEGIN ATOMIC .. END
in the trigger body). DB2 UDB V8.2 does not support federated 2 phase
commit.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thank you very much!