Re: [Q] cheap way to make DB2 (OS/390) real time update ORACLE tables?
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 21 Jul 2004 07:08:19 +0100
Funny how time flies - I thought I'd asked a similar question about a
week and a half ago, turns out it was nearly a month. anyway I got a
reply which would seem to be highly pertinent to you. You can find the
helpful reply at
//www.freelists.org/archives/oracle-l/06-2004/msg01750.html.
Comments inline but based on only a weeks worth of experience.
On Tue, 20 Jul 2004 11:27:03 -0700 (PDT), dba1 mcc <mccdba1@xxxxxxxxx> wrote:
> I am study DB2 on mainframe to update tables on
> ORACLE (UNIX). The update must be real time and 2PC.
> I know ORACLE has "Transparant Gateway" to do it, but
> it is tooooo expensive (95K). I am look for cheaper
> solution like ODBC or MQ.
>
> My question are:
>
> 1. Any one use ODBC update from DB2 (mainframe) to
> ORACLE?
No this is mssql to oracle. Oracle stored proc querying only the
remote db to update the oracle db.
> 2. is it stable and good performance?
Stable yes. I suspect that running on windows is a help here as far as
driver stability is concerned.
Performance. The sql that you write may end up being rewitten when
sent to the remote database, and in unhelpful ways. For example our
procedure contains a couple of thousand executions of
SELECT <FIELDS OF INTEREST> FROM TABLE@REMOTE WHERE <KEY COLUMN> = :B1;
for a number of tables. In most cases what gets executed at the remote site is
SELECT <FIELDS OF INTEREST> FROM TABLE@REMOTE
and the filtering is done locally. This is A Bad Thing(tm). The
driving_site hint seems to be ineffective as well. Now to be honest
I'm not entirely surprised by the fact that the hint doesn't work, I
am not very surprised that the driver doesn't replace the bind with
the odbc ? style parameter syntax except that sometimes it does.
At the moment I am tending to the view that using this form of
connectivity severely limits Oracle's knowledge of what the other
system can execute and so the least common denominator sql gets passed
for remote execution.
The other thing that I would suggest is that (actually like all
distributed query environments) this sort of setup is a way of casting
a magnifying glass on your code for poor design. In my case for
example there really shouldn't be a couple of thousand single row
queries - the update should be done against the set in which we are
interested. On a oracle-oracle system this poor design gets hidden
because the key lookup gets passed to the remote site - here it
doesn't.
> 3. Anyone use MQ (message Queue) update from DB2 to
> ORACLE?
sorry.
>
> 4. any other solution?
Does DB2 supply a technology that goes the other way?
Can you do periodic loads using sql*loader? I guess this question is
really 'does it *have* to be real time 2PC.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
