Federated Database

Posted on 2005-04-28
Last Modified: 2011-09-20
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.
dynamic result sets 1
language sql
for i as select f1 from t2
insert into t2 values ( i.f1 ) ; update administrator.t
set f1 = f1 - 1 ;
end for ;
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.
Question by:vr_bala
    LVL 13

    Expert Comment

    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

    Author Comment

    If i issue commit in between statements, i may get data inconsitency. Suppose if update failed, the insert cannot undo.
    LVL 13

    Accepted Solution

    well, federated database does not support two phase commit, which is what you are trying to do

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now