Contents

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

Documentation Accessibility

Related Documents

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

Oracle Database 10g Release 2 (10.2) New Features

Oracle Database 10g Release 1 (10.1) 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 Talk to 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 Transparent Gateways

2.3.2 Generic Connectivity Agents

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 Transparent Gateways and Generic Connectivity

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.2 Executing Queries

3.4 Result Set Support

3.4.1 Result Set Support In Non-Oracle Systems

3.4.1.1 Model 1: Result Set Support

3.4.1.2 Model 2: Result Set Support

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 Datetime 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 Using Oracle's Optimizer with 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 Setting Up Access to Non-Oracle Systems

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

4.1.2 Step 2: Create the Database Link to the Non-Oracle System

4.1.3 Step 3: Test the Connection

4.2 Setting Initialization Parameters

4.2.1 Name and Location of Heterogeneous Services Initialization Parameter File

4.2.2 Syntax for Initialization Parameter Settings

4.2.3 Encrypting Initialization Parameters

4.2.3.1 Using the tg4pwd Utility

4.2.4 Gateway Initialization Parameters

4.3 Optimizing Data Transfers Using Bulk Fetch

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

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

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

4.3.4 Controlling the Reblocking of Array Fetches

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

5.1 Why Use Multithreaded Agents?

5.1.1 The Challenge of Dedicated Agent Architecture

5.1.2 The Advantage of Multithreading

5.2 Multithreaded Agent Architecture

5.2.1 Monitor Thread

5.2.2 Dispatcher Threads

5.2.3 Task Threads

5.3 Administering Multithreaded Agents

5.3.1 Agent Control Utility (agtctl) Commands

5.3.2 Using agtctl in Single-Line Command Mode

5.3.2.1 Setting Configuration Parameters for a Multithreaded Agent

5.3.2.2 Starting a Multithreaded Agent

5.3.2.3 Shutting Down a Multithreaded Agent

5.3.2.4 Examining the Value of Configuration Parameters

5.3.2.5 Resetting a Configuration Parameter to Its Default Value

5.3.2.6 Deleting an Entry for a Specific SID from the Control File

5.3.2.7 Requesting Help

5.3.3 Using Shell Mode Commands

5.3.3.1 Example: Setting a Configuration Parameter

5.3.3.2 Example: Starting a Multithreaded Agent

5.3.4 Configuration Parameters for Multithreaded Agent Control

6 Performance Recommendations

6.1 Optimizing Heterogeneous Distributed SQL Statements

6.2 Optimizing Performance of Distributed Queries

7 Generic Connectivity

7.1 What Is Generic Connectivity?

7.1.1 Types of Agents

7.1.2 Generic Connectivity Architecture

7.1.2.1 Oracle and Non-Oracle Systems on Separate Machines

7.1.2.2 Oracle and Non-Oracle Systems on the Same Machine

7.1.3 SQL Execution

7.1.4 Data Type Mapping

7.1.5 Generic Connectivity Restrictions

7.2 Supported Oracle SQL Statements and Functions

7.3 Configuring Generic Connectivity Agents

7.3.1 Creating the Initialization File

7.3.2 Editing the Initialization File

7.3.3 Setting Initialization Parameters for an ODBC-Based Data Source

7.3.3.1 Setting Agent Parameters on Windows

7.3.3.2 Setting Agent Parameters on UNIX Platforms

7.3.4 Setting Initialization Parameters for an OLE DB-Based Data Source

7.4 ODBC Connectivity Requirements

7.5 OLE DB (SQL) Connectivity Requirements

7.6 OLE DB (FS) Connectivity Requirements

7.6.1 OLE DB Interfaces for Data Providers to Expose

7.6.2 Data Source Properties

A Heterogeneous Services Initialization Parameters

A.1 HS_CALL_NAME

A.2 HS_COMMIT_POINT_STRENGTH

A.3 HS_DB_DOMAIN

A.4 HS_DB_INTERNAL_NAME

A.6 HS_DESCRIBE_CACHE_HWM

A.7 HS_FDS_CONNECT_INFO

A.7.1 ODBC-Based Data Source on Windows

A.7.2 ODBC-Based Data Source on UNIX

A.7.3 OLE DB-Based Data Source (Windows Only)

A.8 HS_FDS_DEFAULT_SCHEMA_NAME

A.9 HS_FDS_SHAREABLE_NAME

A.10 HS_FDS_TRACE_LEVEL

A.11 HS_LANGUAGE

A.11.1 Character Sets

A.11.2 Language

A.11.3 Territory

A.12 HS_LONG_PIECE_TRANSFER_SIZE

A.13 HS_NLS_DATE_FORMAT

A.14 HS_NLS_DATE_LANGUAGE

A.15 HS_NLS_NCHAR

A.16 HS_NLS_NUMERIC_CHARACTER

A.17 HS_NLS_TIMESTAMP_FORMAT

A.18 HS_NLS_TIMESTAMP_TZ_FORMAT

A.19 HS_OPEN_CURSORS

A.20 HS_ROWID_CACHE_SIZE

A.21 HS_RPC_FETCH_REBLOCKING

A.22 HS_RPC_FETCH_SIZE

A.23 HS_TIME_ZONE

B Data Type Mapping for Generic Connectivity

B.1 Mapping ANSI Data Types to Oracle Data Types Through an ODBC Interface

B.2 Mapping ANSI Data Types to Oracle Data Types Through an OLE DB Interface

C Data Dictionary Translation Support

C.1 Accessing the Non-Oracle Data Dictionary

C.2 Heterogeneous Services Data Dictionary Views

C.3 Views and Tables Supported by Generic Connectivity

C.3.1 Data Dictionary Mapping

C.3.2 Generic Connectivity Data Dictionary Descriptions

C.3.2.1 ALL_CATALOG

C.3.2.2 ALL_COL_COMMENTS

C.3.2.3 ALL_CONS_COLUMNS

C.3.2.4 ALL_CONSTRAINTS

C.3.2.5 ALL_IND_COLUMNS

C.3.2.6 ALL_INDEXES

C.3.2.7 ALL_OBJECTS

C.3.2.8 ALL_TAB_COLUMNS

C.3.2.9 ALL_TAB_COMMENTS

C.3.2.10 ALL_TABLES

C.3.2.11 ALL_USERS

C.3.2.12 ALL_VIEWS

C.3.2.13 DICTIONARY

C.3.2.14 USER_CATALOG

C.3.2.15 USER_COL_COMMENTS

C.3.2.16 USER_CONS_COLUMNS

C.3.2.17 USER_CONSTRAINTS

C.3.2.18 USER_IND_COLUMNS

C.3.2.19 USER_INDEXES

C.3.2.20 USER_OBJECTS

C.3.2.21 USER_TAB_COLUMNS

C.3.2.22 USER_TAB_COMMENTS

C.3.2.23 USER_TABLES

C.3.2.24 USER_USERS

C.3.2.25 USER_VIEWS

Index