6
Administering Oracle Heterogeneous Services

This chapter describes database administration tasks required to maintain a heterogeneous distributed environment. Topics include:

• Setting up access to Non-Oracle Systems

• Structure of the Heterogeneous Services Data Dictionary

• The Data Dictionary Views

• The DBMS_HS Package (setting initialization parameters)

• Security for Distributed External Procedures

• Agent Self-Registration

Setting up access to Non-Oracle Systems

This section explains the generic steps to configure access to a non-Oracle system. Please see your Installation and User's Guide for your particular agent for more installation information. Configuring your particular agent might slightly differ from what is presented in this section.

The steps are:

1. Install the Heterogeneous Services Data Dictionary

2. Set up your environment to access Heterogeneous Services agents

3. Create the database link to the non-Oracle system

4. Test the connection

5. Optionally, register distributed external procedures

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 might already be installed on your Oracle8i server. You can confirm this by checking for the existence of Heterogeneous Services data dictionary views, for example SYS.HS_FDS_CLASS.

Set Up 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 will include 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. The listener must be set up to listen for incoming request from the Oracle8i server, and spawn Heterogeneous Services agents. The listener.ora file must be modified to set up the listener to start Heterogeneous Services agents, and the listener must be (re-)started.

A Sample Descriptor for a Net8 Service Name

The following is a sample entry for the service name in the tnsnames.ora:

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 nameservers 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 Entry in LISTENER.ORA

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. The SID_NAME is typically used to define the initialization parameter file for the agent.

Create the Database Link to the Non-Oracle System

To create a database link to the non-Oracle system, you just use the CREATE DATABASE LINK command to create private or public database links.

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 an MegaBase release 6 server, you could create database link as follows:

CREATE DATABASE LINK salesdb
USING `MegaBase6_sales';

See Also: For more information on creating database links, see Chapter 2, "Distributed Database Administration".

Test the Connection

To test the connection to the non-Oracle system, you can 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 from an existing table or view using the database link, for example::

SELECT * 
FROM product@salesdb 
WHERE product_name like '%pencil%';

When you try to access the non-Oracle system for the first time, the Heterogeneous Services agent will upload information into the Heterogeneous Services data dictionary. The uploaded information includes:

• Capabilities of the non-Oracle system: The agent specifies the capabilities of the non-Oracle system. For example, 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 will upload information into the Oracle8i data dictionary automatically the first time they are accessed. However, some agent vendors may provide scripts that you must run at the Oracle8i server.

Register Distributed External Procedures (Optional)

This step is only required for agents that support distributed external procedures. Distributed external procedures enable users to procedurally access a non-Oracle system. If the agent vendor created distributed external procedures, they will provide a script or installer to register those distributed external procedures in the Oracle8i server.

If you use distributed external procedures to access the non-Oracle system, use a PL/SQL remote procedure call to execute the remote procedure:

execute foo@non_oracle_system(1,2,3) 
procedure successfully completed.

Note: You typically do not need distributed external procedures to execute stored procedures in the non-Oracle system.

Note: See the Installation and User's Guide for your agent for more information on how to register distributed external procedures. The distributed external procedures that can be executed at the non-Oracle system are defined by the agent vendor. See the Installation and User's Guide for your agent for a list of procedures that can be executed.

Structure of the Heterogeneous Services Data Dictionary

Each non-Oracle system you access from an Oracle8i server is considered a non-Oracle system instance and class. You can access multiple non-Oracle systems from the same Oracle8i server. See Figure 6-1.

The Oracle8i server must know the non-Oracle system capabilities (SQL translations, data dictionary translations) for each non-Oracle system that it accesses. This information is stored in the Oracle8i data dictionary.

Figure 6-1 Instances

If this information were stored separately for each non-Oracle systems you access, the amount of stored data dictionary information could become large and sometimes redundant. For example, when you must access three non-Oracle system instances of the same type, the same capabilities, SQL translations and data dictionary translations are stored.

To avoid unnecessary redundancy, this information is organized by classes and instances in the data dictionary. A class defines a type of non-Oracle system, an instance defines specializations of a class for a specific non-Oracle system. Note that instance information takes precedence over class information and class information takes precedence over server supplied defaults.

If you access multiple non-Oracle systems of the same class (type), you may want to set certain information, like initialization parameters, at the instance level. Heterogeneous Services stores both class and instance information. Multiple instances can share the same class information, but each non-Oracle system instance will have its own instance information.

Consider an example where the Oracle8i server accesses three instances of type Megabase release 5, and two instances of Megabase release 6. Suppose Megabase release 5 and Megabase release 6 have different capabilities. The data dictionary will contain two class definitions, one for release 5 and one for release 6, and 5 instance definitions.

The Data Dictionary Views

The Heterogeneous Services data dictionary views, contain information about:

• Names of instances and classes uploaded into the Oracle8i data dictionary. You can view the uploaded class and instance information through the HS_FDS_CLASS view and HS_FDS_INST view respectively.

• Capabilities, including SQL translations, defined per class or instance. Capability information is viewable through the HS_..._CAPS views.

• Data Dictionary translations defined per class or instance. Data dictionary translation information is viewable through the HS_..._DD views.

• Initialization parameters are defined per class or instance. Initialization parameter information is viewable through the HS_...INIT views.

• Distributed external procedures that can be accessed from the Oracle8i server

Table 6-1 Data Dictionary Views for Heterogeneous Services.

View Name	Description
HS_FDS_CLASS	View identifies classes accessible from this Oracle8i server
HS_FDS_INST	View identifies instances accessible from this Oracle8i server
HS_CLASS_INIT	View identifies initialization parameters for each class
HS_INST_INIT	View identifies initialization parameters for each instance
HS_BASE_DD	View identifies all data dictionary translation tablenames supported by Heterogeneous Services
HS_CLASS_DD	View identifies data dictionary translations for each class
HS_INST_DD	View identifies data dictionary translations for each instance
HS_BASE_CAPS	View identifies all capabilities supported by Heterogeneous Services
HS_CLASS_CAPS	View identifies capabilities for each class
HS_INST_CAPS	View identifies capabilities for each instance
HS_EXTERNAL_OBJECTS	View provides information about distributed external procedures and their associated libraries

The views can be divided into four groups:

• General views

• Views used for the transaction service

• Views used for the SQL service

• Views used for the procedural service

Most of the data dictionary views are defined for both classes and instances. Consequently, for most types of information there is a "..._CLASS" and a "..._INST" view defined.

See Also: "Structure of the Heterogeneous Services Data Dictionary" for more information about classes and instances.

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, you must use the procedures available in the package "DBMS_HS". See "The DBMS_HS Package" for more information.

See Also : The Oracle8i Reference for more detailed information about these views

General Data Dictionary Views for Heterogeneous Services

The views that are common for all services are the views that contain:

• Names of the instances and classes that are uploaded into the Oracle8i data dictionary. The uploaded class and instance names can be viewed through the HS_FDS_CLASS view and HS_FDS_INST view respectively.

• Information about the Heterogeneous Services initialization parameters. This information can be viewed through the HS_CLASS_INIT, and HS_INST_INIT views.

For example, you can access both MegaBase release 5 and release 6 from an Oracle8i server. After accessing the agent(s) for the first time, uploaded information in the Oracle8i server could look like:

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.

Views for the Transaction Service

When a non-Oracle system is involved in a distributed transaction, the transaction capabilities of the non-Oracle system (and 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 since 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 Commit Point Site. That is, it can not prepare data, but it can remember the outcome of a particular transaction if asked to 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 to 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 "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 supports 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 will be recovered automatically. If the failure persists, the in-doubt transaction might need to be manually overridden by the database administrator. See Chapter 3, "Distributed Transactions" for more information about distributed transactions.

Transactions with Distributed External Procedures

For distributed external procedures it is unknown whether it will make changes to data at the non-Oracle system. To ensure the consistency of the heterogeneous distributed database, Oracle will assume that the distributed external procedure updates the non-Oracle system.

Accordingly, the distributed external procedure will participate in the remote or distributed transaction, depending on whether it is the only node that was accessed or whether other nodes were accessed as well. Therefore, to use a distributed external procedure, the agent must at least support the "Single-Site" transaction model.

Views for the SQL Service

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

• SQL capabilities and SQL translations of the non-Oracle data source

• Data Dictionary translations to map Oracle data dictionary views to the data dictionary of the non-Oracle system.

Views for Capabilities and Translations

The HS_..._CAPS data dictionary tables contain information about the SQL capabilities of the non-Oracle data source and necessary SQL translations.

HS_..CAPS specifies whether the non-Oracle data store or the Oracle server implements certain SQL language features. If a capability is turned off, Oracle8i does not send any SQL statements to the non-Oracle data source that require that particular capability but it can still do post-processing.

Views for Data Dictionary Translations

In order to make the non-Oracle system appear as an Oracle8i server, the non-Oracle system data dictionary can be queried just as if it were an Oracle data dictionary. Data Dictionary translations that are defined make this possible. These translations are stored in the HS_..._DD views.

For example, the following SELECT statement will be transformed 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, simply because the non-Oracle data source does not have the required information stored 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 Oracle8i data dictionary views and/or tables are translated or mimicked for the non-Oracle system, you 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.

Views for Distributed External Procedures

Distributed external procedures and remote libraries are administered in the Oracle8i server. The agent vendor will provide scripts to register distributed external procedures and their libraries. Information about these registered procedures and libraries are stored in the HS_EXTERNAL_OBJECTS data dictionary view. The information includes:

• The name of the distributed external procedure or remote library

• A PL/SQL prototype that gives information on the 3GL routine, including its name, its arguments, and library name.

• The instance name of the non-Oracle system the distributed external procedure logically resides.

The DBMS_HS Package

The DBMS_HS package contains functions and procedures for application developers and database administrators to set and unset Heterogeneous Services initialization parameters, capabilities, instance names, class names, etc.

See Appendix B, "DBMS_HS Package Reference" for a reference listing off all DBMS_HS package interface information for administering Heterogeneous Services

Setting Initialization Parameters

Initialization parameters can be set either in the Oracle8i server or in the Heterogeneous Services agent. To set initialization parameters in the Oracle8i server, you must use the DBMS_HS package. Please see the installation and user's guide for your particular agent for more information. If the same initialization parameter is set both in the agent and the Oracle8i server, the value of initialization parameter in the Oracle8i server will take precedence.

There are two types of initialization parameters:

• generic initialization parameters

• non-Oracle data store class-specific initialization parameters

Generic initialization parameters are defined by Heterogeneous Services. See Appendix A, "Heterogeneous Services Initialization Parameters" for more information on generic initialization parameters.

Non-Oracle data store class-specific initialization parameters are defined by the agent vendor. Some non-Oracle data store class-specific initialization parameters may be mandatory. For example, an initialization parameter may include connection information required to connect to a non-Oracle system. Non-Oracle data store class-specific parameters are documented in the installation and user's guide for your agent.

Both generic and non-Oracle data store class-specific HS initialization parameters can be set in the Oracle server using the CREATE_INST_INIT procedure in the DBMS_HS package.

For example, you 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: See Appendix A, "Heterogeneous Services Initialization Parameters" for more information on initialization parameters.

Unsetting Initialization Parameters

To unset a Heterogenous Services initialization parameter in the Oracle8i server, you must use the DROP_INST_INIT procedure. For example, to delete the HS_DB_DOMAIN entry:

DBMS_HS.DROP_INST_INIT 
          (FDS_INST_NAME   => `SalesDB',
           FDS_CLASS_NAME  => `MegaBase6',
           INIT_VALUE_NAME => `HS_DB_DOMAIN');






Note:



See Appendix  B, "DBMS_HS Package Reference" for a full description of the DBMS_HS package.  

Security for Distributed External Procedures

Please see the agent-specific documentation on how to control execute privileges on distributed external procedures.

Agent Self-Registration

Agent self-registration automates the process of updating Hetergeneous Services configuration data describing agents on remote hosts, to ensure correct operation over heterogeneous database links. Note that agent self-registration is default behavior. If you do not want to use the agent self-registration feature, you must set the value of the Oracle initialization parameter HS_AUTOREGISTER to false. See "Oracle Server Initialization Parameter HS_AUTOREGISTER" for more information.

Both the server and the agent rely on three types of information to configure and control operation of the HS connection:

• HS initialization parameters: these parameters provide control over various connection-specific details of operation.

• Capability definitions: these definitions identify details like SQL language features supported by the non-Oracle datasource.

• Data dictionary translations: these translations map references to Oracle data dictionary tables and views into equivalents specific to the non-Oracle data source.

This document refers to these three sets of information collectively as HS configuration data.

Advantages of Agent Self-Registration

HS configuration data (that you specify using the DBMS_HS_ADMIN package discussed in the previous section) is stored in the Oracle server's data dictionary. Because the agent may likely be remote, and may therefore be administered separately, several circumstances could lead to configuration mismatches between servers and agents:

• An agent could be newly installed on a separate machine and the server would have no HS data dictionary content to represent the agent's HS configuration data.

• A server could be newly installed and lack the necessary HS configuration data for existing agents and non-Oracle data stores.

• A non-Oracle instance could be upgraded from an older version to a newer version, requiring modification of the HS configuration data.

• An HS agent at a remote site could be upgraded to a new version or patched, requiring modification of the HS configuration data.

• A DBA at the non-Oracle site could change the agent setup, possibly for tuning or testing purposes, in a manner which affects HS configuration data.

Agent self-registration permits successful operation of Heterogeneous Services in all these scenarios.

Specifically, agent self-registration enhances interoperability between any Oracle server and any HS agent, provided that each is at least as recent as Version 8.0.3. The basic mechanism for this is ability to upload HS configuration data (HS Data Dictionary content) from agents to servers.

Self-registration provides automatic updating of HS configuration data residing in the Oracle server data dictionary (if enabled by the server initialization parameter HS_AUTOREGISTER (see below)). Such a data dictionary update assures 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.

How Does Agent Self-Registration Work?

The HS agent self-registration feature can:

• Identify the agent and the non-Oracle data store to the Oracle server.

• Permit agents to define Heterogeneous Services initialization parameters for use both by the agent and connected Oracle8i servers:

• Upload capability definitions and data dictionary translations, if available, from an HS agent during connection initialization.

  Note that, when both the server and the agent are release 8.1 or higher, the upload of class information occurs only when the class is undefined in the server data dictionary. Similarly, instance information is uploaded only if the instance is undefined in the server data dictionary.

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

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 which connects to the it. Note that, when any component of an agent changes (agent executable or uploadable definitions) 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 does this depends on the specific agent in use. The Oracle server then uses FDS_INST_NAME to look up instance-specific configuration information in its data dictionary, using it as a primary key for columns of the same name in the FDS_INST_INIT, FDS_INST_CAPS, and FDS_INST_DD 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 are porting a database from class "MegaBase8.0.4" to class "MegaBase8.1.3", both databases can simultaneously operate with instance name "Scott" and can use separate sets of configuration information.

Unlike class information, instance information is not automatically self-registered in the server data dictionary.

• If the server data dictionary contains instance information, it represents DBA-defined setup details which fully define the instance configuration. No instance information is uploaded from the agent to the server.

• If the server data dictionary contains no instance information, any instance information made available by a connected agent is uploaded to the server for use in that connection. The uploaded instance data is not stored in the server data dictionary.

Oracle Server Initialization Parameter HS_AUTOREGISTER

The Oracle server initialization parameter HS_AUTOREGISTER enables or disables automatic self-registration of HS agents. When set to TRUE, information describing a previously unknown agent class or a new agent version is uploaded into the server's data dictionary.

See the Oracle8i Reference for a description and the syntax of this parameter.

It is recommended that you use the dfault value for this parameter (TRUE) which assures that the server's data dictionary content always correctly represents definitions of class capabilities and data dictionary translations as used in HS connections.

---