Release 8.1.6 for Windows NT
Part Number A80982-01
 Library |
 Contents |
 Index |
| Oracle Transparent Gateway Sybase Administrator's Guide Release 8.1.6 for Windows NT Part Number A80982-01 |
|
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:
To access Sybase data through the gateway, complete the following steps on the Oracle database server:
SQL> CREATE DATABASE LINK SYBS 2 CONNECT TO user_id IDENTIFIED BY password
3 USING 'SYBSDATA'
where:
SQL> SELECT * FROM "EMP"@SYBS
SQL> SELECT * FROM "CORPDATA"."EMP"@SYBS
The ORACLE user must have the appropriate Sybase privileges to access the CORPDATA.EMP table.
SQL> UPDATE "EMP"@SYBS SET "SAL"="SAL"*1.10
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.
The gateway converts Sybase data types to Oracle data types as follows:
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
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:
The DBMS_HS_PASSTHROUGH package does support passing bind values and executing SELECT statements.
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.
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". |
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.
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.
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
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.
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.
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.
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;
/
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
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.
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.
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.
|
 Copyright © 2001 Oracle Corporation. All Rights Reserved. |
|