Oracle8i Distributed Database Systems
Release 2 (8.1.6)

Part Number A76960-01

Library

Product

Contents

Index

Go to previous page Go to next page

7
Managing Oracle Heterogeneous Services Using Transparent Gateways

This chapter teaches you how to maintain a heterogeneous distributed environment when using a transparent gateway. Topics include:

Setting Up Access to Non-Oracle Systems

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:

Step 1: Install the Heterogeneous Services Data Dictionary

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.


Note:

The data dictionary tables, views, and packages may already be installed on your Oracle8i server. Check for the existence of Heterogeneous Services data dictionary views, for example, SYS.HS_FDS_CLASS. 


Step 2: Set Up the Environment to Access Heterogeneous Services Agents

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:

  1. Set up a Net8 service name for the agent that can be used by the Oracle8i server. The Net8 service name descriptor includes protocol-specific information needed to access the Net8 listener. The service name descriptor must include the (HS=OK) clause to make sure the connection uses Oracle8i Heterogeneous Services.

  2. Set up the listener to listen for incoming request from the Oracle8i server and spawn HS agents. Modify the listener.ora file so that the listener can start Heterogeneous Services agents, and then restart the listener.

A Sample Entry for a Net8 Service Name

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.

A Sample Listener Entry

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.

Step 3: Create the Database Link to the Non-Oracle System

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. 

Step 4: Test the Connection

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:

Type of Data  Explanation 

Capabilities of the non-Oracle system 

For example, the agent specifies whether it can perform a join, or a GROUP BY. 

SQL translation information 

The agent specifies how to translate Oracle functions and operators into functions and operators of the non-Oracle system. 

Data dictionary translations 

To make the data dictionary information of the non-Oracle system available just as if it were an Oracle data dictionary, the agent specifies how to translate Oracle data dictionary tables into tables and views of the non-Oracle system. 


Note:

Most agents upload information into the Oracle8i data dictionary automatically the first time they are accessed. Some agent vendors may provide scripts, however, that you must run on the Oracle8i server. 


See Also:

"Using the Heterogeneous Services Data Dictionary Views"

Registering Agents

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:

Enabling Agent Self-Registration

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  Description 

HS initialization parameters 

Provide control over various connection-specific details of operation. 

Capability definitions 

Identify details like SQL language features supported by the non-Oracle datasource. 

Data dictionary translations 

Map references to Oracle data dictionary tables and views into equivalents specific to the non-Oracle data source. 

See Also:

"Specifying HS_AUTOREGISTER"

Using Agent Self-Registration to Avoid Configuration Mismatches

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.

Understanding Agent Self-Registration

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:

FDS_CLASS and FDS_CLASS_VERSION

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.


Note:

This information is uploaded when you initialize each connection. 


FDS_INST_NAME

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.

Specifying HS_AUTOREGISTER

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.

See Also:

Oracle8i Reference for a description of this parameter. 

Disabling Agent Self-Registration

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.

Using the Heterogeneous Services Data Dictionary Views

You can use the HS data dictionary views to access information about Heterogeneous Services. This section addresses the following topics:

Understanding the Types of Views

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.

Table 7-1 Data Dictionary Views for Heterogeneous Services
View  Type  Identifies 

HS_BASE_CAPS 

SQL service 

All capabilities supported by Heterogeneous Services 

HS_BASE_DD 

SQL service 

All data dictionary translation table names supported by Heterogeneous Services 

HS_CLASS_CAPS 

Transaction service, SQL service 

Capabilities for each class 

HS_CLASS_DD 

SQL service 

Data dictionary translations for each class 

HS_CLASS_INIT 

General 

Initialization parameters for each class 

HS_FDS_CLASS 

General 

Classes accessible from this Oracle8i server 

HS_FDS_INST 

General 

Instances accessible from this Oracle8i server 

HS_INST_CAPS 

Transaction service, SQL service 

Capabilities for each instance 

HS_INST_DD 

SQL service 

Data dictionary translations for each instance 

HS_INST_INIT 

General 

Initialization parameters for each instance 

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:

 

Understanding the Sources of Data Dictionary Information

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.


Note:

It is not possible to determine positively what capabilities and what data dictionary translations are in use for a given session due to the possibility that an agent can upload instance 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.

Using the General Views

The views that are common for all services are as follows:

View  Contains 

HS_FDS_CLASS

HS_FDS_INST 

Names of the instances and classes that are uploaded into the Oracle8i data dictionary 

HS_CLASS_INIT

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.

Using the Transaction Service Views

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.

Read-only (RO) 

The non-Oracle system can only be queried with SQL SELECT statements. Procedure calls are not allowed because procedure calls are assumed to write data. 

Single-Site (SS) 

The non-Oracle system can handle remote transactions but not distributed transactions. That is, it can not participate in the two-phase commit protocol. 

Commit Confirm (CC) 

The non-Oracle system can participate in distributed transactions. It can participate in Oracle's two-phase commit protocol but only as the Commit Point Site. That is, it can not prepare data, but it can remember the outcome of a particular transaction if asked by the global coordinator. 

Two-Phase Commit 

The non-Oracle system can participate in distributed transactions. It can participate in Oracle's two-phase commit protocol, as a regular two-phase commit node, but not as a Commit Point Site. That is, it can prepare data, but it can not remember the outcome of a particular transaction if asked to by the global coordinator. 

Two-Phase Commit Confirm 

The non-Oracle system can participate in distributed transactions. It can participate in Oracle's two-phase commit protocol as a regular two-phase commit node or as the Commit Point Site. That is, it can prepare data and it can remember the outcome of a particular transaction if asked by the global coordinator. 

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. 

Using the SQL Service Views

Data dictionary views that are specific for the SQL service contain information about:

Using Views for Capabilities and Translations

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.

Using Views for Data Dictionary Translations

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. 

Using the Heterogeneous Services Dynamic Performance Views

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:

Determining Which Agents Are Running on a Host

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):

Table 7-2 V$HS_AGENT
Column  Description 

AGENT_ID 

Net8 session identifier used for connections to agent (listener.ora SID) 

MACHINE 

Operating system machine name 

PROGRAM 

Program name of agent 

AGENT_TYPE 

Type of agent 

FDS_CLASS_ID 

The ID of the foreign data store class 

FDS_INST_ID 

The instance name of the foreign data store 

Determining the Open HS Sessions

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):

Table 7-3 V$HS_SESSION
Column  Description 

HS_SESSION_ID 

Unique HS session identifier 

AGENT_ID 

Net8 session identifier used for connections to agent (listener.ora SID) 

DB_LINK 

Server database link name used to access the agent NULL means that no database link is used (eg, when using external procedures) 

DB_LINK_OWNER 

Owner of the database link in DB_LINK 

Determining the HS Parameters

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):

Table 7-4 V$HS_SESSION
Column  Description 

HS_SESSION_ID 

Unique HS session identifier 

PARAMETER 

The name of the HS parameter 

VALUE 

The value of the HS parameter 

Using the DBMS_HS Package

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. 

Specifying Initialization Parameters

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. 

Unspecifying 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. 


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index