4 Using Heterogeneous Services Agents

4.1.1 Step 1: Configure Oracle Net Services to Access Heterogeneous Services Agents

To initiate a connection to the non-Oracle system, the Oracle server starts an agent process through the Oracle Net listener. For the Oracle server to be able to connect to the agent, you must perform the following tasks:

1. Set up an Oracle Net service name for the agent that can be used by the Oracle server. The Oracle Net service name descriptor includes protocol-specific information needed to access the Oracle Net listener. The service name descriptor must include the (HS=OK) clause to ensure that the connection uses Oracle Heterogeneous Services. 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.

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

   Sybase_sales= (DESCRIPTION=
                         (ADDRESS=(PROTOCOL=tcp)
                                  (HOST=sales)
                                  (PORT=1521)
                         )
                         (CONNECT_DATA = (SERVICE_NAME=SalesDB)
                         )
                         (HS = OK)
                 )
   
   

2. Set up the listener on the gateway to listen for incoming requests from the Oracle server and spawn Heterogeneous Services agents. Then, start the listener on the gateway system.

   The following is a sample entry for the listener in listener.ora:

LISTENER =
       (ADDRESS_LIST =
          (ADDRESS= (PROTOCOL=tcp)
                    (HOST = sales)
                    (PORT = 1521)
          )
      )
    ... 
    SID_LIST_LISTENER = 
      (SID_LIST = 
          (SID_DESC = (SID_NAME=SalesDB)
                      (ORACLE_HOME=/home/oracle/megabase/9.0.1)
                      (PROGRAM=tg4mb80)
                      (ENVS=LD_LIBRARY_PATH=non_oracle_system_lib_directory)
          )
      )
 

The value associated with the PROGRAM keyword defines the name of the agent executable. The full path of the directory which contains the DLLs that are loaded by the Heterogeneous Services agent is specified by LD_LIBRARY_PATH. You must use the same SID_NAME as used in the name of the initialization parameter file for the agent.

4.1.2 Step 2: 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 statement is the Oracle Net service name.

Use quotes with the username and password to avoid differences in case-sensitive behavior between Oracle and non-Oracle databases. For example:

CREATE DATABASE LINK sales
CONNECT TO "sales1"
IDENTIFIED BY "Sales1"
USING 'Sybase_sales';

4.1.3 Step 3: 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:

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

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

4.2.1 Name and Location of Heterogeneous Services Initialization Parameter File

The name of the file is initsid.ora, where sid is the Oracle system identifier used for the gateway.

In the case of Generic Connectivity, the Heterogeneous Services initialization file is located in the directory $ORACLE_HOME/hs/admin. In the case of Transparent Gateways, it is located in the directory $ORACLE_HOME/product_name/admin where product_name reflects the name of the product. For example, the Sybase gateway initialization file is located in the directory $ORACLE_HOME/tg4sybs/admin.

4.2.2 Syntax for Initialization Parameter Settings

The initialization file contains a list of initialization parameter settings each of which should be on a separate line. The syntax to set an initialization parameter is as follows:

[SET] [PRIVATE] parameter = parameter_value

The SET and PRIVATE keywords are optional. If the SET keyword is present, the variable is also set in the environment. If the PRIVATE keyword is present, the parameter is not uploaded to the server. Oracle recommends that you do not use the PRIVATE keyword unless the initialization parameter value contains sensitive information (like a password) that should not be sent over the network from the gateway to the Oracle server.

In the initialization parameter syntax, all keywords (SET, PRIVATE, and IFILE) are case-insensitive. Initialization parameter names and values are case-sensitive. Most initialization parameter names are uppercase. String values for Heterogeneous Services parameters must be lowercase. Exceptions to this rule are explicitly noted.

Another initialization file can be included in an Heterogeneous Services initialization file by using the IFILE directive. The syntax for this is as follows:

IFILE = path_name

4.2.3 Encrypting Initialization Parameters

Initialization parameters may contain sensitive information, such as user IDs or passwords. Initialization parameters are stored in plain text files and are insecure. An encryption feature has been added to Heterogeneous Services making it possible to encrypt parameter values. This is done through the tg4pwd utility. To use this feature requires setting the value of a parameter in the initialization file to an unquoted asterisk (*). For example:

HD_FDS_CONNECT_INFO = *

With the value set to this security marker, all Heterogeneous Services agents know that the real value will be stored in a related, encrypted password file. The name of this file will be initsid.pwd, where sid is the Oracle system identifier used for the gateway. This file is created by the tg4pwd utility in the current directory containing the initialization file. Running the utility prompts for the real value of the parameter, which the utility will encrypt and store in the password file. It should be noted that encrypted initialization parameters are implicitly treated as PRIVATE parameters and are not uploaded to the server.

HS_FDS_CONNECT_INFO = *

tg4pwd [sid] {userid}

tg4pwd SYBASE
ORACLE Gateway Password Utility
Constructing password file for Gateway SID SYBASE
Enter the value for HS_FDS_CONNECT_INFO
sybasew

4.2.4 Gateway Initialization Parameters

Gateway initialization parameters can be divided into two groups. One is a set of generic initialization parameters that are common to all gateways and the other is a set of initialization parameters that are specific to individual gateways. The following generic initialization parameters are the only initialization parameters discussed in this document:

Do not use the PRIVATE keyword when setting any of these parameters. Using the PRIVATE keyword prevents the parameter from being uploaded to the server and can cause errors in SQL processing. None of these parameters needs to be set in the environment, so you do not need to use the SET keyword.

4.3.1 Using OCI, an Oracle Precompiler, or Another Tool for Array Fetches

You can optimize data transfers between your application and the Oracle server by using array fetches. See your application development tool documentation for information about array fetching and how to specify the amount of data to be sent and each network round-trip.

4.3.2 Controlling the Array Fetch Between the Oracle Database Server and the Agent

When Oracle retrieves data from a non-Oracle system, the Heterogeneous Services initialization parameter, HS_RPC_FETCH_SIZE, defines the number of bytes sent for each fetch between the agent and the Oracle server. The agent fetches data from the non-Oracle system until one of the following occurs:

• It has accumulated the specified number of bytes to send back to the Oracle database server.

• The last row of the result set is fetched from the non-Oracle system.

4.3.3 Controlling the Array Fetch Between Agent and Non-Oracle Server

The initialization parameter, HS_FDS_FETCH_ROWS, determines the number of rows to be retrieved from a non-Oracle system. Note that the array fetch must be supported by the agent. See your agent-specific documentation to ensure that your agent supports array fetching.

4.3.4 Controlling the Reblocking of Array Fetches

By default, an agent fetches data from the non-Oracle system until it has enough data retrieved to send back to the server. It continues until the number of bytes fetched from the non-Oracle system is equal to or higher than the value of HS_RPC_FETCH_SIZE initialization parameter. In other words, the agent reblocks the data between the agent and the Oracle database server in sizes defined by the value of the HS_RPC_FETCH_SIZE initialization parameter.

When the non-Oracle system supports array fetches, you can immediately send the data fetched from the non-Oracle system by the array fetch to the Oracle database server without waiting until the exact value of the HS_RPC_FETCH_SIZE initialization parameter is reached. That is, you can stream the data from the non-Oracle system to the Oracle database server and disable reblocking by setting the value of the HS_RPC_FETCH_REBLOCKING initialization parameter to OFF.

For example, assume that you set HS_RPC_FETCH_SIZE to 64 kilobytes (KB) and HS_FDS_FETCH_ROWS to 100 rows. Also assume that each row is approximately 600 bytes in size, so that the 100 rows are approximately 60 KB. When the HS_RPC_FETCH_REBLOCKING initialization parameter is set to ON, the agent starts fetching 100 rows from the non-Oracle system.

Because there is only 60 KB of data in the agent, the agent does not send the data back to the Oracle database server. Instead, the agent fetches the next 100 rows from the non-Oracle system. Because there is now 120 KB of data in the agent, the first 64 KB can be sent back to the Oracle database server.

Now there is 56 KB of data left in the agent. The agent fetches another 100 rows from the non-Oracle system before sending the next 64 KB of data to the Oracle database server. By setting the HS_RPC_FETCH_REBLOCKING initialization parameter to OFF, the first 100 rows are immediately sent back to the Oracle database server.

4.4.1 Enabling Agent Self-Registration

To ensure correct operation over heterogeneous database links, agent self-registration automates updates to Heterogeneous Services 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, set the HS_AUTOREGISTER initialization parameter to FALSE.

Both the server and the agent rely on three types of information to configure and control operation of the Heterogeneous Services connection. These three sets of information are collectively called HS configuration data:

HS_AUTOREGISTER = TRUE

4.4.2 Disabling Agent Self-Registration

To disable agent self-registration, set the HS_AUTOREGISTER initialization parameter as follows:

HS_AUTOREGISTER = FALSE

Disabling agent self-registration means that agent information is not stored in the data dictionary. Consequently, the Heterogeneous Services data dictionary views are not useful sources of information. Nevertheless, the Oracle server still requires information about the class and instance of each agent. If agent self-registration is disabled, the server stores this information in local memory.

4.5.1 Data Type Checking Support for a Remote-Mapped Statement

The Oracle database has always performed data type checking and data type coercion in a homogeneous environment. For example, SELECT * FROM EMP WHERE EMPNO='7934' would return the same result as SELECT * FROM EMPNO WHERE EMPNO=7934. There is also full data type checking support for remote-mapped statements in a heterogeneous environment. In general, the operands in SQL statements whether its a column, literal, or bind variable would be processed internally for data type checking. Consider the following examples:

SELECT * FROM EMP@LINK WHERE NUMBER_COLUMN='123'
SELECT * FROM EMP@LINK WHERE NUMBER_COLUMN=CHAR_COLUMN;
SELECT * FROM EMP@LINK WHERE NUMBER_COLUMN=CHAR_BIND_VARIABLE;

Most non-Oracle databases do not support data type coercion, and the previous statements fail if they are sent to a non-Oracle database as is. The Heterogeneous Services component for the Oracle database performs data type checking and the necessary data type coercion before sending an acceptable statement to a non-Oracle database.

Data type checking provides consistent behavior on post-processed or remote-mapped statements. Consider the following two statements:

SELECT * FROM EMP@LINK WHERE TO_CHAR(EMPNO)='7933' + '1';

And:

SELECT * FROM EMP@LINK WHERE EMPNO='7933' + '1';

Both of the previous statements provide the same result and coercion regardless if the TO_CHAR function is supported in the non-Oracle database or not. Now, consider the following statement:

SELECT * FROM EMP@LINK WHERE EMPNO='123abc' + '1';

As data type checking is enforced, the coercion attempt within Oracle generates an error and returns it without sending any statements to a non-Oracle database.

In summary, there is consistent data type checking and coercion behavior regardless of post-processed or remote-mapped statements.

4.7.1 Example of a Distributed Query

The following statement joins data between the Oracle database server, an IBM DB2 database, and a Sybase database:

SELECT O.CUSTNAME, P.PROJNO, E.ENAME, SUM(E.RATE*P."HOURS")
       FROM ORDERS@DB2 O, EMP@ORACLE9 E, "PROJECTS"@SYBS P
       WHERE O.PROJNO = P."PROJNO"
          AND P."EMPNO" = E.EMPNO
       GROUP BY O.CUSTNAME, P."PROJNO", E.ENAME;

Through a combination of views and synonyms, using the following SQL statements, the process of distributed queries is transparent:

CREATE SYNONYM ORDERS FOR ORDERS@DB2;
CREATE SYNONYM PROJECTS FOR "PROJECTS"@SYBS;
CREATE VIEW DETAILS (CUSTNAME,PROJNO,ENAME,SPEND)
      AS 
      SELECT O.CUSTNAME, P."PROJNO", E.ENAME, SUM(E.RATE*P."HOURS")
      SPEND
      FROM ORDERS O, EMP E, PROJECTS P
      WHERE O.PROJNO = P."PROJNO"
      AND P."EMPNO" = E.EMPNO
      GROUP BY O.CUSTNAME, P."PROJNO", E.ENAME;

Use the following SQL statement to retrieve information from the data stores in one statement:

SELECT * FROM DETAILS;

The statement retrieves the following table:

CUSTNAME         PROJNO           ENAME          SPEND
--------         ------           -----          -----
ABC Co.             1             Jones            400
ABC Co.             1             Smith            180
XYZ Inc.            2             Jones            400
XYZ Inc.            2             Smith            180

4.10.1 Understanding the Types of Views

The Heterogeneous Services data dictionary views, whose names all begin with the HS_ prefix, can be divided into the following categories:

• General views

• Views used for the transaction service

• Views used for the SQL service

Most of the data dictionary views are defined for both classes and instances. For most types of data there is a *_CLASS view and a *_INST view. See Table 4-1 for additional details.

Like all Oracle data dictionary tables, these views are read-only. Do not change the content of any of the underlying tables.

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

• Instance information uploaded by the connected Heterogeneous Services agent at the start of the session. This information overrides corresponding content in the Oracle data dictionary, but is never stored into the Oracle data dictionary.

• Instance information stored in the Oracle data dictionary. This data overrides any corresponding content for the connected class.

• Class information stored in the Oracle data dictionary.

If the Oracle database server runs with the HS_AUTOREGISTER server initialization parameter set to FALSE, then information is not stored automatically in the Oracle data dictionary. The equivalent data is uploaded by the Heterogeneous Services agent on a connection-specific basis each time a connection is made, with any instance-specific information taking precedence over class information.

You can determine the values of Heterogeneous Services 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. It truncates the parameter name from a maximum of 64 characters to a maximum of 30 characters.

4.10.3 Using the General Views

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

For example, you can access multiple Sybase gateways from an Oracle database server. After accessing the gateways for the first time, the information uploaded into the Oracle database server could appear as follows:

SQL> SELECT * FROM HS_FDS_CLASS; 

FDS_CLASS_NAME        FDS_CLASS_COMMENTS             FDS_CLASS_ID
--------------------- ------------------------------ ------------
Sybase816             Uses Sybase driver, R1.1                 1
Sybase817             Uses Sybase driver, R1.2                21



Two classes are uploaded: a class that accesses Sybase816 and a class that accesses Sybase817. The data dictionary in the Oracle database server now contains capability information, SQL translations, and data dictionary translations for both Sybase816 and Sybase817.


The Oracle database server data dictionary also contains instance information in the HS_FDS_INST view for each non-Oracle system instance that is accessed.

4.10.4 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 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 types shown in the following table.

The transaction model supported by the driver and non-Oracle system can be queried from the HS_CLASS_CAPS Heterogeneous Services data dictionary view.

The following example shows 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 LIKE 'SYBASE%';

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

4.10.5 Using the SQL Service Views

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

  Note:

  This section describes only a portion of the SQL Service-related capabilities. Because you should never need to alter these settings for administrative purposes, these capabilities are not discussed here.

SELECT DD_TABLE_NAME, TRANSLATION_TYPE
FROM   HS_CLASS_DD
WHERE  FDS_CLASS_NAME LIKE 'SYBASE%';

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

4.11.1 Determining Which Agents Are Running on a Host: V$HS_AGENT View

The V$HS_AGENT view identifies the set of Heterogeneous Services agents currently operating on a specified host. Table 4-2 shows the most relevant columns. For a description of all the columns in the view, see Oracle Database Reference.

4.11.2 Determining the Open Heterogeneous Services Sessions: V$HS_SESSION View

The V$HS_SESSION view shows the sessions for each agent and specifies the database link that is used. Table 4-3 shows the most relevant columns. For a description of all the columns in the view, see Oracle Database Reference.

4.11.3 Determining the Heterogeneous Services Parameters: V$HS_PARAMETER View

The V$HS_PARAMETER view lists the Heterogeneous Services parameters and their values that are registered in the Oracle database server. Table 4-4 shows the most relevant columns. For a description of all the columns in the view, see Oracle Database Reference.

Information about the database link that was used for establishing the distributed connection, the startup time, and the set of initialization parameters used for the session is also available. All of the runtime information is derived from dynamically updated tables.