Oracle Transparent Gateway Sybase Administrator's Guide Release 8.1.6 for Windows NT Part Number A80982-01 |
| Library | Contents | Index

# 2
Introduction

Oracle Transparent Gateway for Sybase allows Oracle client applications to access Sybase data through Structured Query Language (SQL). The gateway, with the Oracle database server, creates the appearance that all data resides on a local Oracle database server, even though data might be widely distributed. If data is moved from the Sybase database to an Oracle database, no changes in client application design or function are needed, because the gateway handles all differences in data types or SQL functions between the application and database.

This chapter contains the following sections:

- Oracle Heterogeneous Services

- Oracle Transparent Gateway for Sybase

- Gateway Architecture

- Gateway Process Flow

- Additional Features

## Oracle Heterogeneous Services

The generic core of the Oracle Transparent Gateway for Sybase is incorporated into the database as the Heterogeneous Services facility. Heterogeneous Services (HS) extend the Oracle SQL engine to recognize the SQL and procedural capabilities of the remote non-Oracle system and the mappings required to obtain necessary data dictionary information. Additionally, since Heterogeneous Services is integrated with the Oracle database server, the transaction coordination of the server maintains the transaction coordination between Oracle and the remote non-Oracle system. For example, Heterogeneous Services provide the two-phase commit protocol to ensure distributed transaction integrity, even for non-Oracle systems that do not natively support two-phase commit.

Oracle Transparent Gateway for Sybase, together with the Heterogeneous Services component that is integrated in the Oracle8i server, provide information for and connectivity to Sybase.

See Also: Oracle8i Distributed Database Systems.

## Oracle Transparent Gateway for Sybase

Using Oracle SQL, Oracle client applications can access Sybase data as if the data were stored in an Oracle table. Data residing in Oracle and Sybase databases can be accessed by a single SQL statement, performing heterogeneous joins and subselects. This means you can develop one set of portable applications to use against Oracle and Sybase databases. You can continue to develop new information systems without losing your investment in existing data and applications.

Transactions updating Oracle and Sybase databases are automatically protected by the Oracle two-phase commit feature. Use of synonyms is another Oracle feature. By setting up synonyms in the Oracle database server that point to database links to Sybase tables, the physical location of the data is transparent to the client application. This allows future migration of data from Sybase to Oracle to be transparent to the client applications.

The gateway requires only the Oracle database server and Net8. All other Oracle products are optional. However, using other Oracle products with the gateway can greatly extend the gateway's capabilities.

## Gateway Architecture

The gateway is invoked by the listener. The gateway is not multi-threaded and cannot support shared database links. Each gateway session spawns a separate gateway process, and connections cannot be shared.

The gateway resides on the Windows NT machine with the Sybase database or Sybase client (in which case the Sybase database can reside on a separate machine). The Oracle database server can reside on the same machine as the gateway or on another machine.

Note: The non-Oracle system shown in Figure 2-1 and Figure 2-2 represents Sybase.

The gateway interacts with the Oracle database server to interface between client applications and Sybase, as shown in Figure 2-1.

#### Figure 2-1 Gateway Processing

- Client applications, such as Developer, connect to the Oracle database server by using Net8.

- The Oracle database server, which includes Heterogeneous Services and the database itself, resides on a single system. This Oracle database server also stores definitions of database links for Sybase.

- The gateway to Sybase and the Sybase database server itself, which includes the Sybase database, reside on a second system. The Oracle database server on the first system uses Net8 to connect directly to the Sybase gateway on the second system.

- On the second system, the Sybase gateway accesses the Sybase database server by using whatever networking software the target supports.

The Oracle database server and the gateway work together to present the appearance of a single Oracle database to the client. All data accessed by the client appears to reside in a single Oracle database. The client application sends a request to the Oracle database server, and the Oracle database server sends the request to the gateway. For the first transaction in a session, the gateway logs into Sybase using a username and password that is valid in the Sybase database. The gateway converts the SQL statement to a native Sybase statement, and Sybase performs the request. The gateway converts the retrieved data to a format compatible with the Oracle database server and returns the results to the Oracle database server, which returns the results to the client application.

## Gateway Process Flow

Figure 2-2 shows a typical gateway process flow. The steps explain the sequence of events that occurs when a client application queries the Sybase database through the gateway.

#### Figure 2-2 Gateway Process Flow

1. The client application sends a query over Net8 to the Oracle database server.

2. The Oracle database server sends the query over to the gateway, again using Net8.

3. For the first transaction in a session, the gateway logs into Sybase using a username and password that is valid in the Sybase database.

4. The gateway converts the Oracle SQL statement into an SQL statement understood by Sybase.

5. The gateway retrieves data using Sybase SQL statements.

6. The gateway converts retrieved data into a format compatible with the Oracle database server.

7. The gateway returns query results to the Oracle database server, again using Net8.

8. The Oracle database server passes the query results to the client application by using Net8. The database link remains open until the gateway session is finished or the database link is explicitly closed.

## Additional Features

This section describes the following additional features provided by the Oracle Transparent Gateway for Sybase:

- Remote Data Access

- Elimination of Unnecessary Data Duplication

- Heterogeneous Database Integration

- Application Development and End User Tools

- Two-Phase Commit and Multi-Site Transactions

- Query Optimization

- Error Mapping and Logging

- Stored Procedure Execution

- Pass-Through Feature

### Remote Data Access

Applications can take advantage of Oracle client-server capability to connect to a remote server using Net8. The server can then connect to the gateway using a database link. You have more flexibility in locating your data because the Oracle architecture enables network connections between each of the components.

With remote access, you can move application development onto cost-efficient workstations or microcomputers. Without remote access, you are limited to the data available in the local environment. With remote access, your data sources are virtually unlimited. Remote access also enables you to choose the best environment for your users. For example, data might be located on a platform that supports only character-mode interfaces, but users can access the data from desktop platforms that support graphical user interfaces.

### Elimination of Unnecessary Data Duplication

The gateway gives applications direct access to Sybase data, so the need for uploading and downloading large quantities of data to other locations is eliminated. You instead access the data where it is, when you want it, without having to move the data between machines and risk unsynchronized and inconsistent data. Avoiding data duplication reduces the disk storage requirements over all your systems.

### Heterogeneous Database Integration

The Oracle database server can accept a SQL statement that queries data stored in several different databases. The Oracle database server with heterogeneous services processes the SQL statement and passes the appropriate SQL directly to other Oracle databases and through gateways to non-Oracle databases. The Oracle database server then combines the results and returns them to the client. This enables a query to be processed that spans Sybase, other databases, and local and remote Oracle data.

### Application Development and End User Tools

Through the gateway, Oracle Corporation extends the range of application development and user tools you can use to access the databases. These tools increase application development and user productivity by reducing prototype, development, and maintenance time. Current Oracle users do not have to learn a new set of tools to access data stored in Sybase databases. Instead, they can access Oracle and Sybase data with one set of tools. These tools can run on remote machines connected through Net8 to the Oracle database server.

### Two-Phase Commit and Multi-Site Transactions

In a distributed database system, the network might fail during a distributed transaction. The Oracle transaction model uses a two-phase commit protocol to protect the databases during the period of committing data at sites participating in a distributed transaction. This feature ensures that all database servers participating in the transaction commit or roll back the transaction statements. The gateway supports this two-phase commit protocol. Only one Sybase database is allowed for each update transaction.

### Query Optimization

Whenever possible, the Oracle database server passes the entire query to the non-Oracle system to utilize the indexes and statistics of the non-Oracle system tables.

When a query that involves multiple databases is processed, the Oracle database server passes optimized statements to the remote servers and gateways involved in the query to minimize the amount of data returned across the network.

See Also: Oracle8i Designing and Tuning for Performance and Oracle8i Distributed Database Systems for information about collocated inline views and joins.

### Error Mapping and Logging

The gateway provides error mapping. It maps the Sybase error to an Oracle database server error message and adds all of the relevant error messages generated by Sybase. You can route messages to the client application, an operator console, an error log, or any combination of these destinations as needed. Error mapping provides database transparency for applications.

### Stored Procedure Execution

Using the procedural feature, the gateway can execute stored procedures defined in Sybase databases. There is no requirement to relink the gateway or define the procedure to the gateway, but the procedure's access privileges must permit access by the gateway.

See Also: Executing Stored Procedures and Functions.

### Pass-Through Feature

Commands and statements specific to the Sybase database can be passed through the gateway for execution by Sybase.

See Also: Using the Pass-Through Feature.

| Copyright © 2001 Oracle Corporation. All Rights Reserved. |
| Library | Contents | Index