[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 955
  • Last Modified:

Federated Database

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.
0
vr_bala
Asked:
vr_bala
  • 2
1 Solution
 
ghp7000Commented:
I would try one of two possible solutions
1) after insert operation, commit the transaction, then do the update ( I assume the line should read update administrator.t1)
2) seperate the two transactions into 2 procedures

dont forget to commit your transactions at the end of the procedure as well
0
 
vr_balaAuthor Commented:
If i issue commit in between statements, i may get data inconsitency. Suppose if update failed, the insert cannot undo.
0
 
ghp7000Commented:
well, federated database does not support two phase commit, which is what you are trying to do
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now