Distributed DB2 - Oracle/Oracle - Oracle/DB2 - DB2/DB2 
Author Message
 Distributed DB2 - Oracle/Oracle - Oracle/DB2 - DB2/DB2

Sorry for the strange subject line.  I am working on a system that has Oracle
running on an RS/6000 environment, which must interface with DB/2 on an OS/390
MVS environment (version 4.x).   There is an interface job running from oracle
over the Oracle Transparent Gateway for DB2.   Unfortunately, it is reading one
very important table for a little bit too long, causing a timeout on processes
trying to write to the table.  We are trying to minimize the time the read lock
is in existance.  Dirty reads and mirroring the table are not options (48
million row partitioned table)..   We have noticed that in a heterogenous
environment, i.e., Oracle /DB2 over gateway, no distributed query optimization
is possible.  In other words, if we join a 1 row Oracle table, with a 48 million
row DB2 table, an attempt is made to bring the 48 million rows over the network
to perform the join on the Oracle side.  (ugh).. therefore, joins are being
coded, which take a lot of overhead. (pass one literal value over to DB2 at a
time)..  With that background information, I am wondering the following:

If I install UDB DB2 6.1 on the Unix, RS/6000 environment, and make this entire
interface a Unix DB2 6.1 to Mainframe DB2 situation, rather than an Oracle to
DB2 situation, will smarter decisions be made by query optimization techniques,
to perform joins on the appropriate side of the distributed system.  If I have a
one row table on Unix DB2 6.1, joining with a 48 million row table on the
mainframe DB2 side, will something in DB2 be clever enough to pull the one row
over to the other side, and do the join there?

I would be interested in any answer to this question, other ideas, further
reading ideas, etc.,   The main goal is of course to hold the read lock for 20
seconds or so, as oppossed to the 2 1/2 minutes it is using now.  I am hoping
with fast join distributed optimization techniques, the time this interface runs
can be minimized to the point where it doesn't disturb any other transactions.
Just a couple of other notes.. Isolation levels cannot be set from the Oracle
side over the transparent gateway as far as I know, and in addition, I don't
even know if that's relevant, because I don't know that much about DB2 isolation
levels.   Also, one other suggestion is that we place all of the Oracle
interface tables on the DB2 side, to accomplish much the same thing..  

Any insights are welcome,

Thank you..



Sun, 03 Aug 2003 12:45:49 GMT
 Distributed DB2 - Oracle/Oracle - Oracle/DB2 - DB2/DB2

Doug,

I will invite others to comment. My sense is that although I don't think we can
quantify it, DB2 has a reputation for industry-leading optimization technology.
There are developers who monitor this forum who should be able to comment in a
qualitative way as to how DB2's optimizer would deal with this specific situation.

Quote:

> Sorry for the strange subject line.  I am working on a system that has Oracle
> running on an RS/6000 environment, which must interface with DB/2 on an OS/390
> MVS environment (version 4.x).   There is an interface job running from oracle
> over the Oracle Transparent Gateway for DB2.   Unfortunately, it is reading one
> very important table for a little bit too long, causing a timeout on processes
> trying to write to the table.  We are trying to minimize the time the read lock
> is in existance.  Dirty reads and mirroring the table are not options (48
> million row partitioned table)..   We have noticed that in a heterogenous
> environment, i.e., Oracle /DB2 over gateway, no distributed query optimization
> is possible.  In other words, if we join a 1 row Oracle table, with a 48 million
> row DB2 table, an attempt is made to bring the 48 million rows over the network
> to perform the join on the Oracle side.  (ugh).. therefore, joins are being
> coded, which take a lot of overhead. (pass one literal value over to DB2 at a
> time)..  With that background information, I am wondering the following:

> If I install UDB DB2 6.1 on the Unix, RS/6000 environment, and make this entire
> interface a Unix DB2 6.1 to Mainframe DB2 situation, rather than an Oracle to
> DB2 situation, will smarter decisions be made by query optimization techniques,
> to perform joins on the appropriate side of the distributed system.  If I have a
> one row table on Unix DB2 6.1, joining with a 48 million row table on the
> mainframe DB2 side, will something in DB2 be clever enough to pull the one row
> over to the other side, and do the join there?

> I would be interested in any answer to this question, other ideas, further
> reading ideas, etc.,   The main goal is of course to hold the read lock for 20
> seconds or so, as oppossed to the 2 1/2 minutes it is using now.  I am hoping
> with fast join distributed optimization techniques, the time this interface runs
> can be minimized to the point where it doesn't disturb any other transactions.
> Just a couple of other notes.. Isolation levels cannot be set from the Oracle
> side over the transparent gateway as far as I know, and in addition, I don't
> even know if that's relevant, because I don't know that much about DB2 isolation
> levels.   Also, one other suggestion is that we place all of the Oracle
> interface tables on the DB2 side, to accomplish much the same thing..

> Any insights are welcome,

> Thank you..



Sun, 03 Aug 2003 22:58:59 GMT
 Distributed DB2 - Oracle/Oracle - Oracle/DB2 - DB2/DB2
Hi,

Datajoiner does distributed query processing which optimizes across
different DBMS
(i.e. your 48 Million row thing wouldn't happen). It allows full
upate/query between the involved DBMS.
Datajoiner knows about the capabilities of the involved DBMS, feeds this
information into DB2's optimizer and then reverse engineers queries to
pull or push data across for remote or local joins.

It also compensates for functional deficiencies of the involved DBMS's

Datajoiner is currently being merged into DB2. So far you can query, but
not yet update Oracle in DB2 V7.1.

Due to some companies licencing policies there are no published
benchmarks on just how good datajoiner is which is very, very sad.

A German-American car manufacturer did a study two year ago and found
differences in the order of magnitutes in performance between the
various gateways available on the market.

Cheers
Serge

--
Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2/udb/winos2unix/support



Mon, 04 Aug 2003 00:31:42 GMT
 Distributed DB2 - Oracle/Oracle - Oracle/DB2 - DB2/DB2

What you really want is DB2's federated database technology. We
definitely do global query optimization. In your particular case, I
would guess that we would indeed send the join to DB2/390. Also, you
don't need to have a separate gateway product.

Now to the specifics:

IBM has a product called DataJoiner which was build on the DB2 UDB
code base (DataJoiner runs on win32, and the usual Unix
platforms). You can use DataJoiner to connect to various different
data sources. Everything is accomplished using DDL. In this example
I'm connecting DB2 UDB Win32 to DB2 UDB on AIX. The idea is the same
though.

create wrapper DRDA library 'DRDA.DLL';
create server "OAK" type DB2/6000 version 7.1.0 wrapper "DRDA"
        AUTHID tpcw PASSWORD tpcw
        options ( collating_sequence 'Y',
                  comm_rate '50',
                  cpu_ratio '3',
                  dbname 'FOO',
                  fold_id 'L',
                  fold_pw 'L',
                  io_ratio '10',
                  node 'CACHEOAK',
                  pushdown 'Y',
                  varchar_no_trailing_blanks 'Y');

create user mapping for USER server "OAK"
             options ( remote_authid 'tpcw',
                       remote_password 'foobar');

create nickname "CUSTOMERNICK" FOR "OAK"."TPCW"."CUSTOMER";
create nickname "ORDERNICK" FOR "OAK"."TPCW"."ORDER";
create nickname "ORDERLINENICK" FOR "OAK"."TPCW"."ORDERLINE";

A "nickname" is the term for a table that exists in a foreign data
source. With the DataJoiner product, you can even do writes in remote
data sources (it supports federated 2-phase commit). DataJoiner
supports a whole bunch of remote data sources including (DB2/390,
Oracle, Informix, Sybase, MS SQL Server, IMS)

Starting DB2 UDB 7.1 (on Unix and Windows) we have started integrating
the DataJoiner technology into our core DB2 product. Eventually
(hopefully soon :-) all the DataJoiner functionality will be available
with DB2 UDB 7.1 itself.

In DB2 UDB 7.1, we support read-only access to remote DB2 and Oracle
data sources. In a future fixpack I think we will be adding more data
sources. Remote write with Federated 2PC should be available in a
future release.

Your scenario is remote read to a DB2/390, so I strongly recommend
that you go to DB2 UDB 7.1 on AIX. You can read more about federated
database technology in the DB2 UDB 7.1 documentation.

The DataJoiner technology is quite mature - it is used by thousands of
customers, for quite a few years now.

If you want more info, please ask here. If you want it from an
"official channel" please contact your friendly neighbourhood IBM
representative.

Quote:

> Sorry for the strange subject line.  I am working on a system that has Oracle
> running on an RS/6000 environment, which must interface with DB/2 on an OS/390
> MVS environment (version 4.x).   There is an interface job running from oracle
> over the Oracle Transparent Gateway for DB2.   Unfortunately, it is reading one
> very important table for a little bit too long, causing a timeout on processes
> trying to write to the table.  We are trying to minimize the time the read lock
> is in existance.  Dirty reads and mirroring the table are not options (48
> million row partitioned table)..   We have noticed that in a heterogenous
> environment, i.e., Oracle /DB2 over gateway, no distributed query optimization
> is possible.  In other words, if we join a 1 row Oracle table, with a 48 million
> row DB2 table, an attempt is made to bring the 48 million rows over the network
> to perform the join on the Oracle side.  (ugh).. therefore, joins are being
> coded, which take a lot of overhead. (pass one literal value over to DB2 at a
> time)..  With that background information, I am wondering the following:

> If I install UDB DB2 6.1 on the Unix, RS/6000 environment, and make this entire
> interface a Unix DB2 6.1 to Mainframe DB2 situation, rather than an Oracle to
> DB2 situation, will smarter decisions be made by query optimization techniques,
> to perform joins on the appropriate side of the distributed system.  If I have a
> one row table on Unix DB2 6.1, joining with a 48 million row table on the
> mainframe DB2 side, will something in DB2 be clever enough to pull the one row
> over to the other side, and do the join there?

> I would be interested in any answer to this question, other ideas, further
> reading ideas, etc.,   The main goal is of course to hold the read lock for 20
> seconds or so, as oppossed to the 2 1/2 minutes it is using now.  I am hoping
> with fast join distributed optimization techniques, the time this interface runs
> can be minimized to the point where it doesn't disturb any other transactions.
> Just a couple of other notes.. Isolation levels cannot be set from the Oracle
> side over the transparent gateway as far as I know, and in addition, I don't
> even know if that's relevant, because I don't know that much about DB2 isolation
> levels.   Also, one other suggestion is that we place all of the Oracle
> interface tables on the DB2 side, to accomplish much the same thing..  

> Any insights are welcome,

> Thank you..

--
Toodle-pip
Sailesh Krishnamurthy  
IBM Almaden Research Center
Ph: (408) 927-1913


Mon, 04 Aug 2003 00:41:39 GMT
 Distributed DB2 - Oracle/Oracle - Oracle/DB2 - DB2/DB2
I think you can update via Pass-Through functionalities.
Correct me if i'm wrong...

PM

Quote:

> Hi,

> Datajoiner does distributed query processing which optimizes across
> different DBMS
> (i.e. your 48 Million row thing wouldn't happen). It allows full
> upate/query between the involved DBMS.
> Datajoiner knows about the capabilities of the involved DBMS, feeds this
> information into DB2's optimizer and then reverse engineers queries to
> pull or push data across for remote or local joins.

> It also compensates for functional deficiencies of the involved DBMS's

> Datajoiner is currently being merged into DB2. So far you can query, but
> not yet update Oracle in DB2 V7.1.

> Due to some companies licencing policies there are no published
> benchmarks on just how good datajoiner is which is very, very sad.

> A German-American car manufacturer did a study two year ago and found
> differences in the order of magnitutes in performance between the
> various gateways available on the market.

> Cheers
> Serge

> --
> Visit the DB2 UDB and DB2 Connect Online Support site at:
> http://www.ibm.com/software/data/db2/udb/winos2unix/support



Fri, 08 Aug 2003 14:12:29 GMT
 Distributed DB2 - Oracle/Oracle - Oracle/DB2 - DB2/DB2
I don't know...

--
Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2/udb/winos2unix/support



Sat, 09 Aug 2003 00:31:14 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. IA-Cedar Rapids Area-271400--DBA Skills-ORACLE-DB2-DBA and Oracle or DBA and DB2

2. IA-Cedar Rapids Area-263612--DB2-Visual Basic-ORACLE-DB2,Visual Basic and Oracle

3. CT-Hartford Area-263601--ORACLE-DB2-Annuities, Oracle, DB2

4. Oracle DB2/AS400 Access Manager and Oracle DB2/AS400 Transparent Gateway and UNIX connectivity

5. Oracle to DB2 connectivity or DB2 to Oracle Connectivity

6. US-IL-Bloomington DBA, DB2 Datajoiner, DB2 EE, Oracle, Unix, NT (1123-OG1901)

7. 18727-MS-Jackson-DB2-COBOL-C++-MVS-ORACLE-Programmers(6) MVS COBOL DB2 CICS

8. 18727-MS-Jackson-DB2-COBOL-C++-MVS-ORACLE-Programmers(6) MVS COBOL DB2 CICS

9. 18727-MS-Jackson-DB2-COBOL-C++-MVS-ORACLE-Programmers(6) MVS COBOL DB2 CICS

10. 18727-MS-Jackson-DB2-COBOL-C++-MVS-ORACLE-Programmers(6) MVS COBOL DB2 CICS

11. 18727-MS-Jackson-DB2-COBOL-C++-MVS-ORACLE-Programmers(6) MVS COBOL DB2 CICS

12. 18578-NC-GREENSBORO-DB2-COBOL-ORACLE-UNIX-JCL--DB2 Programmer/Analyst


 
Powered by phpBB® Forum Software