5
Using the Gateway

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

This chapter contains the following sections:

• Accessing Rdb Data Through the Gateway

• Oracle Database Server SQL Construct Processing

• Data Type Conversion

• Using Synonyms

• Using the Pass-Through Feature

• Performing Distributed Queries

• Copying Data from the Oracle Database Server to Rdb

• Copying Data from Rdb to the Oracle Database Server

Accessing Rdb Data Through the Gateway

To access Rdb 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 Rdb database, as in the following:

   SQL> CREATE DATABASE LINK TG4RDB
     2      USING 'tg4rdb'
   
   
   

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

3. Retrieve data from the Rdb database, as in the following example:

   SQL> SELECT * FROM EMP@TG4RDB
   
   

   SQL> SELECT * FROM EMP@TG4RDB
   
   

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

   SQL> UPDATE EMP@TG4RDB 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 Rdb database:

SELECT COL_A FROM TEST@TG4RDB
WHERE COL_A = INITCAP('jones');

The Rdb 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@TG4RDB

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@TG4RDB SET COL_A = 'SMITH'
WHERE COL_A = INITCAP('jones');

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

If you are performing operations on large amounts of data stored in the Rdb 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 Rdb data types to Oracle data types as follows:

Table 5-1  Data Type Conversions

Rdb	Oracle	Comment
BIGINT	NUMBER(19)
BIGINT(s)	NUMBER(19,s)
CHAR	CHAR
DATE VMS	DATE
DECIMAL	NUMBER
DOUBLE PRECISION	FLOAT(49)
FLOAT	FLOAT(49)
INTEGER	NUMBER(10)	NUMBER range is -2,147,483,647 to 2,147,483,647
INTEGER(s)	NUMBER(10,s)
LONG VARCHAR	LONG
NUMERIC	NUMBER
REAL	FLOAT(23)
SMALLINT	NUMBER(5)	NUMBER range is -32,767 to 32,767
SMALLINT(s)	NUMBER(5,s)
TIMESTAMP	DATE
TINYINT	NUMBER(3)
TINYINT(s)	NUMBER(3,s)
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 Rdb table.

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

SQL> CREATE PUBLIC SYNONYM EMP FOR EMP@TG4RDB

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

Using the Pass-Through Feature

The gateway can pass Rdb commands or statements from the application to the Rdb 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 Rdb database, as follows:

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

Where command cannot be one of the following:

• Rdb tool commands

• Any transaction control statement (such as COMMIT and ROLLBACK)

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.

Performing Distributed Queries

Oracle Transparent Gateway technology enables the execution of distributed queries that join the Oracle database server and the Rdb 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 Rdb database:

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

         FROM ORDERS@DB2 O, EMP@ORACLE8 E, PROJECTS@TG4RDB 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@TG4RDB

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 Rdb 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 Rdb 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 Rdb 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 Rdb 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 Rdb

Use the SQL*Plus COPY command to copy data from the local database to the Rdb 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 Rdb database, and commits the transaction:

SQL> COPY FROM SCOTT/TIGER@ORACLE8 -

> INSERT EMP@TG4RDB -

> USING SELECT * FROM EMP

The COPY command supports APPEND, CREATE, INSERT, and REPLACE options. However, INSERT is the only option supported when copying to Rdb. 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 Rdb:

INSERT INTO table_name  SELECT column_list FROM table_name

For example, consider the following statement:

SQL> INSERT INTO TG4RDB_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 Rdb to the Oracle Database Server

The CREATE TABLE command lets you copy data from the Rdb database to the Oracle database server. To create a table on the local database and insert rows from the Rdb 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 Rdb database:

SQL> CREATE TABLE EMP AS SELECT * FROM EMP@TG4RDB

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

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