4
Distributed Environments

This chapter includes the following sections:

Distributed Environments

A distributed environment is chosen for various applications where:

The data is generated at various geographical locations and needs to be available locally most of the time.
The data and software processing is distributed to reduce the impact of any particular site or hardware failure.

Accessing Remote Databases in a Distributed Environment

When a relational database management system (RDBMS) allows data to be distributed while providing the user with a single logical view of data, it supports "location transparency". Location transparency eliminates the need to know the actual physical location of the data. Location transparency thus helps make the development of the application easier. Depending on the needs of the application, the database administrator (DBA) can hide the location of the relevant data.

To access a remote object, the local server must establish a connection with the remote server. Each server requires unique names for the remote objects. The methods used to establish the connection with the remote server, and the naming conventions for the remote objects, differ from database to database.

Oracle and Remote Objects

Oracle allows remote objects (such as tables, views, and procedures) throughout a distributed database to be referenced in SQL statements using global object names. In Oracle, the global name of a schema object comprises the name of the schema that contains the object, the object name, followed by an "at" sign (@), and a database name. For example, the following query selects information from the table named scott.emp in the SALES database that resides on a remote server:

SELECT * FROM
scott.emp@sales.division3.acme.com

A distributed database system can be configured so that each database within the system has a unique database name, thereby providing "effective" global object names.

Furthermore, by defining synonyms for remote object names, you can eliminate references to the name of the remote database. The synonym is an object in the local database that refers to a remote database object. Synonyms shift the responsibility of distributing data from the application developer to the DBA. Synonyms allow the DBA to move the objects as desired without impacting the application.

The synonym can be defined as follows:

CREATE PUBLIC SYNONYM emp FOR
scott.emp@sales.division3.acme.com;

Using this synonym, the SQL statement outlined above can be changed to the following:

SELECT * FROM emp;

MS SQL Server and Sybase and Remote Objects

MS SQL Server and Sybase require schema objects throughout a distributed database to be referenced in SQL statements by fully qualifying the object names. The complete name of a schema object has the following format:

server_name.database_name.object_owner_name.object_name

The server_name is the name of a remote server. The database_name is the name of a remote database on the remote server.

MS SQL Server and Sybase do not support the concept of synonyms or location transparency. In a distributed environment, objects cannot be moved around without impacting the application, as the developers must include the location of the object in the application code.

Most of the static queries tend to include the references to the remote server and remote database. Some applications maintain a user table to map the complete object names (including the remote server name and the database name) to dummy object names. The queries refer to these dummy object names. The translations are performed in real-time with the help of the map in the user table. This limitation precludes any common scheme of referring to remote objects that can work for Oracle, MS SQL Server, and Sybase.

The MS SQL Server or Sybase Omni SQL Gateway server allows location transparency, but this requires that the schema definitions of all the databases participating in the distribution must be available with the Omni SQL Gateway server.

Replication

Replication functionality in MS SQL Server 6.5 and Sybase has the following characteristics:

Unidirectional
Table-based, not transaction-based
No automatic conflict resolution (must be manual)
Heterogeneous replication through Open Database Connectivity (ODBC)

In addition to the characteristics listed above, MS SQL Server 7.0 replication provides heterogeneous replication through ODBC.

Oracle replication has richer replication functionality, which includes the following:

Bi-directional
Any database object can be replicated
Automatic resynchronization
Automatic conflict resolution
Heterogeneous replication provided through gateways

Since Oracle distributed environment and replication support is a superset of MS SQL Server and Sybase, conversion of distributed applications from MS SQL Server or Sybase to Oracle is feasible.

Application Development Tools

Several application development tools that are currently available use specific features of one of the various database servers; you may have to invest significant effort to port these products to other database servers. With critical applications, it is sometimes best to develop and maintain a different set of application development tools that work best with the underlying database, as ODBC support is not adequate in such cases.

The majority of MS SQL Server and Sybase applications are written using ODBC application programming interfaces (APIs) or Visual Basic. DB-Library is widely used to develop 3GL applications with MS SQL Server or Sybase as the backend.

Since Oracle provides ODBC connectivity, it is possible to convert ODBC-based MS SQL Server or Sybase applications to work with an Oracle backend.

If a Visual Basic application is written with ODBC as the connection protocol to access MS SQL Server or Sybase, it is possible to modify and fix the Visual Basic application to work with an Oracle backend.

Many Visual Basic applications use VB-SQL which is DB-Library for Visual Basic. VB-SQL allows Visual Basic programs to access MS SQL Server or Sybase natively (as opposed to using ODBC). Such applications can also be converted to work with an Oracle backend, if you replace the VB-SQL database access routines with Oracle Objects for OLE.

Oracle provides a call interface knows as Oracle Call Interface (OCI), which is functionally equivalent to the DB-Library API. Conversion of DB-Library applications to OCI applications is feasible.

4 Distributed Environments