Oracle Heterogeneous Services Release 8.1.7 Part Number A88714-01 |
|
This chapter describes how to maintain a heterogeneous distributed environment when using a transparent gateway.
This chapter contains these topics:
This section explains the generic steps to configure access to a non-Oracle system. Please see the Installation and User's Guide for your agent for more installation information. The instructions for configuring your agent may slightly differ from the following.
The steps for setting up access to a non-Oracle system are:
For most users, the script to install data dictionary tables and views for Heterogeneous Services is automatically run at the time of installation.
In case you need to install these tables and views manually, you must run a script that creates the Heterogeneous Services data dictionary tables, views, and packages. On most systems this script is called caths.sql
and resides in $ORACLE_HOME/rdbms/admin
.
To initiate a connection to the non-Oracle system, the Oracle database server starts an agent process through the Net8 listener. For the Oracle database server to be able to connect to the agent, you must:
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)) (HS = 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. See the installation documentation for your agent for more information about how to define the Net8 service name.
The following is a sample entry for the listener in the listener.ora
file:
LISTENER = (ADDRESS_LIST = (ADDRESS= (PROTOCOL=tcp) (HOST = dlsun206) (PORT = 1521) ) ) ... SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME=SalesDB) (ORACLE_HOME=/home/oracle/tg4sybs/8.1.6) (PROGRAM=tg4sybs) ) )
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 Net8 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, enter:
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:
Registration is an operation through which Oracle stores information about an agent in the Heterogeneous Services 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 this release, 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 you must set the HS_AUTOREGISTER initialization parameter 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 Heterogeneous Services configuration data.
To disable agent self-registration, set the HS_AUTOREGISTER initialization parameter as follows:
HS_AUTOREGISTER = FALSE
If you disable agent self-registration, then agent information is not stored in the data dictionary. Consequently, the Heterogeneous Services data dictionary views cease to be useful sources of information. However, the Oracle database server still requires information about the class and instance of each agent. To meet this requirement when agent self-registration is disabled, the Oracle database server stores this information in local memory.
Heterogeneous Services configuration data is stored in the Oracle database server's data dictionary. Because the agent is possibly remote, and can 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 interoperability 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 Heterogeneous Services configuration data from agents to servers.
Self-registration provides automatic updating of Heterogeneous Services configuration data residing in the Oracle database server data dictionary. The update ensures that the agent self-registration uploads need to be done only once, during the initial use of a previously unregistered agent. Instance information is uploaded on each connection and is not stored in the database server data dictionary.
The Heterogeneous Services agent self-registration feature can:
The information required to accomplish this is accessed in the database server data dictionary by using the following agent-supplied names:
"Using 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 to which it connects. 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 database server data dictionary. The instance name, FDS_INST_NAME, is configured by the database administrator who administers the agent; how the database administrator 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. Instances of the same name but that occur under different classes have separate sets of configuration information. For example, if your database contains a Sybase816 and a Sybase817 class, but both of these classes have an instance called SALES, then each SALES instance has a separate set of configuration information.
Unlike class information, instance information is not automatically self-registered in the server data dictionary.
The Oracle database server initialization parameter HS_AUTOREGISTER enables or disables automatic self-registration of Heterogeneous Services agents. 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 Corporation 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.
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 three 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, the 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.
See Also:
|
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 |
---|---|
HS_FDS_INST |
Names of the instances and classes that are uploaded into the Oracle8i data dictionary |
HS_INST_INIT |
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 and HS_INST_CAPS capability 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 views HS_CLASS_CAPS and HS_INST_CAPS.
An example of the two-phase commit capability follows:
SELECT cap_description, translation FROM hs_class_caps WHERE cap_description LIKE '2PC%' AND fds_class_name=`Sybase'; 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 Oracle database 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 the Oracle database server 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 or HS_INST_DD views:
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 B, "Heterogeneous Services Data Dictionary Views" for a list of data dictionary views that are supported through Heterogeneous Services mapping. |
The Oracle database server stores information about agents, sessions, and parameters. 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 |
---|---|
V$HS_AGENT |
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 describes the most relevant columns
The following view shows which Heterogeneous Services sessions are open for the Oracle database server:
View | Purpose |
---|---|
V$HS_SESSION |
Lists the sessions for each agent, specifying the database link used. |
The following table shows the most relevant columns
The following view shows which Heterogeneous Services parameters are set in the Oracle database server:
View | Purpose |
---|---|
V$HS_PARAMETER |
Lists Heterogeneous Services parameters and values registered in the Oracle database server. |
The following table describes the most relevant columns.
Column | Description |
---|---|
HS_SESSION_ID |
Unique Heterogeneous Services session identifier |
PARAMETER |
The name of the Heterogeneous Services parameter |
VALUE |
The value of the Heterogeneous Services parameter |
The DBMS_HS package contains functions and procedures that allow you to specify and unspecify Heterogeneous Services initialization parameters, capabilities, instance names, and class names. These parameters are configured in the gateway initialization file, not the Oracle initialization parameter file. The only exception is HS_AUTOREGISTER, which is set in the Oracle initialization parameter file.
See Also:
Oracle8i Supplied PL/SQL Packages Reference for a reference listing of all DBMS_HS package interface information for Heterogeneous Services administration. |
Set initialization parameters either in the Oracle database server or in the Heterogeneous Services agent.
To set initialization parameters in the Oracle database server, use the DBMS_HS package. See the agent's installation documentation for more information. If the same initialization parameter is set both in the agent and the Oracle database server, then the value of the initialization parameter set in the Oracle database server takes precedence.
Many, although not all, Oracle gateways allow initialization parameters to be set inside the initialization files. The name of the initialization file is usually initagent_sid.ora and it is usually located in $ORACLE_HOME/product_name/admin. Parameters set in the server override those set in the initialization files.
There are two types of initialization parameters to consider when setting up your gateway:
Type | Description |
---|---|
Generic |
Defined by Heterogeneous Services. See Appendix A, "Heterogeneous Services Initialization Parameters" for more information on generic initialization parameters. |
Non-Oracle class-specific |
Defined by the Oracle transparent gateway product. Some non-Oracle data store class-specific parameters may be mandatory. For example, a parameter may include connection information required to connect to a non-Oracle system. These parameters are documented in the installation documentation for your agent. |
You can set both generic and non-Oracle data store class-specific Heterogeneous Services initialization parameters in the Oracle database server using the CREATE_INST_INIT procedure in the DBMS_HS package.
For example, set the HS_DB_DOMAIN initialization parameter as follows
DBMS_HS.CREATE_INST_INIT (FDS_INST_NAME => `SalesDB', FDS_CLASS_NAME => `Sybase', INIT_VALUE_NAME => `HS_DB_DOMAIN', INIT_VALUE => `US.SALES.COM');
See Also:
Appendix A, "Heterogeneous Services Initialization Parameters" for more information about initialization parameters. |
To unspecify an Heterogeneous Services initialization parameter in the Oracle database server, use the DROP_INST_INIT procedure. For example, to delete the HS_DB_DOMAIN entry, enter:
DBMS_HS.DROP_INST_INIT (FDS_INST_NAME => `SalesDB', FDS_CLASS_NAME => `Sybase', INIT_VALUE_NAME => `HS_DB_DOMAIN');
|
![]() Copyright © 1996-2001 Oracle Corporation. All Rights Reserved. |
|