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

# 5
Using the Gateway

After the gateway is installed and configured, you can use the gateway to access Sybase data, pass Sybase commands from applications to the Sybase database, perform distributed queries, and copy data.

This chapter contains the following sections:

- Accessing Sybase Data Through the Gateway

- Oracle Database Server SQL Construct Processing

- Data Type Conversion

- Using Synonyms

- Using the Pass-Through Feature

- Executing Stored Procedures and Functions

- Performing Distributed Queries

- Copying Data from the Oracle Database Server to Sybase

- Copying Data from Sybase to the Oracle Database Server

- Monitoring Gateway Sessions

## Accessing Sybase Data Through the Gateway

To access Sybase data through the gateway, complete the following steps on the Oracle database server:

1. Log in to the Oracle database server.

2. Create a database link to the Sybase database, as in the following:

       SQL> CREATE DATABASE LINK SYBS
         2   CONNECT TO user_id IDENTIFIED BY password

         3   USING 'SYBSDATA'

   where:

   user_id | is the user ID used to establish a session in the Sybase database. This user ID must be a valid Sybase database user ID and be authorized to use any database object in the Sybase database that is referenced in the SQL commands. If user_id contains lowercase letters or non-alphanumeric characters, surround user_id with double quotation marks.
   password | is the password used to establish a session in the Sybase database. This must be a valid Sybase database password. If password contains lowercase letters or non-alphanumeric characters, surround password with double quotation marks.

   See Also: "Database Links" and "Creating Database Links" for more information.

3. Retrieve data from the Sybase database using one of the following methods:

   - If the CONNECT TO clause of the database link specifies ORACLE as the user ID, the following example retrieves data from the EMP table in the Sybase database using the name ORACLE as the Sybase database user:

         SQL> SELECT * FROM "EMP"@SYBS

   - The following SELECT statement retrieves the EMP table in the CORPDATA schema, using the name ORACLE as the Sybase database user:

         SQL> SELECT * FROM "CORPDATA"."EMP"@SYBS

   The ORACLE user must have the appropriate Sybase privileges to access the CORPDATA.EMP table.

4. Update data in the Sybase database using the UPDATE statement, as in the following example:

       SQL> UPDATE "EMP"@SYBS SET "SAL"="SAL"*1.10

## Oracle Database Server SQL Construct Processing

The gateway rewrites SQL statements when the statements need to be translated or post-processed.

For example, consider a program that requests the following from the Sybase database:

    SELECT "COL_A" FROM "test"@SYBS
    WHERE "COL_A" = INITCAP('jones');

The Sybase database does not recognize `INITCAP`, so the Oracle database server does a table scan of `test` and filters the results locally. The gateway rewrites the SELECT statement as follows:

    SELECT "COL_A" FROM "test"@SYBS

The results of the query are sent to the gateway and are filtered by the Oracle database server.

Consider the following UPDATE request:

    UPDATE "test"@SYBS WHERE "COL_A" = INITCAP('jones');

In this case, the Oracle database server and the gateway cannot compensate for the lack of support at the Sybase side, so an error is issued.

If you are performing operations on large amounts of data stored in the Sybase database, keep in mind that some functions require data to be moved to the integrating Oracle database server before processing can occur.

## Data Type Conversion

The gateway converts Sybase data types to Oracle data types as follows:

##### Table 5-1 Data Type Conversions

Sybase | Oracle | Comment
--- | --- | ---
BINARY | RAW |
BIT | NUMBER(3) |
CHAR | CHAR |
DATETIME | DATE | Sybase DATETIME does not support the fractional part of the data
DECIMAL | NUMBER(18) |
DOUBLE PRECISION | FLOAT(49) |
FLOAT | FLOAT(49) |
IMAGE | LONG RAW |
INT | NUMBER(10) | NUMBER range is -2,147,483,647 to 2,147,483,647
MONEY | NUMBER(p[,s]) |
NCHAR | Not supported |
NUMERIC | NUMBER(p[,s]) |
NVARCHAR | Not supported |
REAL | FLOAT(23) |
SMALLDATETIME | DATE | Sybase DATETIME does not support the fractional part of the data
SMALLINT | NUMBER(5) | NUMBER range is -32,767 to 32,767
SMALL MONEY | NUMBER(p[,s]) |
TEXT | LONG |
TINYINT | NUMBER(3) |
VARBINARY | RAW |
VARCHAR | VARCHAR2 |

## Using Synonyms

You can provide complete data location transparency and network transparency by using the synonym feature of the Oracle database server. When a synonym is defined, you do not have to know the underlying table or network protocol. A synonym can be public, which means that all Oracle users can refer to the synonym. A synonym can also be defined as private, which means every Oracle user must have a synonym defined to access the Sybase table.

The following statement creates a system wide synonym for the EMP table in the schema of user ORACLE in the Sybase database:

    SQL> CREATE PUBLIC SYNONYM EMP FOR "ORACLE"."EMP"@SYBS

See Also: Oracle8i Administrator's Guide for information about synonyms.

## Using the Pass-Through Feature

The gateway can pass Sybase commands or statements from the application to the Sybase database using the DBMS_HS_PASSTHROUGH package.

Use the DBMS_HS_PASSTHROUGH package in a PL/SQL block to specify the statement to be passed to the Sybase database, as follows:

    DECLARE
        num_rows INTEGER;
    BEGIN
        num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@SYBS('command');
    END;
    /

Where command cannot be one of the following:

- BEGIN TRANSACTION

- COMMIT

- ROLLBACK

- SAVE

- SHUTDOWN

- Sybase tool commands

The DBMS_HS_PASSTHROUGH package does support passing bind values and executing SELECT statements.

See Also: Oracle8i Distributed database Systems for more information about the DBMS_HS_PASSTHROUGH package and the different functions supported by this package.

## Executing Stored Procedures and Functions

Using the procedural feature, the gateway can execute stored procedures that are defined in the Sybase database. It is not necessary 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: Oracle8i Distributed Database Systems for more information about executing stored procedures.

Standard PL/SQL statements are used to execute a stored procedure. By default, all stored procedures and functions do not return a return value to the user. To enable return values, set the HS_FDS_PROC_IS_FUNC parameter in the initialization parameter file.

See Also: For information about editing the initialization parameter file, see "Task 2: Customize the Initialization Parameter File". For information about the HS_FDS_PROC_IS_FUNC parameter, see Appendix C, "Heterogeneous Services Initialization Parameters".

.

Note:: If you set the HS_FDS_PROC_IS_FUNC gateway initialization parameter, you must change the syntax of the procedure execute statement for all existing stored procedures.

In the following example, the employee name JOHN SMYTHE is passed to the Sybase stored procedure REVISE_SALARY. The stored procedure retrieves the salary value from the Sybase database to calculate a new yearly salary for JOHN SMYTHE. The revised salary returned in RESULT is used to update EMP in a table of an Oracle database:

    DECLARE

      INPUT VARCHAR2(15);

      RESULT NUMBER(8,2);

    BEGIN

      INPUT := 'JOHN SMYTHE';

      REVISE_SALARY@SYBS(INPUT,RESULT);

      UPDATE EMP SET SAL = RESULT WHERE ENAME =: INPUT;

    END;

    /

The procedural feature automatically converts non-Oracle data types to and from PL/SQL data types.

Stored procedures that return a result set are executed. However, any result sets are skipped and only output parameters and return values are returned.

## Performing Distributed Queries

Oracle Transparent Gateway technology enables the execution of distributed queries that join the Oracle database server and the Sybase database, and any other data store for which Oracle Corporation provides a gateway. These complex operations can be invisible to the users requesting the data.

### Example of a Distributed Query

The following example joins data between the Oracle database server, an IBM DB2 database, and the Sybase database:

    SQL> SELECT O.CUSTNAME, P.PROJNO, E.ENAME, SUM(E.RATE*P."HOURS")

             FROM ORDERS@DB2 O, EMP@ORACLE8 E, "PROJECTS"@SYBS P

             WHERE O.PROJNO = P."PROJNO"

             AND P."EMPNO" = E.EMPNO

             GROUP BY O.CUSTNAME, P."PROJNO", E.ENAME

Through a combination of views and synonyms, using the following SQL statements, the process of distributed queries is transparent to the user:

    SQL> CREATE SYNONYM ORDERS FOR ORDERS@DB2

    SQL> CREATE SYNONYM PROJECTS FOR "PROJECTS"@SYBS

    SQL> CREATE VIEW DETAILS (CUSTNAME,PROJNO,ENAME,SPEND)

          AS

          SELECT O.CUSTNAME, P."PROJNO", E.ENAME, SUM(E.RATE*P."HOURS")

          SPEND

          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

Use the following SQL statement to retrieve information from the data stores in one command:

    SQL> SELECT * FROM DETAILS;

The command retrieves the following table:

    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

When the gateway is configured as COMMIT_CONFIRM, it is always the commit point site when the Sybase database is updated by the transaction, regardless of the HS_COMMIT_POINT_STRENGTH setting. The Oracle database server commits the unit of work in the Sybase database after verifying that all Oracle databases in the transaction have successfully prepared the transaction.

See Also: Oracle8i Administrator's Guide and Oracle8i Distributed Database Systems for more information about the two-phase commit process.

Only one gateway can participate in an Oracle two-phase commit transaction. Two-phase commit transactions are recorded in the HS_TRANSACTION_LOG table that is created when configuring the gateway.

See Also: "Configuring for Two-Phase Commit".

### Distributed Sybase Transactions

Because the HS_TRANSACTION_LOG table is used to record the status of a gateway transaction, the table must reside in the database where the Sybase update takes place.

Updates to the HS_TRANSACTION_LOG table cannot be part of an Oracle distributed transaction.

See Also: Chapter 4, "Configuring the Gateway" for more information.

## Copying Data from the Oracle Database Server to Sybase

Use the SQL*Plus COPY command to copy data from the local database to the Sybase database. The syntax is as follows:

    COPY FROM username/password@db_name

    INSERT destination_table USING query

The following example selects all rows from the local Oracle EMP table, inserts them into the EMP table on the Sybase database, and commits the transaction:

    SQL> COPY FROM SCOTT/TIGER@ORACLE8 -

    > INSERT SCOTT.EMP@SYBS -

    > USING SELECT * FROM EMP

The COPY command supports APPEND, CREATE, INSERT, and REPLACE options. However, INSERT is the only option supported when copying to Sybase. The SQL*Plus COPY command does not support copying to tables with lowercase table names. Use the following PL/SQL syntax with lowercase table names:

    DECLARE
        v1 oracle_table.column1%TYPE;
        v2 oracle_table.column2%TYPE;
        v3 oracle_table.column3%TYPE;
        .
        .
        .
        CURSOR cursor_name IS SELECT * FROM oracle_table;
    BEGIN
        OPEN cursor_name;
        LOOP
           FETCH cursor_name INTO v1, v2, v3, ... ;
           EXIT WHEN cursor_name%NOTFOUND;
           INSERT INTO destination_table VALUES (v1, v2, v3, ...);
        END LOOP;

        CLOSE cursor_name;
    END;

    /

See Also: SQL*Plus User's Guide and Reference for more information about the COPY command.

The following Oracle SQL INSERT statement is not supported for copying data from the Oracle database server to Sybase:

    INSERT INTO table_name  SELECT column_list FROM table_name

For example, consider the following statement:

    SQL> INSERT INTO SYBS_TABLE SELECT * FROM MY_LOCAL_TABLE

The statement returns the following error message:

    ORA-2025:  All tables in the SQL statement must be at the remote database

## Copying Data from Sybase to the Oracle Database Server

The CREATE TABLE command lets you copy data from a Sybase database to the Oracle database server. To create a table on the local database and insert rows from the Sybase table, use the following syntax:

    CREATE TABLE table_name AS query

The following example creates the table EMP in the local Oracle database and inserts the rows from the EMP table of the Sybase database:

    SQL> CREATE TABLE EMP AS SELECT * FROM SCOTT."EMP"@SYBS

Alternatively, you can use the SQL*Plus COPY command to copy data from the Sybase database to the Oracle database server.

See Also: SQL*Plus User's Guide and Reference for more information about the COPY command.

## Monitoring Gateway Sessions

The Distributed Access Manager is used for monitoring distributed sessions that involve non-Oracle systems. The tool is distributed and installed with Oracle Enterprise Manager and runs as a standalone application.

Start the Distributed Access Manager as follows:

    oemapp damgr

Using Distributed Access Manager requires connecting to an instance of an Oracle database. Once connected, the tool displays all of the gateways currently registered with the server. For each active gateway, runtime information about the gateway is displayed. It contains the machine on which the gateway runs, its process ID, and the operating system user who started the gateway. The modules supported in the gateway are also listed.

In addition, data about the current sessions through this gateway are available. Sessions are tied to the gateway by the gateway's class and instance ID, which uniquely identify the gateway. The parent client session of the distributed session is described in terms of the client program, the user/schema that owns the session, the session ID, the status, and the terminal.

#### Figure 5-1 Distributed Access Manager View of a Session

Information about the database link that was used for establishing the distributed connection, the startup time, and the set of initialization parameters used for the session is also available.

All of the runtime information is derived from dynamically updated V$ tables. The Distributed Access Manager has a refresh capability available through the menu and toolbar that allows users to rerun queries if necessary and update the data. When the data is refreshed, the tool verifies that the set of registered agents remains the same. If it is not, the global view is updated.

See Also: Oracle Enterprise Manager Administrator's Guide and online help for more information about the Distributed Access Manager.

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