Oracle8 Server Distributed Database Systems Release 8.0 A54653_01

Library Product Contents Index

7
Administering Oracle Heterogeneous Services

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

• Non-Oracle System Classes and Instances
• Setting up access to Non-Oracle Systems
• The Data Dictionary Views
• The DBMS_HS Package

Non-Oracle System Classes and Instances

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.

Figure 7-1: Instances

The Oracle8 Server must know the non-Oracle system capabilities (SQL translations, data dictionary translations, initialization parameters etc.), for each non-Oracle system that it accesses. This information is stored in the Oracle8 data dictionary. If this information is 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, you can group the information in the 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 can store both class- and instance-level information. Multiple instances can share the same class information, but each non-Oracle system instance will have its own instance-level information.

Note: Class information is supplied by the Agent vendor. Instance-level information can be set and changed by the Oracle8 DBA.

The following are some examples of how information is stored in the Oracle8 data dictionary.

Example 1

For example, you need to access three non-Oracle systems of type X from an Oracle8 Server. The capabilities and translations havr been defined in a single class which is supplied by the agent vendor. For each non-Oracle system instance, you can specify instance-specific information like initialization parameter values. Since there are three non-Oracle systems, each requiring an instance but sharing the same class, the Oracle8 data dictionary will contain one class definition and three instance definitions.

Example 2

In this scenario the same agent allows you to access both version 1 and version 2 of non-Oracle system X. Assume that version 2 has more, or different, capabilities than version 1. To accommodate the different capabilities, the agent vendor would provide two class definitions with the agent.

You want to access multiple servers of non-Oracle system X version 1. You can define instance information for each non-Oracle system version 1 server. When you access a particular non-Oracle system version 1 server, both the class and instance information will be used to access that server.

Example 3

Finally, consider an agent that can access ODBC-based data stores. Suppose the agent supports access to non-Oracle systems X, Y and Z. Each of these non-Oracle systems have different capabilities, support different SQL constructs, and have different data dictionary tables. As a result, the agent vendor will provide different class definitions to support non-Oracle system X, Y, and Z.

Important: Each agent will list the versions of non-Oracle datastores it can access. If the agent supports more than one non-Oracle datastore, or only supports different versions of the non-Oracle datastore, the vendor of the agent can supply multiple class definitions (with the proper capabilities and translations) with the agent.

Setting up access to Non-Oracle Systems

There are a number of steps that you must perform to access a particular non-Oracle system either using SQL or procedure calls. Note that some of the steps are agent-specific. Generally, the steps are:

1. Install the Heterogeneous Services Data Dictionary
2. Set up the agent executable
3. Install Class information
4. Register the instance to be accessed at the Oracle8 Server
5. Set instance-specific initialization parameters
6. Optionally, register distributed external procedures
7. Create the database link to the non-Oracle system
8. Test the connection

Install the Heterogeneous Services Data Dictionary

Make sure the data dictionary tables for Heterogeneous Services are installed. To install the data dictionary tables and views for Heterogeneous Services, you must run a script. 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 HS_FDS_CLASS.

Set Up the Agent executable

To initiate a connection to the non-Oracle system, the Oracle8 Server starts an agent process through the Net8 listener. The agent will set up the connection to the non-Oracle system.

See the Installation and User's Guide for your particular agent for information about how to set up the agent and listener.

Install Class Information

The class information contains capability and translation information for a particular type of non-Oracle system. This information can be loaded into the Oracle8 Server in two ways

• the agent vendor provides a script that loads the Oracle8 data dictionary with class information
• the agent can upload the class information when a connection is being established to the non-Oracle system.

Note: See the Installation and User's Guide for your agent for a description of how the class information is loaded.

For example, you just bought an agent to access MegaBase from an Oracle8 Server. The agent supports both MegaBase release 5 and MegaBase release 6. After running the script to install the class information, the data dictionary could look like this:

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 have been defined. 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 must have instance information for each non-Oracle system instance to be accessed. Each instance must first be registered.

Register the instance to be accessed at the Oracle8 Server

To register a non-Oracle system instance, you must use the procedure CREATE_FDS_INST in the DBMS_HS package. The procedure lets you specify the new instance name and the class to be used to access the non-Oracle system.

For example, suppose you need to access the Sales database on the MegaBase release 6 server.

You would register the Sales database as follows:

DBMS_HS.CREATE_FDS_INST

            (FDS_INST_NAME     => `SalesDB',

             FDS_CLASS_NAME    => `MegaBase6',

             FDS_INST_COMMENTS => `Sales DB in New England');

Register Distributed External Procedures

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 to register those distributed external procedures in the Oracle8 Server.

Note: You may not need to use the distributed external procedures to execute stored procedures in the non-Oracle system . For some non-Oracle systems a dynamic mapping mechanism is used, in the agent, to execute stored procedures. These procedures don't have to be registered.

See Also: See the Installation and User's Guide for your agent for more information on how to register distributed external procedures.

Set Initialization Parameters

You can set the following initialization parameters for each instance:

• 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 kinds of initialization parameters must be set 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      => `SALES');

Create the Database Link to the Non-Oracle System

Setting up a database link to a non-Oracle system, is the same as setting up a database link to another Oracle server. 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. The service name contains information about the remote server (location of the server and the name of the server).

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. To access non-Oracle systems, additional information needs to be added to the description of this service in one of these three facilities. The information that needs to be added is agent-specific. See the Installation and User's Guide for your agent for information on how to define the Net8 service name.

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

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';

The MegaBase_sales service name could be defined the TNSNAMES.ORA file as follows:

MegaBase6_sales= (DESCRIPTION=

                     (ADDRESS=(PROTOCOL=tcp)

                              (HOST=dlsun206)

                              (PORT=1521)

                     (CONNECT_DATA=

                         (SID=SalesDB))

                     (HS=

                         (AGENT_LOCATION=remote)

                         (AGENT_ID=MegaBase6

                         (FDS_ID=SalesDB)))

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%';

If distributed external procedures are used 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: 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.

The Data Dictionary Views

Heterogeneous Services data dictionary views in the Oracle8 Server can be divided into three groups:

• General views
• Views used for the SQL service
• Views used for distributed external procedures

Most of the information is defined at the class- and instance-level. Consequently, most views have a "..._CLASS" and "..._INST" view defined. The class information is defined by the agent vendor. The instance information is defined by you or your DBA.

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 Appendix B, DBMS_HS Package Reference.

See Also : The Oracle8 Server Reference for more detailed information about these views.

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

General Data Dictionary Views for Heterogeneous Services

To find out which classes are defined, you can query the view HS_FDS_CLASS. Each class has one entry in HS_FDS_CLASS, and is defined by the agent vendor. The instances that are defined by you or your DBA, are viewable in HS_FDS_INST.

See Also: "Non-Oracle System Classes and Instances" on page 7-2 for more information about classes and instances.

The HS_CLASS_INIT and HS_INST_INIT show the initialization parameters defined for each class and instance respectively. The class-level initialization parameters are defined by the agent vendor, and instance-level initialization parameters are defined by you or your DBA.

When a particular initialization parameter is defined at both the class-level and the instance-level, the instance-level initialization parameter value will overrule the initialization parameter value at the class-level.

The HS_ALL_INITS is a view that shows both instance-level and class-level initialization parameters.

See Also: See Chapter A, "Heterogeneous Services Initialization Parameters" for more information on initialization parameters.

Transaction Capabilities of the Non-Oracle System

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 non-Oracle system (and agent) can support one of the following five transaction capabilities.

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.

Note: Heterogeneous distributed transactions, i.e. transactions that access or manipulate non-Oracle systems using Heterogeneous Services, have the same issues and resolutions as homogeneous distributed transactions, i.e. transaction that executed completely across Oracle database servers only. 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.

Data Dictionary Views for the SQL Service

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

• SQL capabilities or possible 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. These capabilities are defined by the vendor that provides the driver. By running a PL/SQL script, the data dictionary will be populated with the non-Oracle data source capabilities and 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.

Note: Direct modification of existing translations and capabilities is not supported by Oracle Corporation. Changing translations could lead to incorrect results and decreased performance as well as other undesired behavior.

Views for Data Dictionary Translations

In order to make the non-Oracle system appear as an Oracle8 Server, Heterogeneous Services emulates the Oracle data dictionary tables as if they reside at the non-Oracle system by using data dictionary translations. These translations are stored in the HS_..._DD views. These translations are defined by the agent provider.

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. See the Oracle8 Server Reference for more information.

Data Dictionary 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

• register, de-register or copy non-Oracle system instances in the Oracle8 Server
• create or drop initialization parameters.

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

Table 7-2:

DBMS_HS procedures

Copying Instances

The COPY_INST procedure in the DBMS_HS package can be used to create a new instance definition from an existing instance definition. This procedure is useful when you are accessing a particular non-Oracle system instance, and want to access the another instance of the same class.

Security for Distributed External Procedures

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

Prev Next

Copyright © 1997 Oracle Corporation. All Rights Reserved.

Library Product Contents Index