Oracle9i Heterogeneous Connectivity Administrator's Guide Release 1 (9.0.1) Part Number A88789_01 |
|
This chapter explains how to use Oracle Transparent Gateways.
This chapter contains the following sections:
This section explains the generic steps to configure access to a non-Oracle system.
.The steps for setting up access to a non-Oracle system are:
To install the data dictionary tables and views for Heterogeneous Services, you must run a script that creates all the Heterogeneous Services data dictionary tables, views, and packages. On most systems the script is called caths.sql
and resides in $ORACLE_HOME/rdbms/admin
.
To initiate a connection to the non-Oracle system, the Oracle9i server starts an agent process through the Oracle Net listener. For the Oracle9i server to be able to connect to the agent, you must:
(HS=OK)
clause to make sure the connection uses Oracle9i Heterogeneous Services.
listener.ora
file so that the listener can start Heterogeneous Services agents, and then restart the listener.
The following is a sample entry for the service name in the tnsnames.ora
file:
Sybase_sales= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp) (HOST=dlsun206) (PORT=1521)) (CONNECT_DATA = (SID=SalesDB)) (Heterogeneous Services = OK))
The description of this service name is defined in tnsnames.ora
, the Oracle Names server, or in third-party name servers using the Oracle naming adapter.
The following is a sample entry for the listener in listener.ora
:
LISTENER = (ADDRESS_LIST = (ADDRESS= (PROTOCOL=tcp) (HOST = dlsun206) (PORT = 1521) ) ) ... SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME=SalesDB) (ORACLE_HOME=/home/oracle/megabase/8.1.3) (PROGRAM=tg4mb80) ) )
The value associated with PROGRAM
keyword defines the name of the agent executable. The agent executable must reside in the $ORACLE_HOME/bin
directory. Typically, you use SID_NAME
to define the initialization parameter file for the agent.
To create a database link to the non-Oracle system, use the CREATE DATABASE LINK
statement. The service name that is used in the USING
clause of the CREATE DATABASE LINK
command is the Oracle Net service name.
For example, to create a database link to the SALES
database on Sybase, enter:
CREATE DATABASE LINK sales USING `Sybase_sales';
To test the connection to the non-Oracle system, use the database link in a SQL or PL/SQL statement. If the non-Oracle system is a SQL-based database, you can execute a SELECT
statement from an existing table or view using the database link. For example, issue:
SELECT * FROM product@sales WHERE product_name like '%pencil%';
When you try to access the non-Oracle system for the first time, the Heterogeneous Services agent uploads information into the Heterogeneous Services data dictionary. The uploaded information includes:
As mentioned in "Configuring Heterogeneous Services" on page 2-5, the user can configure the gateway using initialization parameters. This is done by creating an initialization file and setting the desired parameters in this file
Heterogeneous Services parameters are distinct from Oracle database server initialization parameters. Heterogeneous Services initialization parameters are set in the Heterogeneous Services initialization file and not in the Oracle init.ora
file. There is a Heterogeneous Services initialization file for each gateway instance. The name of the file is init
sid.ora
, where sid is the Oracle system identifier used for the gateway. In the case of generic connectivity, the file is located in the directory $ORACLE_HOME/hs/admin
and in the case of transparent gateways it is located in the directory $ORACLE_HOME/product_name/admin
where product_name is the name of the product. So, the Sybase gateway initialization file is located in the directory $ORACLE_HOME/tg4sybs/admin
.
The syntax of the initialization file is as follows. The file contains a list of initialization parameter settings each of which should be on a separate line. The syntax to set an initialization parameter is:
[set] [private] parameter = value
The set and private keywords are optional. If the set keyword is present then the variable will also be set in the environment. If the private keyword is present, the parameter will not be uploaded to the server. In general, it recommended that this keyword not be used - unless the initialization parameter value contains sensitive information (like a password) that should not be sent over the network from gateway to Oracle server.
Another initialization file can be included in an Heterogeneous Services initialization file by using the ifile directive. The syntax for this is
ifile = pathname for file to be included
In the initialization parameter syntax, all keywords (SET
, PRIVATE
and IFILE
) are case insensitive. Initialization parameter names and values are case sensitive. Most initialization parameters names will be uppercase. When there are any exceptions to this rule, we will explicitly point them out.
Gateway initialization parameters can be divided into two groups. One is a set of generic initialization parameters that are common to all gateways and the other is a set of initialization parameters that are specific to individual gateways. The list of generic initialization parameters is given below. Please refer individual gateway documentation for the list of initialization parameters specific to that gateway.
HS_COMMIT_POINT_STRENGTH
HS_DB_DOMAIN
HS_DB_INTERNAL_NAME
HS_DB_NAME
HS_DESCRIBE_CACHE_HWM
HS_FDS_CONNECT_INFO
HS_FDS_SHAREABLE_NAME
HS_FDS_TRACE_LEVEL
HS_FDS_TRACE_FILE_NAME
HS_LANGUAGE
HS_LONG_PIECE_TRANSFER_SIZE
HS_NLS_DATE_FORMAT
HS_NLS_DATE_LANGUAGE
HS_NLS_NCHAR
HS_NLS_TIMESTAMP_FORMAT
HS_NLS_TIMESTAMP_TZ_FORMAT
HS_OPEN_CURSORS
HS_ROWID_CACHE_SIZE
HS_RPC_FETCH_REBLOCKING
HS_RPC_FETCH_SIZE
HS_TIME_ZONE
Do not use the private keyword when setting any of these parameters. Doing that would cause the parameter not to be uploaded to the server and could cause errors in SQL processing. None of these parameters need be set in the environment, so the set keyword need not be used either.
When an application fetches data from a non-Oracle system using Heterogeneous Services, data is transferred:
Oracle allows you to optimize all three data transfers, as illustrated in Figure 4-1.
This section contains the following topics:
You can optimize data transfers between your application and the Oracle9i server by using array fetches. See your application development tool documentation for information about array fetching and how to specify the amount of data to be sent per network round trip.
When Oracle retrieves data from a non-Oracle system, the Heterogeneous Services initialization parameter HS_RPC_FETCH_SIZE
defines the number of bytes sent per fetch between the agent and the Oracle9i server. The agent fetches data from the non-Oracle system until one of the following occurs:
The initialization parameter HS_FDS_FETCH_ROWS
determines the number of rows to be retrieved from a non-Oracle system. Note that the array fetch must be supported by the agent. See your agent-specific documentation to ensure that your agent supports array fetching.
By default, an agent fetches data from the non-Oracle system until it has enough data retrieved to send back to the server. That is, it keeps going until the number of bytes fetched from the non-Oracle system is equal to or higher than the value of HS_RPC_FETCH_SIZE
. In other words, the agent reblocks the data between the agent and the Oracle database server in sizes defined by the value of HS_RPC_FETCH_SIZE
.
When the non-Oracle system supports array fetches, you can immediately send the data fetched from the non-Oracle system by the array fetch to the Oracle database server without waiting until the exact value of HS_RPC_FETCH_SIZE
is reached. That is, you can stream the data from the non-Oracle system to the Oracle database server and disable reblocking by setting the value of initialization parameter HS_RPC_FETCH_REBLOCKING
to OFF
.
For example, assume that you set HS_RPC_FETCH_SIZE
to 64 kilobytes (KB) and HS_FDS_FETCH_ROWS
to 100 rows. Assume that each row is approximately 600 bytes in size, so that the 100 rows are approximately 60 KB. When HS_RPC_FETCH_REBLOCKING
is set to ON
, the agent starts fetching 100 rows from the non-Oracle system.
Because there is only 60 KB of data in the agent, the agent does not send the data back to the Oracle database server. Instead, the agent fetches the next 100 rows from the non-Oracle system. Because there is now 120 KB of data in the agent, the first 64 KB can be sent back to the Oracle database server.
Now there is 56 KB of data left in the agent. The agent fetches another 100 rows from the non-Oracle system before sending the next 64 KB of data to the Oracle database server. By setting the initialization parameter HS_RPC_FETCH_REBLOCKING
to OFF
, the first 100 rows are immediately sent back to the Oracle9i server.
Registration is an operation through which Oracle stores information about an agent in the data dictionary. Agents do not have to be registered. If an agent is not registered, Oracle stores information about the agent in memory instead of in the data dictionary; when a session involving an agent terminates, this information ceases to be available.
Self-registration is an operation in which a database administrator sets an initialization parameter that lets the agent automatically upload information into the data dictionary. In release 8.0 of the Oracle database server, an agent could determine whether to self-register. In release 9.0, self-registration occurs only when the HS_AUTOREGISTER
initialization parameter is set to TRUE
(default).
This section contains the following topics:
To ensure correct operation over heterogeneous database links, agent self-registration automates updates to Heterogeneous Services configuration data that describe agents on remote hosts. Agent self-registration is the default behavior. If you do not want to use the agent self-registration feature, then set the initialization parameter HS_AUTOREGISTER
to FALSE
.
Both the server and the agent rely on three types of information to configure and control operation of the Heterogeneous Services connection. These three sets of information are collectively called HS configuration data:
HS configuration data is stored in the Oracle database server's data dictionary. Because the agent is possibly remote, and may therefore be administered separately, several circumstances can lead to configuration mismatches between servers and agents:
Agent self-registration permits successful operation of Heterogeneous Services in all these scenarios. Specifically, agent self-registration enhances inter operability between any Oracle database server and any Heterogeneous Services agent, provided that each is at least as recent as Version 8.0.3. The basic mechanism for this functionality is the ability to upload HS configuration data from agents to servers.
Self-registration provides automatic updating of HS configuration data residing in the Oracle database server data dictionary. This update ensures that the agent self-registration uploads need to be done only once, on the initial use of a previously unregistered agent. Instance information is uploaded on each connection, not stored in the server data dictionary.
The Heterogeneous Services agent self-registration feature can:
The information required to accomplish the above is accessed in the server data dictionary by using these agent-supplied names:
FDS_CLASS
FDS_CLASS_VERSION
"Heterogeneous Services Data Dictionary Views" to learn how to use the Heterogeneous Services data dictionary views.
See Also:
FDS_CLASS
and FDS_CLASS_VERSION
are defined by Oracle or by third-party vendors for each individual Heterogeneous Services agent and version. Oracle Heterogeneous Services concatenates these names to form FDS_CLASS_NAME
, which is used as a primary key to access class information in the server data dictionary.
FDS_CLASS
should specify the type of non-Oracle data store to be accessed and FDS_CLASS_VERSION
should specify a version number for both the non-Oracle data store and the agent that connects to the it. Note that when any component of an agent changes, FDS_CLASS_VERSION
must also change to uniquely identify the new release.
Instance-specific information can be stored in the server data dictionary. The instance name, FDS_INST_NAME
, is configured by the DBA who administers the agent; how the DBA performs this configuration depends on the specific agent in use.
The Oracle database server uses FDS_INST_NAME
to look up instance-specific configuration information in its data dictionary. Oracle uses the value as a primary key for columns of the same name in these views:
Server data dictionary accesses that use FDS_INST_NAME
also use FDS_CLASS_NAME
to uniquely identify configuration information rows. For example, if you port a database from class Sybase8.1.6 to class Sybase8.1.7, both databases can simultaneously operate with instance name SCOTT
and use separate sets of configuration information.
Unlike class information, instance information is not automatically self-registered in the server data dictionary.
HS_AUTOREGISTER
The Oracle database server initialization parameter HS_AUTOREGISTER
enables or disables automatic self-registration of Heterogeneous Services agents. Note that this parameter is specified in the Oracle initialization parameter file, not the agent initialization file. For example, you can set the parameter as follows:
HS_AUTOREGISTER = TRUE
When set to TRUE
, the agent uploads information describing a previously unknown agent class or a new agent version into the server's data dictionary.
Oracle recommends that you use the default value for this parameter (TRUE
), which ensures that the server's data dictionary content always correctly represents definitions of class capabilities and data dictionary translations as used in Heterogeneous Services connections.
To disable agent self-registration, set the HS_AUTOREGISTER
initialization parameter as follows:
HS_AUTOREGISTER = FALSE
Disabling agent self-registration entails that agent information is not stored in the data dictionary. Consequently, the Heterogeneous Services data dictionary views are not useful sources of information. Nevertheless, the Oracle server still requires information about the class and instance of each agent. If agent self-registration is disabled, the server stores this information in local memory.
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 non-Oracle database system database:
SELECT "COL_A" FROM "test"@SYBS WHERE "COL_A" = INITCAP('jones');
The non-Oracle database system 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 non-Oracle database system side, so an error is issued.
If you are performing operations on large amounts of data stored in the non-Oracle database system database, keep in mind that some functions require data to be moved to the integrating Oracle database server before processing can occur.
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 non-Oracle database system table.
The following statement creates a system wide synonym for the EMP
table in the schema of user ORACLE
in the non-Oracle database system database:
SQL> CREATE PUBLIC SYNONYM EMP FOR "ORACLE"."EMP"@SYBS
The following example joins data between the Oracle database server, an IBM DB2 database, and the non-Oracle database system database:
SQL> SELECT O.CUSTNAME, P.PROJNO, E.ENAME, SUM(E.RATE*P."HOURS")
FROM ORDERS@DB2 O, EMP@ORACLE9 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
Use the SQL*Plus COPY command to copy data from the local database to the non-Oracle database system 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 non-Oracle database system database, and commits the transaction:
SQL> COPY FROM SCOTT/TIGER@ORACLE9-
> 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 non-Oracle database system. 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 non-Oracle database system:
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 non-Oracle database system database to the Oracle database server. To create a table on the local database and insert rows from the non-Oracle database system 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 non-Oracle database system 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 non-Oracle database system database to the Oracle database server.
You can use the Heterogeneous Services data dictionary views to access information about Heterogeneous Services. This section addresses the following topics:
The Heterogeneous Services data dictionary views, which all begin with the prefix HS_
, can be divided into four main types:
Most of the data dictionary views are defined for both classes and instances. Consequently, for most types of data there is a *_CLASS
and an *_INST
view.
Like all Oracle data dictionary tables, these views are read-only. Do not use SQL to change the content of any of the underlying tables. To make changes to any of the underlying tables, use the procedures available in the DBMS_HS
package.
The values used for data dictionary content in any particular connection on a Heterogeneous Services database link can come from any of the following sources, in order of precedence:
If the Oracle database server runs with the HS_AUTOREGISTER
server initialization parameter set to FALSE
, then no information is stored automatically in the Oracle data dictionary. The equivalent data is uploaded by the Heterogeneous Services agent on a connection-specific basis each time a connection is made, with any instance-specific information taking precedence over class information.
You can determine the values of Heterogeneous Services initialization parameters by querying the VALUE
column of the V$HS_PARAMETER
view. Note that the VALUE
column of V$HS_PARAMETER
truncates the actual initialization parameter value from a maximum of 255 characters to a maximum of 64 characters, and it truncates the parameter name from a maximum of 64 characters to a maximum of 30 characters.
The views that are common for all services are as follows:
View | Contains |
---|---|
|
Names of the instances and classes that are uploaded into the Oracle8i data dictionary |
|
Information about the Heterogeneous Services initialization parameters |
For example, you can access multiple Sybase gateways from an Oracle database server. After accessing the gateways for the first time, the information uploaded into the Oracle database server could appear as follows:
SQL> SELECT * FROM hs_fds_class; FDS_CLASS_NAME FDS_CLASS_COMMENTS FDS_CLASS_ID --------------------- ------------------------------ ------------ Sybase816 Uses Sybase driver, R1.1 1 Sybase817 Uses Sybase driver, R1.2 21
Two classes are uploaded: a class that accesses Sybase816 and a class that accesses Sybase817. The data dictionary in the Oracle database server now contains capability information, SQL translations, and data dictionary translations for both Sybase816 and Sybase817.
In addition to this information, the Oracle database server data dictionary also contains instance information in the HS_FDS_INST
view for each non-Oracle system instance that is accessed.
When a non-Oracle system is involved in a distributed transaction, the transaction capabilities of the non-Oracle system and the agent control whether it can participate in distributed transactions. Transaction capabilities are stored in the HS_CLASS_CAPS
tables.
The ability of the non-Oracle system and agent to support two-phase commit protocols is specified by the 2PC type capability, which can specify one of the following five types.
The transaction model supported by the driver and non-Oracle system can be queried from Heterogeneous Services' data dictionary view HS_CLASS_CAPS
.
One of the capabilities is of the 2PC type:
SELECT cap_description, translation FROM hs_class_caps WHERE cap_description LIKE '2PC%' AND fds_class_name=`MegaBase6'; CAP_DESCRIPTION TRANSLATION ---------------------------------------- ----------- 2PC type (RO-SS-CC-PREP/2P-2PCC) CC
When the non-Oracle system and agent support distributed transactions, the non-Oracle system is treated like any other Oracle9i server. When a failure occurs during the two-phase commit protocol, the transaction is recovered automatically. If the failure persists, the in-doubt transaction may need to be manually overridden by the database administrator.
Data dictionary views that are specific for the SQL service contain information about:
The HS_*_CAPS
data dictionary tables contain information about the SQL capabilities of the non-Oracle data source and required SQL translations. These views specify whether the non-Oracle data store or the Oracle database server implements certain SQL language features. If a capability is turned off, then Oracle9i does not send any SQL statements to the non-Oracle data source that require this particular capability, but it still performs post-processing.
In order to make the non-Oracle system appear similar to an Oracle database server, Heterogeneous Services connections map a limited set of Oracle data dictionary views onto the non-Oracle system's data dictionary. This mapping permits applications to issue queries as if these views belonged to an Oracle data dictionary. Data dictionary translations make this access possible. These translations are stored in Heterogeneous Services views whose names are suffixed with _DD
.
For example, the following SELECT
statement transforms into a Sybase query that retrieves information about EMP
tables from the Sybase data dictionary table:
SELECT * FROM USER_TABLES@salesdb WHERE UPPER(TABLE_NAME)='EMP';
Data dictionary tables can be mimicked instead of translated. If a data dictionary translation is not possible because the non-Oracle data source does not have the required information in its data dictionary, Heterogeneous Services causes it to appear as if the data dictionary table is available, but the table contains no information.
To retrieve information for which Oracle data dictionary views or tables are translated or mimicked for the non-Oracle system, you can issue the following query on the HS_CLASS_DD
view:
SELECT DD_TABLE_NAME, TRANSLATION_TYPE FROM HS_CLASS_DD WHERE FDS_CLASS_NAME=`Sybase'; DD_TABLE_NAME T ----------------------------- - ALL_ARGUMENTS M ALL_CATALOG T ALL_CLUSTERS T ALL_CLUSTER_HASH_EXPRESSIONS M ALL_COLL_TYPES M ALL_COL_COMMENTS T ALL_COL_PRIVS M ALL_COL_PRIVS_MADE M ALL_COL_PRIVS_RECD M ...
The translation type T
specifies that a translation exists. When the translation type is M
, the data dictionary table is mimicked.
See Also:
Appendix D, "Data Dictionary Translation Support" for a list of data dictionary views that are supported through Heterogeneous Services mapping. |
The Oracle database server stores information about agents, sessions, and parameter. You can use the V$
dynamic performance views to access this information. This section contains the following topics:
The following view shows generation information about agents:
View | Purpose |
---|---|
|
Identifies the set of Heterogeneous Services agents currently running on a given host, using one row per agent process. |
Use this view to determine general information about the agents running on a specified host. The following table shows the most relevant columns (for a description of all the columns in the view, see Oracle9i Database Reference):
The following view shows which Heterogeneous Services sessions are open for the Oracle database server:
View | Purpose |
---|---|
|
Lists the sessions for each agent, specifying the database link used. |
The following table shows the most relevant columns (for an account of all the columns in the view, see Oracle9i Database Reference):
The following view shows which Heterogeneous Services parameters are set in the Oracle database server:
View | Purpose |
---|---|
|
Lists Heterogeneous Services parameters and values registered in the Oracle database server. |
The following table shows the most relevant columns (for an account of all the columns in the view, see Oracle9i Database Reference):
Column | Description |
---|---|
|
Unique Heterogeneous Services session identifier |
|
The name of the Heterogeneous Services parameter |
|
The value of the Heterogeneous Services parameter |
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 © 1996-2001, Oracle Corporation. All Rights Reserved. |
|