Federated Insert and local update in on transaction not possible

[Klemens]

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

[hikums]

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

[Klemens]

Both statements are successfull if they are in separate transactions.
Only in the same transaction it doesn't work.

<hi****@gmail.c om> schrieb im Newsbeitrag
news:11******** **************@ g43g2000cwa.goo glegroups.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

[hikums]

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_commi t 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_commi t 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.c om> schrieb im Newsbeitrag
news:11******** **************@ g43g2000cwa.goo glegroups.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

[Klemens]

Thanks,
I had missed the information that two-phase commit is currently not
supported on federated systems.
So my next question to ibm people: When will it be supported?
<hi****@gmail.c om> schrieb im Newsbeitrag
news:11******** *************@g 14g2000cwa.goog legroups.com...

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_commi t 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_commi t 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.c om> schrieb im Newsbeitrag
news:11******** **************@ g43g2000cwa.goo glegroups.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

[Serge Rielau]

Klemens wrote:

Thanks,
I had missed the information that two-phase commit is currently not
supported on federated systems.
So my next question to ibm people: When will it be supported?

In a "future release". Seriously it's high up in the priority list.
If you need better information you'll need to go through the official
channel and be prepared to sign a dotted-line.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab