Contents

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Heterogeneous Connectivity User's Guide?

• Oracle Database 11g Release 2 (11.2) New Features
• Oracle Database 11g Release 1 (11.1) New Features

1 Introduction

• 1.1 The Information Integration Challenge
• 1.2 How Oracle Addresses Synchronous Information Integration
• 1.3 Benefits of Oracle's Solution for Synchronous Information Integration
  • 1.3.1 Remote Data Can Be Accessed Transparently
  • 1.3.2 No Unnecessary Data Duplication
  • 1.3.3 SQL Statements Can Query Several Different Databases
  • 1.3.4 Oracle's Application Development and End User Tools Can Be Used
  • 1.3.5 Users Can Communicate With a Remote Database in its Own Language

2 The Role of the Heterogeneous Services Component

• 2.1 Heterogeneous Connectivity Process Architecture
• 2.2 Heterogeneous Services Agents
• 2.3 Types of Heterogeneous Services Agents
  • 2.3.1 Oracle Database Gateways
  • 2.3.2 Oracle Database Gateway for ODBC Agent
• 2.4 Heterogeneous Services Components
  • 2.4.1 Transaction Service
  • 2.4.2 SQL Service
• 2.5 Heterogeneous Services Configuration Information
  • 2.5.1 Data Dictionary Translation Views
  • 2.5.2 Heterogeneous Services Initialization Parameters
  • 2.5.3 Capabilities
• 2.6 The Heterogeneous Services Data Dictionary
  • 2.6.1 Classes and Instances
  • 2.6.2 Data Dictionary Views
• 2.7 Gateway Process Flow

3 Features of Oracle Database Gateways

• 3.1 SQL and PL/SQL Support
• 3.2 Heterogeneous Replication
• 3.3 Passthrough SQL
  • 3.3.1 Using the DBMS_HS_PASSTHROUGH Package
  • 3.3.2 Considering the Implications of Using Passthrough SQL
  • 3.3.3 Executing Passthrough SQL Statements
    • 3.3.3.1 Executing Nonqueries
      • 3.3.3.1.1 Using Bind Variables: Overview
      • 3.3.3.1.2 Using IN Bind Variables
      • 3.3.3.1.3 Using OUT Bind Variables
      • 3.3.3.1.4 Using IN OUT Bind Variables
    • 3.3.3.2 Executing Queries
• 3.4 Result Set Support
  • 3.4.1 Result Set Support for Non-Oracle Systems
  • 3.4.2 Heterogeneous Services Support for Result Sets
    • 3.4.2.1 Results Sets: Cursor Mode
    • 3.4.2.2 Result Sets: Sequential Mode
• 3.5 Data Dictionary Translations
• 3.6 Date-Time Data Types
• 3.7 Two-Phase Commit Protocol
• 3.8 Piecewise LONG Data Type
• 3.9 SQL*Plus DESCRIBE Command
• 3.10 Constraints on SQL in a Distributed Environment
  • 3.10.1 Remote and Heterogeneous References
  • 3.10.2 Important Restrictions
• 3.11 Oracle's Optimizer and Heterogeneous Services
  • 3.11.1 Example: Using Index and Table Statistics
  • 3.11.2 Example: Remote Join Optimization
  • 3.11.3 Optimizer Restrictions for Non-Oracle Access

4 Using Heterogeneous Services Agents

• 4.1 Initialization Parameters
  • 4.1.1 Encrypting Initialization Parameters
    • 4.1.1.1 Using the dg4pwd Utility
  • 4.1.2 Gateway Initialization Parameters
• 4.2 Optimizing Data Transfers Using Bulk Fetch
  • 4.2.1 Using OCI, an Oracle Precompiler, or Another Tool for Array Fetches
  • 4.2.2 Controlling the Array Fetch Between Oracle Database and the Agent
  • 4.2.3 Controlling the Array Fetch Between the Agent and the Non-Oracle System
  • 4.2.4 Controlling the Reblocking of Array Fetches
• 4.3 Optimizing Data Loads Using Parallel Load
• 4.4 Registering Agents
  • 4.4.1 Enabling Agent Self-Registration
    • 4.4.1.1 Using Agent Self-Registration to Avoid Configuration Mismatches
    • 4.4.1.2 Understanding Agent Self-Registration
      • 4.4.1.2.1 FDS_CLASS and FDS_CLASS_VERSION
      • 4.4.1.2.2 FDS_INST_NAME
    • 4.4.1.3 Specifying HS_AUTOREGISTER
  • 4.4.2 Disabling Agent Self-Registration
• 4.5 Oracle Database Server SQL Construct Processing
  • 4.5.1 Data Type Checking Support for a Remote-Mapped Statement
• 4.6 Executing User-Defined Functions on a Non-Oracle Database
• 4.7 Using Synonyms
  • 4.7.1 Example of a Distributed Query
• 4.8 Copying Data from the Oracle Database Server to the Non-Oracle Database System
• 4.9 Copying Data from the Non-Oracle Database System to the Oracle Database Server
• 4.10 Using Heterogeneous Services Data Dictionary Views
  • 4.10.1 Understanding the Types of Views
  • 4.10.2 Understanding the Sources of Data Dictionary Information
  • 4.10.3 Using the General Views
  • 4.10.4 Using the Transaction Service Views
  • 4.10.5 Using the SQL Service Views
    • 4.10.5.1 Using Views for Capabilities and Translations
    • 4.10.5.2 Using Views for Data Dictionary Translations
• 4.11 Using the Heterogeneous Services Dynamic Performance Views
  • 4.11.1 Determining Which Agents Are Running on a Host: V$HS_AGENT View
  • 4.11.2 Determining the Open Heterogeneous Services Sessions: V$HS_SESSION View
  • 4.11.3 Determining the Heterogeneous Services Parameters: V$HS_PARAMETER View

5 Performance Recommendations

• 5.1 Optimizing Heterogeneous Distributed SQL Statements
• 5.2 Optimizing Performance of Distributed Queries

Index