1. 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,
2. Apollo - Any Good?
3. US-NY: ORACLE DBA >>>>>URGENT
4. Fetching the resultset from Stored Procedure
5. Trouble with SPROC and VIEW not working together.
6. DBASE file format ?