federated database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vrbala@gmail.com - 29 Apr 2005 08:01 GMT

Hi All,

I have a issue in federated database.

I will explain the scenario
I have a table T1 in database called offlinedb. It has one column F1
I have an another table T2 in database uatdb.
I added attached table T1 in offlinedb using Federated Objects.
Then i created a procedure P1 in uatdb database with the following
contents.
CREATE PROCEDURE P1
dynamic result sets 1
language sql
MODIFIES SQL DATA
BEGIN
for i as select f1 from t2
do
insert into t2 values ( i.f1 ) ; update administrator.t
set f1 = f1 - 1 ;
end for ;
END

If i execute the procedure P1, i am getting the following error.
Error: DB2 SQL error: SQLCODE: -30090, SQLSTATE: 25000, SQLERRMC: 18

Please give some suggestions to rectify this issue. We need to
incorporate this logic in our production server.

Regards,
Balasubramanian R

Reply to this Message

Rhino - 29 Apr 2005 13:43 GMT

> Hi All,
>
[quoted text clipped - 23 lines]
> Please give some suggestions to rectify this issue. We need to
> incorporate this logic in our production server.

How about looking up this error in the Messages Reference manual? The cause
of the error and the solution is described there.

Rhino

Reply to this Message

EDWARD LIPSON - 29 Apr 2005 16:18 GMT

I assue you read the manual and saw that reason 18 is a 2PC issue.
From the manual section on coordination of 2PC

Both the native SQL and TP monitor multisite update programs must be
precompiled with the CONNECT 2 SYNCPOINT TWOPHASE options. Both can
use the SQL Connect statement to indicate which database they want to
be used for the SQL statements that follow. If there is no TP monitor
to tell DB2 it is going to coordinate the transaction (as indicated by
DB2 receiving the xa_open calls from the TP monitor to establish a
database connection), then the DB2 software will be used to coordinate
the transaction.

You have to configure 2PC. Federated may not do the correct type of
connect for you. I did not check that part of the manual in detail. Connect
String might work, or there could be some other options to tweak. From
the section I snipped above, doing a second connect to the db directly
should work. Then you get into stored procedures and connect statements.

V > Hi All,

V > I have a issue in federated database.

V > I will explain the scenario
V > I have a table T1 in database called offlinedb. It has one column F1
V > I have an another table T2 in database uatdb.
V > I added attached table T1 in offlinedb using Federated Objects.
V > Then i created a procedure P1 in uatdb database with the following
V > contents.
V > CREATE PROCEDURE P1
V > dynamic result sets 1
V > language sql
V > MODIFIES SQL DATA
V > BEGIN
V > for i as select f1 from t2
V > do
V > insert into t2 values ( i.f1 ) ; update administrator.t
V > set f1 = f1 - 1 ;
V > end for ;
V > END

V > If i execute the procedure P1, i am getting the following error.
V > Error: DB2 SQL error: SQLCODE: -30090, SQLSTATE: 25000, SQLERRMC: 18

V > Please give some suggestions to rectify this issue. We need to
V > incorporate this logic in our production server.

V > Regards,
V > Balasubramanian R

Edward Lipson via Relaynet.org Moondog
edward.lipson@moondog.com elipson@bankofny.com
---
þ MM 1.1 #0361 þ

Reply to this Message