7
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

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:

Install the Heterogeneous Services Data Dictionary
Set up your environment to access Heterogeneous Services agents
Create the database link to the non-Oracle system
Test the connection
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 Oracle8 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 Oracle8 server starts an agent process through the Net8 listener. For the Oracle8 server to be able to connect to the agent, you must:

Set up a Net8 service name for the agent that can be used by the Oracle8 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 Oracle8 Heterogeneous Services.
The listener must be set up to listen for incoming request from the Oracle8 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.0.4)
                  (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:

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 Oracle8 data dictionary automatically the first time they are accessed. However, some agent vendors may provide scripts that you must run at the Oracle8 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 Oracle8 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 Oracle8 server is considered a non-Oracle system instance. You can access multiple non-Oracle systems from the same Oracle8 server. See Figure 7-1.

The Oracle8 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 Oracle8 data dictionary.

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.

Figure 7-1 Instances

To avoid unnecessary redundancy, this information is grouped in data dictionary into classes. A class defines a type of non-Oracle system.

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

Instances and Classes that are uploaded into the Oracle8 data dictionary. The uploaded class and instance information can be viewed through the HS_FDS_CLASS view and HS_FDS_INST view respectively.
Capabilities, SQL translations and Data dictionary 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 Oracle8 server.

View Name	Description
HS_FDS_CLASS	View containing classes to be accessible from this Oracle8 server
HS_FDS_INST	View containing instances accessible from this Oracle8 server
HS_CLASS_INIT	View containing initialization parameters for each class
HS_INST_INIT	View containing initialization parameters for each instance
HS_ALL_INITS	View containing both driver and instance initialization parameters
HS_BASE_DD	View containing all capabilities supported by Heterogeneous Services
HS_CLASS_DD	View containing data dictionary translations for each class
HS_INST_DD	View containing data dictionary translations for each instance
HS_ALL_DD	View containing both driver and instance data dictionary translations
HS_BASE_CAPS	View containing all the base capabilities defined.
HS_CLASS_CAPS	View containing capabilities for each class
HS_INST_CAPS	View containing capabilities for each instance
HS_ALL_CAPS	View containing both driver and instance capabilities
HS_EXTERNAL_OBJECTS	View containing information about distributed external procedures and their associated libraries

Table 7-1 Data Dictionary Views for Heterogeneous Services

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 distributed external procedures

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" on page 7-5 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" on page 7-13 for more information.

See Also : The Oracle8 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:

Information about the instances and classes that are upload into the Oracle8 data dictionary. The uploaded class and instance information can be viewed through the HS_FDS_CLASS view and HS_FDS_INST view respectively.
Information about the initialization parameters. This information can be viewed through the HS_CLASS_INIT, HS_INST_INIT, and HS_ALL_INIT views.

For example, you can access both MegaBase release 5 and release 6 from an Oracle8 server. After accessing the agent(s) for the first time, uploaded information in the Oracle8 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 Oracle8 server now contains capability information, SQL translations and data dictionary translations for both MegaBase5 and MegaBase6.

In addition to this information, the Oracle8 server will also contain instance information 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. The transaction capability is stored in the HS_CLASS_CAPS capability table.

The non-Oracle system (and agent) can support one of the following five transaction capabilities.

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 view HS_CLASS_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 Oracle8 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 other nodes 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 mappings 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.

When a capability is turned off, Oracle8 will not send any SQL statements to the non-Oracle data source that contain that particular capability but can still do post-processing.

Views for Data Dictionary Translations

In order to make the non-Oracle system appear as an Oracle8 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 Oracle8 data dictionary views and/or tables are translated or mimicked for the non-Oracle system, you issue the following query the HS_ALL_DD view:

SELECT DD_TABLE_NAME, TRANSLATION_TYPE
FROM   HS_ALL_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 Oracle8 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 initialization parameters.

Table 7-2

Procedure Description

CREATE_INST_INIT

Set an initialization parameter for a non-Oracle system

DROP_INST_INIT

Delete a non-Oracle system instance initialization parameter

DBMS_HS procedures

Setting Initialization Parameters

Initialization parameters can be set either in the Oracle8 server or in the Heterogeneous Services agent. To set initialization parameters in the Oracle8 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 Oracle8 server, the value of initialization parameter in the Oracle8 server will take precedence.

There are two types of initialization parameters:

generic initialization parameters
agent-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.

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

Both generic and agent-specific 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 Chapter A, "Heterogeneous Services Initialization Parameters" for more information on initialization parameters.

Unsetting Initialization Parameters

To unset a initialization parameter in the Oracle8 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.

Procedure	Description
CREATE_INST_INIT	Set an initialization parameter for a non-Oracle system
DROP_INST_INIT	Delete a non-Oracle system instance initialization parameter

7Administering Oracle Heterogeneous Services