Users sometimes need to query or manipulate data that resides in multiple Oracle databases or in a mixture of Oracle and non-Oracle databases. This section describes a number of techniques and architectures you can use to enable these capabilities in a distributed environment.
For many years, Oracle has offered access to distributed data residing on multiple Oracle database servers on multiple systems or nodes. Users don't need to know the location of the data in distributed databases. Data is accessed by table name, physical location, network protocol, and operating system, in a manner transparent to users. A distributed database residing on multiple database servers can appear to users to be a single logical database.
Developers can create connections between individual databases by creating database links in SQL. These connections form a distributed database. For example, the statement:
CREATE PUBLIC DATABASE LINK employees.northpole.bigtoyco.com
creates a path to a remote database of that name containing Bigtoyco's North Pole employees. Any application or user attached to a local employees database can access the remote North Pole database by using the global access name (employees.northpole.bigtoyco.com) in SQL queries, inserts, updates, deletions, and other statements. Oracle Net (previously known as Net8 or SQL*Net) handles the interaction with any network protocols used to communicate with the remote database.
Let's look briefly at how queries and updates issued for distributed Oracle databases differ from those issued for a single Oracle database. When using distributed data in a query, your primary concern is to properly optimize the retrieval of data for a query. Queries in a single Oracle database are optimized for performance, most frequently using the cost-based optimizer, as discussed in Chapter 4. Oracle7 added global cost-based optimization for the improvement of query performance across distributed databases as well. For example, the cost-based optimizer considers indexes on remote databases when choosing a plan, whereas the rule-based optimizer does not. The cost-based optimizer also considers statistics on remote databases. Improvements to the Oracle8i optimizer included optimizing for join and set operations to be performed on the nodes offering the best performance and also minimizing the amount of data sent between systems. For these reasons, the cost-based optimizer is more frequently recommended and used for distributed databases.
When a user wants to write data back to a distributed database, the issue becomes a bit more complicated. As we've mentioned before, a transaction is an atomic logical unit of work that typically contains one or more SQL statements. These statements write data to a database and must either be committed or rolled back as a unit. Distributed transactions can take place across multiple database servers. When distributed transactions are committed via the SQL COMMIT statement, Oracle uses a two-phase commit protocol to ensure transaction integrity and consistency across multiple systems. This protocol is further described in Section 12.2 of this chapter.
Oracle's Transparent Gateways (illustrated in Figure 12-1) are Oracle software products that provide users with access to non-Oracle databases via Oracle SQL. Oracle SQL is automatically translated into the SQL of the target database, allowing applications developed for Oracle to be used against non-Oracle databases. Native SQL of the target database can also be transmitted directly. Oracle datatypes such as NUMBER, CHAR, and DATE are converted into the datatypes of the target. Oracle data dictionary views are provided for target data store objects. As with Oracle databases, heterogeneous databases can be linked to Oracle through database links to create a distributed database. The gateways can be deployed in a two-tier architecture in the Oracle database or in a middle tier (Oracle Application Server).
There are four basic types of database connectivity provided:
Open Database Connectivity
Generic ODBC and OLE DB interfaces are free and are bundled with the Oracle database. Open Systems Gateways provide access to Informix, Microsoft SQL Server, Sybase, and other databases on Unix and Windows platforms. These interfaces and gateways leverage Heterogeneous Services included in the Oracle database, which determine optimal SQL strategies for the remote site. Additionally, Oracle's OLAP Option for Oracle Database 10g provides OLE DB for OLAP (ODBO) enabling access from a variety of analysis tools.
Transparent Gateways
Transparent Gateways exist for dozens of non-Oracle data stores. Mainframe Integration Gateways provide access to DB2 on mainframes. Enterprise Integration Gateways provide access to IBM AS/400 and via IBM Distributed Relational Database Architecture (DRDA) connections. Finally, Oracle offers the EDA/SQL Gateways for a number of other sources. Transparent Gateways performance improved in Oracle8 by moving Heterogeneous Services from the Transparent Gateways layer into the database kernel. Performance was further improved in the Oracle8i release with the introduction of multi-threading for these services and in Oracle9i with multi-threaded agent support. Oracle Database 10g adds support for remote functions in non-Oracle databases embedded in SELECT statements.
Procedural Gateways
Procedural Gateways implement remote procedure calls (RPCs) to applications built on non-Oracle data stores. The Gateway for APPC, the standard IBM protocol for RPCs, is used when Oracle applications need procedural access to applications built on CICS, DB2, IMS, VSAM, and other data stores on the mainframe and applications that use SNA LU6.2 to communicate to the mainframe. The Oracle Procedural Gateway for IBM MQSeries allows Oracle-based applications to exchange messages with applications that communicate via MQSeries message queues. Both are included with the Oracle Enterprise Integration Gateways.
Access Manager
An Access Manager provides access to Oracle from non-Oracle based applications. The Oracle Access Manager for AS/400 resides on the AS/400 and provides AS/400 applications written in RPG, C, or COBOL access to Oracle running on any platform. You can access Oracle from these applications through ANSI-standard SQL or through Oracle DML or DDL. Because PL/SQL is also supported, AS/400 applications can call Oracle stored procedures. TCP/IP and LU6.2 are supported for connectivity (via Oracle Net/Net8). The Oracle Access Manager for AS/400 is included with the Oracle Enterprise Integration Gateways.