3.6 Performing Distributed Queries

The Oracle Database Gateway technology enables the execution of distributed queries that join data in an Oracle database and in DRDA servers and data from any other data store for which Oracle provides a gateway.

These complex operations can be completely transparent to the users requesting the data.

The following example joins data between an Oracle database, DB2 UDB for z/OS, and a DRDA server:

SELECT o.custname, p.projno, e.ename, sum(e.rate*p.hours)
FROM orders@DB2 o, EMP@ORACLE7 e, projects@DRDA p
WHERE o.projno = p.projno 
AND p.empno = e.empno
GROUP BY o.custname, p.projno, e.ename

A combination of views and synonyms, using the following SQL statements, keeps the process of distributed queries transparent to the user:

CREATE SYNONYM orders for orders@DB2;
CREATE SYNONYM PROJECTS for PROJECTS@DRDA;
CREATE VIEW details (custname,projno,ename,spend)
AS 
SELECT o.custname, p.projno, e.ename, sum(e.rate*p.hours)
FROM orders o, EMP e, projects p
WHERE o.projno = p.projno
AND p.empno = e.empno
GROUP BY o.custname, p.projno, e.ename

The following SQL statement retrieves information from these three data stores in one command:

SELECT * FROM DETAILS;

The results of this command are:

CUSTNAME   PROJNO     ENAME      SPEND
---------  ---------  ---------  ---------
ABC Co.    1         Jones      400
ABC Co.    1         Smith      180
XYZ Inc.   2         Jones      400
XYZ Inc.   2         Smith      180

• Two-Phase Commit Processing
  To fully participate in a two-phase commit transaction, a server must support the PREPARE TRANSACTION statement. The PREPARE TRANSACTION statement ensures that all participating databases are prepared to COMMIT or to ROLLBACK a specific unit of work.
• Distributed DRDA Transactions
  Because the HS_TRANSACTION_LOG table is used to record the status of a gateway transaction, this table must be in at the database where the DRDA update takes place.

3.6.1 Two-Phase Commit Processing

To fully participate in a two-phase commit transaction, a server must support the PREPARE TRANSACTION statement. The PREPARE TRANSACTION statement ensures that all participating databases are prepared to COMMIT or to ROLLBACK a specific unit of work.

Oracle database supports the PREPARE TRANSACTION statement. Any number of Oracle database can participate in a distributed two-phase commit transaction. The PREPARE TRANSACTION statement is performed automatically when a COMMIT is issued explicitly by an application or implicitly at the normal end of the application.

The gateway does not support the PREPARE TRANSACTION statement. This limits the two-phase commit protocol when the gateway participates in a distributed transaction. The gateway becomes the commit focal point site of a distributed transaction. The gateway is configured as commit/confirm, so it is always the commit point site, regardless of the commit point strength setting. The gateway commits the unit of work after verifying that all Oracle databases in the transaction have successfully committed their work. The gateway must coordinate the distributed transaction so that only one gateway instance can participate in a two-phase commit transaction.

Two-phase commit transactions are recorded in the HS_TRANSACTION_LOG table (see the initialization parameter HS_FDS_TRANSACTION_LOG), which is created during installation. This table is created when the o2pc.sql script is run. The owner of this table also owns the package. Refer to "DRDA Gateway Package Binding Considerations" on Oracle Database Gateway Installation and Configuration Guide for IBM AIX on POWER Systems (64-Bit), Linux x86-64, Oracle Solaris on SPARC (64-Bit), Oracle Solaris on x86-64 (64-Bit) and HP-UX Itanium or Oracle Database Gateway Installation and Configuration Guide for Microsoft Windows, depending on your platform, for more information.

3.6.2 Distributed DRDA Transactions

Because the HS_TRANSACTION_LOG table is used to record the status of a gateway transaction, this table must be in at the database where the DRDA update takes place.

Therefore, all updates that take place over the gateway must be local to the IBM database.

Note:

• Updates to the HS_TRANSACTION_LOG table cannot be part of an IBM distributed transaction.
• The default commit mode on OS400 V5R1 and later is READ UNCOMMITTED (*CHG) and this requires files to be journaled. Hence, the object specified by the HS_TRANSACTION_LOG initialization parameter must be journaled.

For additional information about the two-phase commit process, refer to Oracle Database Heterogeneous Connectivity User's Guide.