Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience

Organization

Related Documentation

Conventions

Documentation Accessibility

1 Introduction

The Information Integration Challenge

How Oracle Addresses Synchronous Information Integration

Benefits of Oracle's Solution for Synchronous Information Integration

Remote Data Can Be Accessed Transparently

There is No Unnecessary Data Duplication

SQL Statements Can Query Several Different Databases

Oracle's Application Development and End User Tools Can Be Used

Users Can Talk to a Remote Database in its Own Language

2 The Role of the Heterogeneous Services Component

Heterogeneous Connectivity Process Architecture

Heterogeneous Services Agents

Types of Heterogeneous Services Agents

Oracle Transparent Gateways

Generic Connectivity

Heterogeneous Services Components

Transaction Service

SQL Service

Configuring Heterogeneous Services

Data Dictionary Translations

Heterogeneous Services Initialization Parameters

Capabilities

The Heterogeneous Services Data Dictionary

Classes and Instances

Data Dictionary Views

Gateway Process Flow

3 Features of Oracle Transparent Gateways and Generic Connectivity

SQL and PL/SQL Support

Heterogeneous Replication

Pass-through SQL

Using the DBMS_HS_PASSTHROUGH Package

Considering the Implications of Using Pass-Through SQL

Executing Pass-Through SQL Statements

Result Set Support

Result Set Support In Non-Oracle Systems

Heterogeneous Services Support for Result Sets

Code Examples Using Result Sets

Data Dictionary Translations

Examples of Data Dictionary Queries

Datetime Data Types

Two Phase Commit Protocol

Piecewise Long

SQL*Plus Describe Command

Constraints on SQL in a Distributed Environment

Resolving Remote and Heterogeneous References

Resolving Important Restrictions

Updates, Inserts and Deletes

Optimization

Example of Using Index and Table Statistics

Example of Remote Join Optimization

Optimizer Restrictions for Non-Oracle Access

4 Using Heterogeneous Services Agents

Setting Up Access to Non-Oracle Systems

Step 1: Install the Heterogeneous Services Data Dictionary

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

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

Step 4: Test the Connection

Setting Initialization Parameters

Name and Location of Heterogeneous Services Initialization Parameter File

Syntax for Initialization Parameter Settings

Initialization Parameters

Optimizing Data Transfers Using Bulk Fetch

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

Controlling the Array Fetch Between Oracle Database Server and Agent

Controlling the Array Fetch Between Agent and Non-Oracle Server

Controlling the Reblocking of Array Fetches

Registering Agents

Enabling Agent Self-Registration

Disabling Agent Self-Registration

Oracle Database Server SQL Construct Processing

Using Synonyms

Copying Data from the Oracle Database Server to the Non-Oracle Database System

Copying Data from the Non-Oracle Database System to the Oracle Database Server

Heterogeneous Services Data Dictionary Views

Understanding the Types of Views

Understanding the Sources of Data Dictionary Information

Using the General Views

Using the Transaction Service Views

Using the SQL Service Views

Using the Heterogeneous Services Dynamic Performance Views

Determining Which Agents Are Running on a Host

Determining the Open Heterogeneous Services Sessions

Determining the Heterogeneous Services Parameters

5 Multithreaded Agents

Why Use Multithreaded Agents?

The Challenge of Dedicated Agent Architecture

The Advantage of Multithreading

Multithreaded Agent Architecture

The Monitor Thread

Dispatcher Threads

Task Threads

Administering Multithreaded Agents

Agent Control Utility (agtctl) Commands

Using Single-Line Command Mode

Using Shell Mode Commands

Configuration Parameters for Multithreaded Agent Control

6 Performance Tips

Optimizing Heterogeneous Distributed SQL Statements

Using Gateways and Partition Views

Optimizing Performance of Distributed Queries

7 Generic Connectivity

What Is Generic Connectivity?

Types of Agents

Generic Connectivity Architecture

SQL Execution

Data Type Mapping

Generic Connectivity Restrictions

Supported Oracle SQL Statements and Functions

Configuring Generic Connectivity Agents

Creating the Initialization File

Editing the Initialization File

Setting Initialization Parameters for an ODBC-based Data Source

Setting Initialization Parameters for an OLE DB-based Data Source

ODBC Connectivity Requirements

OLE DB (SQL) Connectivity Requirements

OLE DB (FS) Connectivity Requirements

OLE DB Interfaces for Data Providers to Expose

Data Source Properties

A Heterogeneous Services Initialization Parameters

HS_COMMIT_POINT_STRENGTH

HS_DB_DOMAIN

HS_DB_INTERNAL_NAME

HS_DB_NAME

HS_DESCRIBE_CACHE_HWM

HS_FDS_CONNECT_INFO

ODBC-based Data Source on Windows

ODBC-based Data Source on UNIX

OLE DB-based Data Source (Windows NT Only)

HS_FDS_DEFAULT_SCHEMA_NAME

HS_FDS_SHAREABLE_NAME

HS_FDS_TRACE_LEVEL

HS_LANGUAGE

Character Sets

Language

Territory

HS_LONG_PIECE_TRANSFER_SIZE

HS_NLS_DATE_FORMAT

HS_NLS_DATE_LANGUAGE

HS_NLS_NCHAR

HS_NLS_TIMESTAMP_FORMAT

HS_NLS_TIMESTAMP_TZ_FORMAT

HS_OPEN_CURSORS

HS_ROWID_CACHE_SIZE

HS_RPC_FETCH_REBLOCKING

HS_RPC_FETCH_SIZE

HS_TIME_ZONE

IFILE

B Data Type Mapping

Mapping ANSI Data Types to Oracle Data Types Through an ODBC Interface

Mapping ANSI Data Types to Oracle Data Types Through an OLE DB Interface

C DBMS_HS_PASSTHROUGH for Pass-Through SQL

Summary of Subprograms

Subprogram Descriptions

BIND_VARIABLE Procedure

BIND_VARIABLE_NCHAR Procedure

BIND_VARIABLE_RAW Procedure

BIND_OUT_VARIABLE Procedure

BIND_OUT_VARIABLE_NCHAR Procedure

BIND_OUT_VARIABLE_RAW Procedure

BIND_INOUT_VARIABLE Procedure

BIND_INOUT_VARIABLE_NCHAR Procedure

BIND_INOUT_VARIABLE_RAW Procedure

CLOSE_CURSOR Function

EXECUTE_IMMEDIATE Function

EXECUTE_NON_QUERY Function

FETCH_ROW Function

GET_VALUE Procedure

GET_VALUE_NCHAR Procedure

GET_VALUE_RAW Procedure

OPEN_CURSOR Function

PARSE Procedure

D Data Dictionary Translation Support

Accessing the Non-Oracle Data Dictionary

Heterogeneous Services Data Dictionary Views

Views and Tables Supported by Generic Connectivity

Data Dictionary Mapping

Generic Connectivity Data Dictionary Descriptions

Index