Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Introduction

The Heterogeneous Challenge

The Heterogeneous Services Module in the Oracle Database Server

Integrating Heterogeneous Services Into the Oracle Server

Benefits of Heterogeneous Services

Remote Data Access

Elimination of Unnecessary Data Duplication

Heterogeneous Database Integration

Application Development and End User Tools

Two-Phase Commit and Multi-Site Transactions

Query Optimization

Error Mapping and Logging

Pass-Through Feature

2 Oracle Transparent Gateways and Generic Connectivity

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

Initialization Parameters

Capabilities

The Heterogeneous Services Data Dictionary

Classes and Instances

Data Dictionary Views

Gateway Process Flow

Oracle Transparent Gateways for Non-Oracle Database Systems

3 Major Features

SQL and PL/SQL Support

Heterogeneous Replication

Passthrough SQL

Using the DBMS_HS_PASSTHROUGH package

Considering the Implications of Using Pass-Through SQL

Executing Pass-Through SQL Statements

Executing Non-Queries

Executing Queries

Result Set Support

Introduction

Result Set Support In Non-Oracle Systems:

Model 1

Model 2

Heterogeneous Services Support for Result Sets

Cursor mode

Sequential Mode

Code Examples:

OCI program fetching from result sets in cursor mode

OCI program fetching from result sets in sequential mode

PL/SQL program fetching from result sets in cursor mode

Data Dictionary Translations

Examples

Date Time

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

Using Index and Table Statistics

Other Optimizations

Remote Join Optimization

Optimizer Restrictions for non-Oracle Access

4 Using the Gateway

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

A Sample Entry for a Oracle Net Service Name

A Sample Listener Entry

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

Step 4: Test the Connection

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

Using Agent Self-Registration to Avoid Configuration Mismatches

Understanding Agent Self-Registration

Specifying HS_AUTOREGISTER

Disabling Agent Self-Registration

Oracle Database Server SQL Construct Processing

Using Synonyms

Example of a Distributed Query

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 Views for Capabilities and Translations

Using Views for Data Dictionary Translations

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 Using Multithreaded Agents

Concepts

The Challenge of Dedicated Agent Architecture

The Advantage of Multithreading

Multithreaded Agent Architecture

Overview

The Monitor Thread

Dispatcher Threads

Task Threads

Multithreaded Agent Administration

Overview

Single Command Mode Commands

Shell Mode Commands

6 Performance Tips

Optimizing Heterogeneous Distributed SQL Statements

Using Gateways and Partition Views

Optimizing Performance of Distributed Queries

Choose the best SQL statement.

Use the cost-based optimizer.

Use views.

7 Generic Connectivity

What Is Generic Connectivity?

Types of Agents

Generic Connectivity Architecture

Oracle and Non-Oracle Systems on Separate Machines

Oracle and Non-Oracle Systems on the Same Machine

SQL Execution

Data Type Mapping

Generic Connectivity Restrictions

Supported Oracle SQL Statements

Functions Supported by Generic Connectivity

Configuring Generic Connectivity Agents

Creating the Initialization File

Editing the Initialization File

Setting Initialization Parameters for an ODBC-based Data Source

Setting Agent Parameters on Windows NT

Setting Agent Parameters on UNIX platforms

Setting Initialization Parameters for an OLE DB-based Data Source

ODBC Connectivity Requirements

OLE DB (SQL) Connectivity Requirements

OLE DB (FS) Connectivity Requirements

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

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

BIND_VARIABLE procedure

Syntax.

Parameters

Exceptions

Pragmas

BIND_VARIABLE_NCHAR procedure

Syntax

Parameters

Exceptions

Pragmas

BIND_VARIABLE_RAW procedure

Syntax

Parameters

Exceptions

Pragmas

BIND_OUT_VARIABLE procedure

Syntax

Parameters

Exceptions

Pragmas

BIND_OUT_VARIABLE_NCHAR procedure

Syntax

Parameters

Exceptions

Pragmas

BIND_OUT_VARIABLE_RAW procedure

Syntax

Parameters

Exceptions

Pragmas

BIND_INOUT_VARIABLE procedure

Syntax

Parameters

Exceptions

Pragmas

BIND_INOUT_VARIABLE_NCHAR procedure

Syntax

Parameters

Exceptions

Pragmas

BIND_INOUT_VARIABLE_RAW procedure

Syntax

Parameters

Exceptions

Pragmas

CLOSE_CURSOR function

Syntax

Parameter

Exceptions

Pragmas

EXECUTE_IMMEDIATE function

Syntax

Parameter Description

Returns

Exceptions

Pragmas

EXECUTE_NON_QUERY function

Syntax

Parameter

Returns

Exceptions

Pragmas

FETCH_ROW function

Syntax

Parameters and Descriptions

Returns

Exceptions

Pragmas

GET_VALUE procedure

Syntax

Parameters

Exceptions

Pragmas

GET_VALUE_NCHAR procedure

Syntax

Parameters

Exceptions

Pragmas

GET_VALUE_RAW procedure

Syntax

Parameters

Exceptions

Pragmas

OPEN_CURSOR function

Syntax

Returns

Exceptions

Pragmas

PARSE procedure

Syntax

Parameters

Exceptions

Pragmas

D Data Dictionary Translation Support

Accessing the Non-Oracle Data Dictionary

Heterogeneous Services Data Dictionary Views

Supported Views and Tables

Data Dictionary Mapping

Generic Connectivity Data Dictionary Descriptions

ALL_CATALOG

ALL_COL_COMMENTS

ALL_CONS_COLUMNS

ALL_CONSTRAINTS

ALL_IND_COLUMNS

ALL_INDEXES

ALL_OBJECTS

ALL_TAB_COLUMNS

ALL_TAB_COMMENTS

ALL_TABLES

ALL_USERS

ALL_VIEWS

DICTIONARY

USER_CATALOG

USER_COL_COMMENTS

USER_CONS_COLUMNS

USER_CONSTRAINTS

USER_IND_COLUMNS

USER_INDEXES

USER_OBJECTS

USER_TAB_COLUMNS

USER_TAB_COMMENTS

USER_TABLES

USER_USERS

USER_VIEWS