Re: [Q] cheap way to make DB2 (OS/390) real time update ORACLE tables?

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
http://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_at_yahoo.com> 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_at_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_at_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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------