Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

12.1 Accessing Multiple Databases as a Single Entity

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.

12.1.1 Distributed Data Access Across Multiple Oracle Databases

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.

12.1.2 Access to and from Non-Oracle Databases

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

Figure 12-1. Typical configuration and use of Transparent Gateways

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.