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.
> 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
by