Oracle8i Distributed Database Systems Release 2 (8.1.6) A76960-01 |
|
This chapter teaches you how to maintain a heterogeneous distributed environment when using a transparent gateway. Topics include:
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:
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 Oracle8i server starts an agent process through the Net8 listener. For the Oracle8i 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:
MegaBase6_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 and User's Guide for your agent for more information about how to define the Net8 service name.
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 Net8 service name.
For example, to create a database link to the SALES database on a MegaBase release 6 server, you might issue:
CREATE DATABASE LINK sales USING `MegaBase6_sales';
See Also:
Chapter 2, "Managing a Distributed Database" for more information on creating database links. |
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 HS 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 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 8.1, 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 HS 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 HS 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 interoperability between any Oracle database server and any HS 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 HS 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:
"Using the Heterogeneous Services Data Dictionary Views" to learn how to use the HS data dictionary views.
See Also:
FDS_CLASS and FDS_CLASS_VERSION are defined by Oracle or by third-party vendors for each individual HS 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 MegaBase8.0.4 to class MegaBase8.1.3, 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.
The Oracle database server initialization parameter HS_AUTOREGISTER enables or disables automatic self-registration of HS 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 HS 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 HS data dictionary views are not useful sources of information. Nevertheless, Oracle still requires information about the class and instance of each agent. If agent self-registration is disabled, Oracle stores this information in local memory.
You can use the HS data dictionary views to access information about Heterogeneous Services. This section addresses the following topics:
The HS 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.
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 HS 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 HS 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 HS initialization parameters |
For example, you can access both MegaBase release 5 and release 6 from an Oracle8i server. After accessing the agents for the first time, the information uploaded into the Oracle8i server could look like:
SQL> SELECT * FROM hs_fds_class; FDS_CLASS_NAME FDS_CLASS_COMMENTS FDS_CLASS_ID --------------------- ------------------------------ ------------ MegaBase5 Uses ODBC HS driver, R1.0 1 MegaBase6 Uses ODBC HS driver, R1.0 21
Two classes are uploaded: one class to access MegaBase release 5 servers and one class to access MegaBase release 6 servers. The data dictionary in the Oracle8i server now contains capability information, SQL translations, and data dictionary translations for both MegaBase5 and MegaBase6.
In addition to this information, the Oracle8i 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.
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 Oracle8i 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.
See Also:
Chapter 4, "Distributed Transactions Concepts" for more information about distributed transactions. |
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 Oracle8i 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 Oracle8i server, HS 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 HS views whose names are suffixed with _DD.
For example, the following SELECT statement transforms into a MegaBase query that retrieves information about EMP tables from the MegaBase 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, HS causes it to appear as if the data dictionary table is available, but the table contains no information.
To retrieve information for which Oracle8i 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 view:
SELECT DD_TABLE_NAME, TRANSLATION_TYPE FROM HS_CLASS_DD WHERE FDS_CLASS_NAME=`MegaBase6'; 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, "Data Dictionary Views Available Through Heterogeneous Services" 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 |
---|---|
V$HS_AGENT |
Identifies the set of HS 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 Oracle8i Reference):
The following view shows which HS 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 (for an account of all the columns in the view, see Oracle8i Reference):
The following view shows which HS parameters are set in the Oracle database server:
View | Purpose |
---|---|
V$HS_PARAMETER |
Lists HS 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 Oracle8i Reference):
Column | Description |
---|---|
HS_SESSION_ID |
Unique HS session identifier |
PARAMETER |
The name of the HS parameter |
VALUE |
The value of the HS parameter |
The DBMS_HS package contains functions and procedures that allow you to specify and unspecify Heterogeneous Services initialization parameters, capabilities, instance names, class names, etc. These parameters are configured in the gateway initialization file--not the Oracle initialization parameter file. The only exceptions is HS_AUTOREGISTER, which is set in the Oracle initialization parameter file.
See Also:
Oracle8i Supplied PL/SQL Packages Reference for a reference listing off all DBMS_HS package interface information for HS administration. |
Set initialization parameters either in the Oracle8i server or in the Heterogeneous Services agent. To set initialization parameters in the Oracle8i server, use the DBMS_HS package. Please see the agent's Installation and User's Guide for more information. If the same initialization parameter is set both in the agent and the Oracle8i server, then the value of initialization parameter in the Oracle8i server takes precedence.
The following types of initialization parameters exist:
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 agent vendor. 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 and User's Guide for your agent. |
You can set both generic and non-Oracle data store class-specific HS 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 => `MegaBase6', 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 HS initialization parameter in the Oracle8i 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 => `MegaBase6', INIT_VALUE_NAME => `HS_DB_DOMAIN');
See Also:
Oracle8i Supplied PL/SQL Packages Reference for a full description of the DBMS_HS package. |
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|