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 thePREPARE TRANSACTION
statement. ThePREPARE TRANSACTION
statement ensures that all participating databases are prepared toCOMMIT
or toROLLBACK
a specific unit of work. - Distributed DRDA Transactions
Because theHS_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.
Parent topic: Using the Oracle Database Gateway for DRDA
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.
Parent topic: Performing Distributed Queries
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 theHS_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.
Parent topic: Performing Distributed Queries