Read Chapter 12/Appendix C of Federated Systems Guide!! Also read the
the last line of my posting, it states "Federated systems do 2 not
currently support the two-phase commit protocol"
Also from db2 for your error code:
For reason 18, do one of the following steps:
o Submit a COMMIT or ROLLBACK before issuing an update to a
different data source.
o If multiple data sources need to be updated within a unit of
work, make sure the two_phase_commit server option has been
set to 'Y' for all the data sources that need to be updated.
See the SQL Reference for information on setting the value
used for the two_phase_commit setting.
o If the data source to be updated supports only one-phase
commit and the application is operating with a CONNECT type 2
connection setting, change the application to operate with a
CONNECT type 1 connection setting.
From DB2 Help:
Understanding federated system transaction support
2 Federated system transaction processing builds on DB2(R) distributed
2 database transaction capabilities. To understand federated system
transaction 2 processing, you must understand the following essential
DB2 distributed transaction 2 processing concepts:
2 2 Unit of work (UOW) 2 Remote unit of work (RUOW) 2 Distributed unit
of work (DUOW) 2 Multisite update 2 Transaction manager (TM) 2 Resource
manager (RM) 2 Type 1 connection 2 Type 2 connection 2 One-phase commit
2 Two-phase commit
2 These concepts work identically in both federated and non-federated 2
DB2 systems. However, the scope of each concept changes in a federated
system.
2 For example, a unit of work is implicitly begun when any data 2 in a
database is read or written. For a unit of work in a federated system,
2 the database can be a federated database or a data source database.
For a 2 distributed unit of work in a federated system, you can access
both a federated 2 database and a data source database.
An application must end a unit of work by issuing either a COMMIT or a
ROLLBACK statement, regardless of the number of databases that are
accessed. The COMMIT statement makes permanent all changes made within
a unit of work. The ROLLBACK statement removes these changes from a
database. Changes made by a unit of work become visible to other
applications after a successful commit.
Recommendation: Always explicitly commit or roll back units of work in
your applications.
2 When only one data source is updated in a unit of work, a COMMIT 2
statement for this unit of work can be sent in a single operation. This
operation 2 is called a one-phase commit or single site update. 2 A
site corresponds to a server definition in 2 a federated system. A
federated server is the local site for update operations 2 in a
federated system. Any remote data source is a remote site for update 2
operations in a federated system.
In a one-phase commit operation, no other data sources in the same unit
of work are required to verify their ability to commit data.
Examples of a one-phase commit operation include:
A non-distributed unit of work
2 A distributed unit of work that involves reading from one or 2 more
data sources but updating data at only one data source
2 In a distributed unit of work that involves updates of multiple 2
databases on multiple sites, data must be consistent. The multisite
update 2 or two-phase commit protocol is commonly used to ensure data
consistency across 2 multiple databases within a distributed unit of
work. Federated systems do 2 not currently support the two-phase commit
protocol.
Klemens wrote: Both statements are successfull if they are in separate transactions.
Only in the same transaction it doesn't work.
<hi****@gmail.com> schrieb im Newsbeitrag
news:11**********************@g43g2000cwa.googlegr oups.com... Do you have select/insert/update privileges on the federated table and
local table, if so, you can do that.
Klemens wrote: I get SQL30090 reason 18 by trying to do an insert in a federated table and an update in a local table in one transaction
Do I have to change some settings to get done or ist this not possible by definition?
Thanks
Klemens
