DB2 and ORACLE in XA transaction

		Home \| Contact Us \| FAQ \| Search & Site Map \| Link to Us
		Sign In \| Join \| Other 45 Sites in Network

Discussion Groups

Database Servers

DB2 Informix Ingres MS SQL Oracle Pervasive.SQL PostgreSQL Progress Sybase

Desktop Databases

FileMaker FoxPro MS Access Paradox

General

General DB Topics Database Theory

Related Topics

Java Development .NET Development VB Development More Topics ...

Database Forum / DB2 Topics / April 2007

DB2 and ORACLE in XA transaction

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gregor Kovač - 24 Apr 2007 13:08 GMT

Hi!

I'm using DB2 database on one server and ORACLE database on the second one.
I want to move some data from ORACLE database to DB2 database and update
some columns on ORACLE database so I know what rows were transfered. I'd
like to do this in a distributed XA transaction and Java. :)
How is this possible ?

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

Knut Stolze - 24 Apr 2007 15:12 GMT

> Hi!
>
[quoted text clipped - 3 lines]
> transfered. I'd like to do this in a distributed XA transaction and Java.
> :) How is this possible ?

I'm not sure what your question is. My answer would be: open one XA
transaction to DB2, another one to Oracle, do the operations you want to
do, then perform a two-phase commit.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

DA Morgan - 24 Apr 2007 16:06 GMT

>> Hi!
>>
[quoted text clipped - 7 lines]
> transaction to DB2, another one to Oracle, do the operations you want to
> do, then perform a two-phase commit.

Or alternatively purchase Oracle's Heterogeneous Services (formerly
Transparent Gateway). But without version information you are asking
people to shoot in the dark.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Reply to this Message

Gregor Kovač - 25 Apr 2007 05:41 GMT

>> Hi!
>>
[quoted text clipped - 7 lines]
> transaction to DB2, another one to Oracle, do the operations you want to
> do, then perform a two-phase commit.

Thanks for you answer. Maybe I was a bit unclear.
On one server I have an ORACLE database (don't know what version, yet :)) ).
One another I have DB2 v9.1. Now I want to move data from ORACLE to DB2 and
from DB2 to ORACLE. Using distributed transactions would allow me to do
commit/rollback on both ends, right? I'm using Java and don't know how to
do it (yet). :))
Now I have something like this:
// Open connection to ORACLE
Connection oracleConn = DriverManager.getConnection(....);
oracleConn.setautoCommit(false);
// Open connection to DB2
Connection db2Conn = DriverManager.getConnection(....);
db2Conn.setautoCommit(false);

// select data from DB2
ResultSet rs = db2Conn.createStatement().executeQuery("SELECT * FROM
TABLE1");
while (rs.next() == true)
{
// insert data into ORACLE
// update some rows in DB2
}

db2Conn.commit(); // 1
oracleConn.commit(); // 2

In this example these is an error since commit to db2Conn (1) and be
performed and commit to oracleConn (2) can fail and I end up with data I
don't want to have. :))

I'm curious how to do the XA transactions? Do I have to use something like
DriverManager.getXAConnection() ?

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

Michael Ortega-Binderberger - 25 Apr 2007 07:59 GMT

On Wed, 25 Apr 2007, Gregor Kova~M wrote:

>>> Hi!
>>>
[quoted text clipped - 43 lines]
> Best regards,
> Kovi

Hi,
You would need to use the jdbc calls for XA, you can look
them up in the manuals. You will have other problems though,
if you do this, then you will be playing the role of TM,
which, if your application is serious and not a simple toy,
then playing this role is quite a lot of work as you need to
keep track of transaction id's, server responses, etc in a
reliable way, just to ensure correctness in case one or both
of the databases go down, and transactions are not left
pending.

OR, you could just use DB2's federated capability to do what
you want. I've used it between oracle and db2 and it works
fine. It takes care of the TM role for you, and you need no
programming, just some configuration steps, then access db2
through jdbc as if it was a single database and forget about
multiple databases.

Its a separately licensed feature, not sure if you can
afford it, but would definitely save you lots of work.

Michael

Reply to this Message

Harold Lee - 25 Apr 2007 21:11 GMT

> I'm using DB2 database on one server and ORACLE database on the second one.
> I want to move some data from ORACLE database to DB2 database and update
> some columns on ORACLE database so I know what rows were transfered. I'd
> like to do this in a distributed XA transaction and Java. :)
> How is this possible ?

One way is to use Federation. The NET8 wrapper for Oracle supports XA,
and the SQL would be simple if your Oracle tables are mapped into the
DB2 database as Nicknames.

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.webs
phere.ii.federation.query.doc/administering/iiyfqtpc01.html

Harold Lee
haroldl@us.ibm.com

Reply to this Message

Harold Lee - 25 Apr 2007 23:27 GMT

> I'm using DB2 database on one server and ORACLE database on the second one.
> I want to move some data from ORACLE database to DB2 database and update
> some columns on ORACLE database so I know what rows were transfered. I'd
> like to do this in a distributed XA transaction and Java. :)
> How is this possible ?

Another option is to use DB2 Federation to create a nickname in the
DB2 database for the Oracle table(s). The NET8 wrapper supports XA for
two phase commit:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.webs
phere.ii.federation.query.doc/administering/iiyfqtpc01.html

This would make your SQL very simple because it would be the same as
moving rows around within a DB2 database.

Harold Lee
haroldl@us.ibm.com

Reply to this Message

Mark Townsend - 26 Apr 2007 04:05 GMT

You can also do this from the other direction - use heterogeneous
services from the Oracle database to copy the rows from Oracle to DB2
and then update the Oracle rows.

See
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14232/gencon.htm#s
thref343

Heterogeneous Services are included for free in all Oracle databases
from Oracle8i onwards, but they use ODBC as the comm layer. If you want
something more performant than an ODBC connection, there are the
specific Transparent Gateways available for cost - in this case see the
DRDA one at
http://download-west.oracle.com/docs/cd/B19306_01/gateways.102/b16218/toc.htm

Usage is the same - simply set up a DBLink (remote_db2_db) over
heterogeneous services or the DRDA Transparent Gateway and issue
something like

INSERT INTO remote_db2_table@remote_db2_db SELECT column_list FROM
local_oracle_table;

where you are connected to Oracle, and remote_db2_table is the DB2 table
that you want to insert into on the other side of the remote_db2_db DBLink.

You could wrap this in the begin transaction/commit with an update to
the local oracle table as well.

Alternatively, the COPY command issued from SQLPlus is very efficient

COPY FROM SCOTT/TIGER@local_oracle_db INSERT
remote_db2_table@remote_db2_db USING SELECT * FROM local_oracle_table;

Reply to this Message

Frank Swarbrick - 26 Apr 2007 17:24 GMT

>>> On 4/25/2007 at 9:05 PM, in message

<0GUXh.904$tp5.177@newssvr23.news.prodigy.net>, Mark
Townsend<markbtownsend@sbcglobal.net> wrote:

> You can also do this from the other direction - use heterogeneous
> services from the Oracle database to copy the rows from Oracle to DB2
> and then update the Oracle rows.
>
> See
> http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14232/genco

> n.htm#sthref343
>
[quoted text clipped - 4 lines]
> DRDA one at
> http://download-west.oracle.com/docs/cd/B19306_01/gateways.102/b16218/toc

> .htm

You seem to have a lot of Oracle knowledge. Do you have any idea on the
price of the Oracle Transparent Gateway for IBM DRDA? I am in fear of a
price similar to the IBM WebSphere Federation Server ($125,000!!!).

Frank

Reply to this Message

DA Morgan - 26 Apr 2007 22:15 GMT

>>>> On 4/25/2007 at 9:05 PM, in message
> <0GUXh.904$tp5.177@newssvr23.news.prodigy.net>, Mark
[quoted text clipped - 22 lines]
>
> Frank

Going to store.oracle.com and searching for "DB2" will give you a list
of products and prices. Buying from an Oracle sales rep will often save
you money. The prices I see on the site are listed in hundreds of
dollars ... not hundreds of thousands of dollars.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Reply to this Message

Serge Rielau - 26 Apr 2007 22:49 GMT

>>>>> On 4/25/2007 at 9:05 PM, in message
>> <0GUXh.904$tp5.177@newssvr23.news.prodigy.net>, Mark
[quoted text clipped - 27 lines]
> you money. The prices I see on the site are listed in hundreds of
> dollars ... not hundreds of thousands of dollars.

Similar to the ODBC interface, presuming that Oracle can serve as an
OLEDB provider (or a webservice provider...) once can write an OLEDB
table function or webservice. That would be free.
OLEDB of course assumes Windows...

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

DA Morgan - 27 Apr 2007 00:11 GMT

>>>>>> On 4/25/2007 at 9:05 PM, in message
>>> <0GUXh.904$tp5.177@newssvr23.news.prodigy.net>, Mark
[quoted text clipped - 31 lines]
> table function or webservice. That would be free.
> OLEDB of course assumes Windows...

And writing your own assumes no need for support.

$125K Serge? Does it come in a solid-platinum case from Tiffany?

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Reply to this Message

Serge Rielau - 27 Apr 2007 02:16 GMT

> And writing your own assumes no need for support.

Yeah, why do something myself if I can pay for it...
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0405brown/

Least we forget Kovi just needed to shuffle a bit of data.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

DA Morgan - 27 Apr 2007 15:35 GMT

>> And writing your own assumes no need for support.
> Yeah, why do something myself if I can pay for it...
[quoted text clipped - 4 lines]
> Cheers
> Serge

If anyone has invested the resources required, hardware and software,
to support both DB2 and Oracle ... whatever they are doing is important
enough to do it properly.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Reply to this Message

The Boss - 26 Apr 2007 23:36 GMT

>>>>> On 4/25/2007 at 9:05 PM, in message
>> <0GUXh.904$tp5.177@newssvr23.news.prodigy.net>, Mark
[quoted text clipped - 26 lines]
> save you money. The prices I see on the site are listed in hundreds of
> dollars ... not hundreds of thousands of dollars.

??

When I go to store.oracle.com, I have to choose a country first.
Selecting Netherlands and searching for "db2" doesn't give a single result.
Selecting US and searching for "db2" only gives entries for:
- Secure Enterprise Search Connector - IBM DB2 Content Manager
- System Monitoring Plug-in for Non Oracle Databases - IBM DB2

However, I found info on "Enterprise Integration Gateways" at:
http://oraclestore.oracle.com/OA_HTML/ibeCCtpSctDspRte.jsp?section=11002

<quote>
Oracle Enterprise Integration Gateways provide a broad range of
capabilities:

Standard Oracle SQL can access DB2 data made available via IBM's DRDA
(Distributed Relational Database Architecture) on OS/390 (z/OS), VM/ESA,
VSE/ESA, OS/400, and popular UNIX systems. Applications using the
Transparent Gateway for DRDA need not know the physical location of the
data, the network protocol, or the target operating system. This creates the
appearance that all data resides in one local Oracle database, and
eliminates the need to duplicate the data on different platforms, minimizing
redundant and inconsistent data. Support for transactional consistency
between Oracle and DB2 maintains data integrity, even in a distributed
update environment.
</quote>

followed by similar info for other Gateway variants (for Websphere, AS/400,
...).
At the bottom of the page:

<quote>
Check Licensing Definitions
Please Select:
Enterprise Integration Gateways - Computer Perpetual[?27,276.00]
</quote>

A little more than "hundreds of dollars" (let alone euros)...
And I'm sure my customer using "OTG for DB2" pays a lot more than a couple
of hundreds of dollars/euros as well.

Signature

Jeroen

Reply to this Message

Frank Swarbrick - 27 Apr 2007 00:05 GMT

>>> On 4/26/2007 at 4:36 PM, in message

<46312950$0$338$e4fe514c@news.xs4all.nl>, The
Boss<usenet@No.Spam.Please.invalid> wrote:

> When I go to store.oracle.com, I have to choose a country first.
> Selecting Netherlands and searching for "db2" doesn't give a single
[quoted text clipped - 40 lines]
> couple
> of hundreds of dollars/euros as well.

Yep. For U.S.A. I show $35,000.
Still a lot if I was paying for it myself, but also a lot less than
$125,000.

Thanks for finding this.

Frank

Reply to this Message

DA Morgan - 27 Apr 2007 00:13 GMT

>>>>>> On 4/25/2007 at 9:05 PM, in message
>>> <0GUXh.904$tp5.177@newssvr23.news.prodigy.net>, Mark
[quoted text clipped - 63 lines]
> And I'm sure my customer using "OTG for DB2" pays a lot more than a couple
> of hundreds of dollars/euros as well.

http://store.oracle.com
Select United States
At the top where it says "Quick Search | All Products" type in "DB2"

I have no idea why it isn't there for your country but then I don't
work for Oracle.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Reply to this Message

Michael Ortega-Binderberger - 27 Apr 2007 02:30 GMT

>>>>> On 4/25/2007 at 9:05 PM, in message
>> <0GUXh.904$tp5.177@newssvr23.news.prodigy.net>, Mark
[quoted text clipped - 26 lines]
> you money. The prices I see on the site are listed in hundreds of
> dollars ... not hundreds of thousands of dollars.

I don't know where you got the 125K price for db2 with
federation. If you already have a db2 (which I guess you do
since you want to integrate it with oracle), then " adding"
the feature is not as expensive as buying a whole new
package. Also, there are several levels, there is standard,
enterprise and whatever. Seems like the 125K you found is
for the enterprise unlimeted that includes a db2 license and
all the wrappers. If you need only oracle, then you can
purchase "by wrapper". its best if you get an ibm sales rep
to help you (ok, pause so can laugh for a bit). I'm sure
that if all you want is to access 1 oracle server, you can
get it for a lot less that 125K.

Whether you go with oracle gateway or db2 federated, is
probably up to what your site has a preference for. If
you're mostly a db2 shop, you should get good pricing from
ibm, else, put the squeeze on oracle if you're an oracle
shop.
Note that the drda wrapper is free with a db2 license.
Michael

Reply to this Message

Frank Swarbrick - 27 Apr 2007 16:06 GMT

>>> On 4/26/2007 at 7:30 PM, in message
>
[quoted text clipped - 17 lines]
> shop.
> Note that the drda wrapper is free with a db2 license.

https://www-112.ibm.com/software/howtobuy/buyingtools/paexpress/Express?P0=E
1&part_number=D59J1LL,D59IVLL,D59IQLL,D59IXLL&catalogLocale=en_US&Locale=en_
US&country=USA&PT=html&S_TACT=none&S_CMP=none

IBM WebSphere Federation Server 2 Base Processors License + SW Maintenance
12 Months (D59IVLL) 125,000.00

Our IBM Business Partner also quoted us a similar price, less a bit of a
discount.
If it's true that you can buy only the wrappers you really need then this is
good news. Perhaps we'll have to slap our IBM BP upside the head to get us
some more realistic pricing.

Frank

Reply to this Message

Mark Townsend - 27 Apr 2007 03:49 GMT

I work for Oracle. The TG for DRDA is part of the Oracle Enterprise
Integration Gateways suite which includes the TG for DRDA, the APPC
gateway, a native DB2/400 gateway, an MQSeries gateway, and the ability
to access Oracle from AS/400 programs. The suite is $35K US per server -
the server referred to is the server on which the Oracle database
resides that access these different systems.

Reply to this Message

Sign In

Join

My Latest Posts

My Monitored Threads

My Photo Gallery

My Homepage

Start New Thread

Enable EMail Alerts

Rate this Thread

©2008 Advenet LLC Privacy Policy - Terms of Use

This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.