Oracle® Database

Heterogeneous Connectivity Administrator's Guide

10g Release 2 (10.2)

B14232-01

June 2005

Oracle Database Heterogeneous Connectivity Administrator's Guide, 10g Release 2 (10.2)

B14232-01

Copyright © 2001, 2005, Oracle. All rights reserved.

Primary Author:  Rhonda Day

Contributor:  Juan Pablo Ahues-Vasquez, Peter Castro, Jacco Draaijer, Vira Goorah, Peter Wong, Raghu Mani

The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited.

The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. This document is not warranted to be error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose.

If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software—Restricted Rights (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065

The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and we disclaim liability for any damages caused by such use of the Programs.

Oracle, JD Edwards, PeopleSoft, and Retek are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

The Programs may provide links to Web sites and access to content, products, and services from third parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites. You bear all risks associated with the use of such content. If you choose to purchase any products or services from a third party, the relationship is directly between you and the third party. Oracle is not responsible for: (a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the third party, including delivery of products or services and warranty obligations related to purchased products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party.

Contents

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

Audience

Documentation Accessibility

Related Documents

Conventions

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.5 HS_DB_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 A.24 IFILE
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

List of Figures

2-1 Oracle Heterogeneous Connectivity Process Architecture

2-2 Accessing Multiple Non-Oracle Instances

2-3 Gateway Process Flow

3-1 Flow Diagram for Nonquery Passthrough SQL

3-2 Passthrough SQL for Queries

4-1 Optimizing Data Transfers

5-1 Multithreaded Agent Architecture

7-1 Oracle and Non-Oracle Systems on a Separate Machines

7-2 Oracle and Non-Oracle Systems on the Same Machine

List of Tables

4-1 Data Dictionary Views for Heterogeneous Services

4-2 Important Columns in the V$HS_AGENT View

4-3 Important Columns in the V$HS_SESSION View

4-4 Important Columns in the V$HS_SESSION View

5-1 Agent Control Utility Commands

5-2 Initialization Parameters for agtctl

7-1 ODBC Functions

7-2 OLE DB (SQL) Interfaces

7-3 OLE DB (FS) Interfaces

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-1 Generic Connectivity Data Dictionary Mapping

Send Us Your Comments

Oracle Database Heterogeneous Connectivity Administrator's Guide, 10g Release 2 (10.2)

B14232-01

Oracle welcomes your comments and suggestions on the quality and usefulness of this publication. Your input is an important part of the information used for revision.

- Did you find any errors?

- Is the information clearly presented?

- Do you need more information? If so, where?

- Are the examples correct? Do you need more examples?

- What features did you like most about this manual?

If you find any errors or have any other suggestions for improvement, please indicate the title and part number of the documentation and the chapter, section, and page number (if available). You can send comments to us in the following ways:

- Electronic mail: infodev_us@oracle.com

- FAX: telephone number. Attn: Server Technologies Documentation Manager

- Postal service:

Oracle Corporation

Server Technologies Documentation Manager

400 Oracle Parkway, Mailstop 4op11

Redwood Shores, CA 94065

USA

If you would like a reply, please give your name, address, telephone number, and electronic mail address (optional).

If you have problems with the software, please contact your local Oracle Support Services.

Preface

This manual describes Oracle's approach for information integration in a heterogeneous environment. Specifically, it describes Oracle Transparent Gateways and Generic Connectivity and is meant to be an administrator's guide for these Oracle products.

This preface contains these topics:

- Audience

- Documentation Accessibility

- Related Documents

- Conventions

Audience

This manual is intended for the following users:

- Database administrators who want to administer distributed database systems that involve Oracle to non-Oracle database links

- Application developers who want to make use of the heterogeneous connectivity functionality in the Oracle database server

- Readers who want a high-level understanding of Oracle's architecture for heterogeneous connectivity and how it works

To use this document, you need to be familiar with relational database concepts and basic database or applications administration. You also need to be familiar with the operating system environment under which database administrators are running Oracle.

Documentation Accessibility

Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at

http://www.oracle.com/accessibility/

Accessibility of Code Examples in Documentation

Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.

Accessibility of Links to External Web Sites in Documentation

This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.

TTY Access to Oracle Support Services

Oracle provides dedicated Text Telephone (TTY) access to Oracle Support Services within the United States of America 24 hours a day, seven days a week. For TTY support, call 800.446.2398.

Related Documents

For more information, see these Oracle resources:

- Oracle Database Concepts

- Oracle Database Administrator's Guide

- Oracle Database Application Developer's Guide - Fundamentals

Many of the examples in this book use the sample schemas, which are installed by default when you select the Basic Installation option with an Oracle Database installation. Refer to Oracle Database Sample Schemas for information on how these schemas were created and how you can use them yourself.

Printed documentation is available for sale in the Oracle Store at

http://oraclestore.oracle.com/

To download free release notes, installation documentation, white papers, or other collateral, please visit the Oracle Technology Network (OTN). You must register online before using OTN; registration is free and can be done at

http://www.oracle.com/technology/membership/

If you already have a username and password for OTN, then you can go directly to the documentation section of the OTN Web site at

http://www.oracle.com/technology/documentation/

Conventions

The following text conventions are used in this document:

Convention

Meaning

boldface

Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.

italic

Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.

monospace

Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.

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

This section describes new features of the Oracle Database 10g Release 2 (10.2) and provides pointers to additional information. New features information from previous releases is also retained to help those users migrating to the current release.

The following sections describe the new features in Oracle Database Heterogeneous Connectivity Administrator's Guide:

- Oracle Database 10g Release 2 (10.2) New Features

- Oracle Database 10g Release 1 (10.1) Features

Oracle Database 10g Release 2 (10.2) New Features

New features for Oracle Database 10g Release 2 (10.2) in the Heterogeneous Connectivity Administrator's Guide include:

- Data type checking

This release adds data type checking for remote-mapped statements. The result provides consistent data type checking and complete data type coercion.

See Also:

Section 4.5, "Oracle Database Server SQL Construct Processing"

- tg4pwd Utility

Use this utility to encrypt initialization parameters in a Heterogeneous Services Gateway initialization file.

See Also:

Section 4.2.3, "Encrypting Initialization Parameters"

Oracle Database 10g Release 1 (10.1) Features

Features for Oracle Database 10g Release 1 (10.1) in the Heterogeneous Connectivity Administrator's Guide included:

- HS_CALL_NAME initialization parameter

Use the HS_CALL_NAME initialization parameter to specify remote functions that can be referenced by SQL statements.

See Also:

Section 4.6, "Executing User-Defined Functions on a Non-Oracle Database" and Section A.1, "HS_CALL_NAME"

1 Introduction

This chapter describes the challenges of operating in a heterogeneous environment. Oracle recognizes these challenges and offers both synchronous and asynchronous solutions that enable companies to easily operate in such an environment. The two synchronous solutions, Oracle Transparent Gateways and Generic Connectivity, are discussed this book.

This chapter contains these topics:

- The Information Integration Challenge

- How Oracle Addresses Synchronous Information Integration

- Benefits of Oracle's Solution for Synchronous Information Integration

See Also:

For information about a specific Oracle Transparent Gateway, please consult the Oracle documentation for that specific gateway

1.1 The Information Integration Challenge

Information integration is a challenge that affects many organizations. Many run several different database systems. Each of these systems stores data and has a set of applications that run against it. This data is just bits and bytes on a file system - and only a database can turn the bits and bytes of data into business information. Integration and consolidation of all business information would allow an organization to easily and quickly take advantage of the synergies inherent in business information.

Consolidation of all data into one database system is often difficult. This is in large part because many of the applications that run against one database may not have an equivalent that runs against another. Until such time as migration to one consolidated database system is made feasible, it is necessary for the various heterogeneous database systems to work together.

There are several problems to overcome before such interoperability becomes possible. The database systems can have different access interfaces, different data types, different capabilities, and different ways of handling error conditions. Even when one relational database is trying to access another relational database the differences are significant. In such a situation, the common features of the databases include data access through SQL, two-phase commit, and similar data types.

However, there are significant differences as well. SQL dialects can be different, as can transaction semantics. There can be some data types in one database that do not exist in the other. The most significant area of difference is in the data dictionaries of the two databases. Most data dictionaries contain similar information but the information is structured for each in a completely different way. There are several possible ways of overcoming this problem. In this book, we describe the approach that Oracle has taken for synchronously accessing information from multiple sources.

1.2 How Oracle Addresses Synchronous Information Integration

If a client program needs to access or modify data at several Oracle databases, it can open connections to each of them. This approach, however, has several drawbacks; among them are the following:

- If you want to join data from the databases, the client must contain logic allowing that.

- If data integrity must be guaranteed, the client needs to contain transaction coordination logic.

Oracle provides another approach called distributed processing, where the client connects to one Oracle database and shifts the burden of joining data and transaction coordination to that database. The database to which the client program connects is called the local database. Any database other than this one is a remote database. The client program can access objects at any of the remote databases using database links. The Oracle query processor takes care of the joins and its transaction engine takes care of the transaction coordination.

The approach that Oracle has taken to solving the heterogeneous connectivity problem is to allow a non-Oracle system to be one of the remote nodes in the previously described scenario. From the client's point of view, the remote non-Oracle system functions like a remote Oracle system. It appears to understand the same SQL dialect and to have the same data dictionary structure as an Oracle system. Access to a non-Oracle system in this manner is done through a component in the Oracle server called Heterogeneous Services.

The work done by the Heterogeneous Services component is, for the most part, completely transparent to the end user. With only a few exceptions (these are noted in later chapters), you are not required to do anything different to access a non-Oracle system than is required for accessing an Oracle system. The Heterogeneous Services component is used as the foundation for implementing Oracle's access to non-Oracle databases.

The following are two methods that Oracle uses for solving the challenges of information sharing and integration in a heterogeneous environment. Because they are both based on a foundation that is integrated into the database, they can exploit all of the features of the database.

- Oracle Transparent Gateways

An Oracle Transparent Gateway works in conjunction with the Heterogeneous Services component of the Oracle Database server to access a particular, commercially available, non-Oracle system for which that Oracle Transparent Gateway has been designed. For example, you use the Oracle Transparent Gateway for Sybase on Solaris to access a Sybase database operating on a Sun Solaris platform.

Using an Oracle Transparent Gateway, you can access data anywhere in a distributed database system without being required to know either the location of the data, or how it is stored.

- Generic Connectivity

Oracle provides a set of agents, containing only generic code, that interface with the Heterogeneous Services component and comprise Generic Connectivity. These agents require drivers to provide access to the non-Oracle systems. Oracle provides Generic Connectivity agents for ODBC and OLE DB that enable you to use ODBC and OLE DB drivers to access non-Oracle databases.

Note:

The ODBC and OLE DB drivers that are required by Generic Connectivity agents are not supplied by Oracle. Users should obtain drivers from other vendors.

The functionality of Generic Connectivity is more limited than that of Oracle Transparent Gateways.

Oracle also offers asynchronous information integration solutions that are mentioned here, but that are not discussed in this book. Briefly, these solutions include:

- Oracle Streams

Oracle Streams enables the propagation of data, transactions, and events in a single data stream or queue, either within a database or between multiple databases. Not only can Oracle Streams capture, propagate, and apply changes to data, it can also handle data structure changes (DDL) and user-defined events. Changes can be captured and applied as is, or transformed at any point in the capture, propagation, and application processing.

- Messaging Gateway

The messaging gateway enables communication between Oracle and other non-Oracle message queuing.

- Open System Interfaces

Oracle offers a number of open interfaces, such as OCI, JDBC, and ODBC, that enable customers to use third party applications or to write their own client applications to access the Oracle database.

1.3 Benefits of Oracle's Solution for Synchronous Information Integration

Much of the processing power of Generic Connectivity and Transparent Gateways is integrated into the database. This provides an efficient solution for information integration that enables full exploitation of the power and features of the Oracle database. This includes such features as powerful SQL parsing and distributed optimization capabilities.

The following sections explore the benefits of Oracle's approach to resolving the challenges of a heterogeneous environment:

- Remote Data Can Be Accessed Transparently

- 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

1.3.1 Remote Data Can Be Accessed Transparently

Both Generic Connectivity and Oracle Transparent Gateways provide the ability to transparently access data in non-Oracle databases from an Oracle environment. You can create synonyms for the objects in a non-Oracle database and refer to them without having to specify a physical location. This transparency eliminates the need for application developers to customize their applications to access data from different non-Oracle systems, thus decreasing development efforts and increasing the mobility of the application.

Instead of requiring applications to interoperate with non-Oracle systems using their native interfaces (which can result in intensive application-side processing), applications can be built upon a consistent Oracle interface for both Oracle and non-Oracle systems.

1.3.2 No Unnecessary Data Duplication

Generic Connectivity and Oracle Transparent Gateways provide applications direct access to data in non-Oracle databases. This eliminates the need to upload and download large amounts of data to different locations, thus reducing data duplication and saving disk storage space. Also, by eliminating this need to upload and download large amounts of data, there is a further benefit of a reduced risk for unsynchronized or inconsistent data.

1.3.3 SQL Statements Can Query Several Different Databases

The Oracle database server accepts SQL statements that query data stored in several different databases. The Oracle database server with the Heterogeneous Services component processes the SQL statement and passes the appropriate SQL directly to other Oracle databases and through gateways to non-Oracle databases. The Oracle database server then combines the results and returns them to the client.

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

Generic Connectivity and Oracle Transparent Gateways extend the range of Oracle's database and application development tools. Oracle has tools that increase application development and user productivity by reducing prototype, development, and maintenance time.

You are not required to develop new tools or learn how to use other tools to access data stored in non-Oracle databases. Instead, you can access Oracle and non-Oracle data with a single set of Oracle tools. These tools can run on remote machines connected through Oracle Net to the Oracle database server.

1.3.5 Users Can Talk to a Remote Database in its Own Language

Oracle enables you to transparently access non-Oracle systems using Oracle SQL. In some cases, however, it becomes necessary to use non-Oracle system SQL to access the non-Oracle system. For such cases, Heterogeneous Services has a pass-through feature that enables you to bypass Oracle's query processor and to talk to the remote database in its own language.

2 The Role of the Heterogeneous Services Component

Oracle's synchronous solutions for operating in a heterogeneous environment are Oracle Transparent Gateways and Generic Connectivity. The common component of the Oracle database server for supporting these solutions is Heterogeneous Services. This chapter describes the architecture and functionality of the Heterogeneous Services component and its interaction with Oracle Transparent Gateways and Generic Connectivity.

This chapter contains the following topics:

- Heterogeneous Connectivity Process Architecture

- Heterogeneous Services Agents

- Types of Heterogeneous Services Agents

- Heterogeneous Services Components

- Heterogeneous Services Configuration Information

- The Heterogeneous Services Data Dictionary

- Gateway Process Flow

2.1 Heterogeneous Connectivity Process Architecture

At a high level, Oracle heterogeneous connectivity process architecture is structured as shown in Figure 2-1.

Figure 2-1 Oracle Heterogeneous Connectivity Process Architecture

The Heterogeneous Services component in the Oracle database server talks to a Heterogeneous Services agent process which, in turn, talks to the non-Oracle system. We can conceptually divide the code into three parts:

- The Heterogeneous Services component in the Oracle database server.

Most of the processing related to heterogeneous connectivity is done in this module.

- Agent generic code.

This is code in the agent that is generic to all Heterogeneous Services products. This consists, for the most part, of code to communicate with the database and multithreading support.

- The driver.

This is the module that communicates with the non-Oracle system. It is used to map calls from the Heterogeneous Services onto the native API of the non-Oracle system and it is non-Oracle system specific.

2.2 Heterogeneous Services Agents

A Heterogeneous Services agent is the process through which an Oracle server connects to a non-Oracle system. This agent process that accesses a non-Oracle system is called a gateway. Access to all gateways goes through the Heterogeneous Services component in the Oracle server and all gateways contain the same agent-generic code. Each gateway has a different driver linked in that maps the Heterogeneous Services to the client API of the non-Oracle system.

The agent process consists of two components. These are agent generic code and a non-Oracle system-specific driver. An agent exists primarily to isolate the Oracle database server from third-party code. In order for a process to access the non-Oracle system, the non-Oracle system client libraries have to be linked into it. In the absence of the agent process, these libraries would have to be directly linked into the Oracle database and problems in this code could cause the Oracle server to go down. Having an agent process isolates the Oracle server from any problems in third-party code so that even if a fatal error takes place, only the agent process will end.

An agent can reside in the following places:

- On the same machine as the non-Oracle system

- On the same machine as the Oracle server

- On a machine different from either of these two

Agent processes are started when a user session makes its first non-Oracle system access through a database link. These connections are made using Oracle's remote data access software, Oracle Net Services, which enables both client/server and server-server communication. The agent process continues to run until the user session is disconnected or the database link is explicitly closed.

Multithreaded agents behave differently. They have to be explicitly started and shut down by a database administrator instead of automatically being spawned by Oracle Net Services.

See Also:

Chapter 5, "Multithreaded Agents"

2.3 Types of Heterogeneous Services Agents

There are two types of Heterogeneous Services agents:

- Oracle Transparent Gateways

- Generic Connectivity Agents

2.3.1 Oracle Transparent Gateways

An Oracle Transparent Gateway is a gateway that is designed for accessing a specific non-Oracle system. Oracle provides gateways to access several commercially produced non-Oracle systems; many of these gateways have been ported to several platforms. For example, an Oracle Transparent Gateway for Sybase on Solaris is the Solaris port of a gateway designed to access Sybase database systems.

With Oracle Transparent Gateways, you can use an Oracle database server to access data anywhere in a distributed database system without being required to know either the location of the data or how it is stored. When the results of your queries are returned to you by the Oracle database server, they are presented to you as if the data stores from which they were taken all resided within a remote instance of an Oracle distributed database.

2.3.2 Generic Connectivity Agents

In addition to Oracle Transparent Gateways for various non-Oracle database systems, there is a set of agents that comprise the Oracle Generic Connectivity feature. These agents contain only generic code and the customer is responsible for providing the necessary drivers. Oracle has Generic Connectivity agents for ODBC and OLE DB that enable you to use ODBC and OLE DB drivers to access non-Oracle systems that have an ODBC or an OLE DB interface.

To access a specific non-Oracle system using Generic Connectivity agents, you must configure an ODBC or OLE DB driver to the non-Oracle system. These drivers are not provided by Oracle. However, as long as Oracle supports the ODBC and OLE DB protocols, you can use the Generic Connectivity feature to access any non-Oracle system that can be accessed using an ODBC or OLE DB driver.

Generic Connectivity agents have some limitations. Connections to a Generic Connectivity agent are supported only for the local Oracle database server. Functionality of these agents, especially when compared to the Oracle Transparent Gateways, is limited.

See Also:

Chapter 7, "Generic Connectivity"

2.4 Heterogeneous Services Components

This section discusses the components of Heterogeneous Services in the Oracle database server. These components are:

- Transaction Service

- SQL Service

2.4.1 Transaction Service

The transaction service component of the Heterogeneous Services component makes it possible for non-Oracle systems to be integrated into Oracle database server transactions and sessions. When you access a non-Oracle system for the first time over a database link within your Oracle user session, you transparently set up an authenticated session in the non-Oracle system. At the end of your Oracle user session, the authenticated session in the non-Oracle database system is also closed.

Additionally, one or more non-Oracle systems can participate in an Oracle distributed transaction. When an application commits a transaction, Oracle's two-phase commit protocol accesses the non-Oracle database system to transparently coordinate the distributed transaction. Even in those cases where the non-Oracle system does not support all aspects of Oracle two-phase commit protocol, Oracle can (with some limitations) support distributed transactions with the non-Oracle system.

2.4.2 SQL Service

The Structured Query Language (SQL) service handles the processing of all SQL-related operations. The work done by the SQL service includes:

- Mapping Oracle internal SQL-related calls to the Heterogeneous Services driver application programing interface (API); this is in turn mapped by the driver to the client API of the non-Oracle system.

- Translating SQL statements from Oracle's SQL dialect to the SQL dialect of the non-Oracle system.

- Translating queries that reference Oracle data dictionary tables to queries that extract the necessary information from the non-Oracle system data dictionary.

- Converting data from non-Oracle system data types to Oracle data types and back.

- Making up for missing functionality at the non-Oracle system by issuing multiple queries to get the necessary data and doing post-processing to get the desired results.

2.5 Heterogeneous Services Configuration Information

Heterogeneous Services components consist of generic code and must be configured to work with many different non-Oracle systems. Each gateway has configuration information stored in the driver module. The information is uploaded to the server immediately after the connection to the gateway has been established. The configuration information includes:

- Data Dictionary Translation Views

- Heterogeneous Services Initialization Parameters

- Capabilities

2.5.1 Data Dictionary Translation Views

Data dictionary translations are views on non-Oracle data dictionary tables. They help Heterogeneous Services translate references to Oracle data dictionary tables into queries that can retrieve the equivalent information from the non-Oracle data dictionary.

See Also:

Appendix C, "Data Dictionary Translation Support"

2.5.2 Heterogeneous Services Initialization Parameters

Heterogeneous Services initialization parameters serve two functions:

- They give you a means of fine-tuning the gateway to optimize performance and memory utilization for the gateway and the Heterogeneous Services component.

- They enable you to tell the gateway (and, thereby, Heterogeneous Services) how the non-Oracle system has been configured (for example, in what language the non-Oracle system is running). They give information to Heterogeneous Services about the configurable properties of the non-Oracle system.

You can examine the Heterogeneous Services initialization parameters for a session by querying the view V$HS_PARAMETER. Users can set initialization parameters in gateway initialization files.

2.5.3 Capabilities

Capabilities tell Heterogeneous Services about the limitations of the non-Oracle system (such as what types of SQL statements are supported) and how to map Oracle data types and SQL expressions to their non-Oracle system equivalents. In other words, they tell Heterogeneous Services about the non-configurable properties of the non-Oracle system. Capabilities cannot be changed by the user.

2.6 The Heterogeneous Services Data Dictionary

Configuration information is uploaded from an agent to the Heterogeneous Services component immediately after the connection to the agent has been established. The configuration information is stored in Heterogeneous Services data dictionary tables. No further upload takes place until something at the agent changes (for example, if a patch is applied or if the agent is upgraded to a new version).

2.6.1 Classes and Instances

Using Heterogeneous Services, you can access several non-Oracle systems from a single Oracle database. This is illustrated in Figure 2-2, which shows two non-Oracle systems being accessed.

Figure 2-2 Accessing Multiple Non-Oracle Instances

Both agents upload configuration information, which is stored as part of the Heterogeneous Services data dictionary information on the Oracle database server.

Although it is possible to store data dictionary information at one level of granularity by having completely separate definitions in the Heterogeneous Services data dictionary for each individual instance, this can lead to an unnecessarily large amount of redundant data dictionary information. To avoid this, Oracle organizes the Heterogeneous Services data dictionary by two levels of granularity, called class and instance.

A class pertains to a specific type of non-Oracle system. For example, you may want to access the class of Sybase database systems with your Oracle database server. An instance defines specializations within a class. For example, you may want to access several separate instances within a Sybase database system. Each class definition (one level of granularity) is shared by all the particular instances (a second level of granularity) under that class. Further, instance information takes precedence over class information, and class information takes precedence over server-supplied defaults.

For example, suppose that the Oracle database server accesses three instances of Sybase and two instances of Ingres II. Sybase and Ingres II each have their own code, requiring separate class definitions for the Oracle database server to access them. The Heterogeneous Services data dictionary therefore would contain two class definitions, one for Sybase and one for Ingres II, with five instance definitions, one for each instance being accessed by the Oracle database server.

Note that instance level capability and data dictionary information are session-specific and hence are not stored in the Heterogeneous Services data dictionary of the Oracle database server. However, instance level initialization parameters can be stored in the database.

2.6.2 Data Dictionary Views

The Heterogeneous Services data dictionary views contain the following kinds of information:

- Names of instances and classes uploaded into the Oracle data dictionary

- Capabilities, including SQL translations, defined for each class or instance

- Data Dictionary translations defined for each class or instance

- Initialization parameters defined for each class or instance

You can access information from the Oracle data dictionary by using fixed views. The views are categorized into three main types:

- General views

- Views used for the transaction service

- Views used for the SQL service

See Also:

Appendix C, "Data Dictionary Translation Support"

2.7 Gateway Process Flow

Figure 2-3 shows a typical gateway process flow. The steps explain the sequence of events that occur when a client application queries the non-Oracle database system database through the gateway.

Figure 2-3 Gateway Process Flow

1. The client application sends a query over Oracle Net to the Oracle database server.

2. Heterogeneous Services and the gateway converts the Oracle SQL statement into a SQL statement understood by the non-Oracle database system.

3. The Oracle database server sends the query over to the gateway using Oracle Net.

4. For the first transaction in a session, the gateway logs into non-Oracle database system using a username and password that is valid in the non-Oracle system.

5. The gateway retrieves data using non-Oracle database system SQL statements.

6. The gateway converts retrieved data into a format compatible with the Oracle database server.

7. The gateway returns query results to the Oracle database server, again using Oracle Net Services.

8. The Oracle database server passes the query results to the client application using Oracle Net. The database link remains open until the gateway session is finished or the database link is explicitly closed.

3 Features of Oracle Transparent Gateways and Generic Connectivity

This chapter describes the major features provided by Oracle Transparent Gateways and Generic Connectivity. Descriptions of these features are contained in the following topics:

- SQL and PL/SQL Support

- Heterogeneous Replication

- Passthrough SQL

- Result Set Support

- Data Dictionary Translations

- Datetime Data Types

- Two-Phase Commit Protocol

- Piecewise LONG Data Type

- SQL*Plus DESCRIBE Command

- Constraints on SQL in a Distributed Environment

- Using Oracle's Optimizer with Heterogeneous Services

Note:

These features may not be available in all Heterogeneous Services gateways. Not only must there be generic support for these features, which Heterogeneous Services provides, but there must also be support added to the driver for them. Consult the appropriate gateway documentation to determine if a particular feature is supported for your gateway.

3.1 SQL and PL/SQL Support

SQL statements are translated and data types are mapped according to capabilities. PL/SQL calls are mapped to non-Oracle system stored procedures. In the case of SQL statements, if functionality is missing at the remote system, then either a simpler query is issued or the statement is broken up into multiple queries and the desired results are obtained by post-processing in the Oracle database.

Even though Heterogeneous Services can, for the most part, incorporate non-Oracle systems into Oracle distributed sessions, there are several limitations to this. Some of the generic limitations are:

- There is no support for CONNECT BY clauses in SQL statements.

- ROWID support is limited; consult individual gateway documentation for more details. The Oracle Universal ROWID data type is not supported in any gateway that uses Heterogeneous Services.

- LOBs, ADTs, and REFs are not supported.

- Remote packages are not supported.

- Remote stored procedures can have out arguments of type REF CURSOR but not in or in-out objects.

- Oracle Heterogeneous Services agents do not support shared database links.

Note:

In addition to these generic limitations, each gateway can have additional limitations. Please consult the gateway documentation for individual gateways for a complete list of limitations of the product.

3.2 Heterogeneous Replication

Data can be replicated between a non-Oracle system and an Oracle server using materialized views.

Note:

There is another means of replicating information between Oracle and non-Oracle databases called Streams.
For information about using Streams, see Oracle Streams Concepts and Administration.

Materialized views instantiate data captured from tables at the non-Oracle master site at a particular point in time. This instant is defined by a refresh operation, which copies this data to the Oracle server and synchronizes the copy on Oracle with the master copy on the non-Oracle system. The "materialized" data is then available as a view on the Oracle server.

Replication facilities provide mechanisms to schedule refreshes and to collect materialized views into replication groups to facilitate their administration. Refresh groups permit refreshing multiple materialized views just as if they were a single object.

Heterogeneous replication support is necessarily limited to a subset of the full Oracle-to-Oracle replication functionality:

- Only the non-Oracle system can be the master site. This is because materialized views can be created only on an Oracle server.

- Materialized views must use complete refresh. This is because fast refresh would require Oracle-specific functionality in the non-Oracle system.

- Not all types of materialized views can be created to reference tables on a non-Oracle system. Primary key and subquery materialized views are supported, but ROWID and OBJECT ID materialized views are not supported. This is because there is no SQL standard for the format and contents of ROWID, and non-Oracle systems do not implement Oracle objects.

Other restrictions apply to any access to non-Oracle data through Oracle's Heterogeneous Services facilities. The most important of these are:

- Non-Oracle data types in table columns mapped to a fixed view must be compatible with (that is, have a mapping to or from) Oracle data types. This is usually true for data types defined by ANSI SQL standards.

- A subquery materialized view may not be able to use language features restricted by individual non-Oracle systems. In many cases Heterogeneous Services supports such language features by processing queries within the Oracle server, but occasionally the non-Oracle systems impose limitations that cannot be diagnosed until Heterogeneous Services attempt to execute the query.

The following examples illustrate basic setup and use of three materialized views to replicate data from a non-Oracle system to an Oracle data store.

Note:

For the following examples, remote_db refers to the non-Oracle system which you are accessing from your Oracle database server.
Modify these examples for your environment. Do not try to execute them as they are written.

Example 1: Create Materialized Views for Heterogeneous Replication

This example creates three materialized views that are then used in succeeding examples.

1. Create a primary key materialized view of table customer@remote_db.

CREATE MATERIALIZED VIEW pk_mv REFRESH COMPLETE AS
SELECT * FROM customer@remote_db WHERE "zip" = 94555;

2. Create a subquery materialized view of tables orders@remote_db and customer@remote_db.

CREATE MATERIALIZED VIEW sq_mv REFRESH COMPLETE AS
SELECT * FROM orders@remote_db o WHERE EXISTS
(SELECT c."c_id" FROM customer@remote_db c
WHERE c."zip" = 94555 and c."c_id"  = o."c_id" );

3. Create a complex materialized view of data from multiple tables on remote_db.

CREATE MATERIALIZED VIEW cx_mv
REFRESH COMPLETE AS
SELECT  c."c_id", o."o_id"
FROM customer@remote_db c,
orders@remote_db o,
order_line@remote_db ol
WHERE c."c_id" = o."c_id"
AND o."o_id" = ol."o_id";

Example 2: Set Up a Refresh Group for Heterogeneous Replication

BEGIN
dbms_refresh.make('refgroup1',
'pk_mv, sq_mv, cx_mv',
NULL, NULL);
END;
/

Example 3: Force Refresh of all Three Materialized Views

BEGIN
dbms_refresh.refresh('refgroup1');
END;
/

See Also:

Oracle Database Advanced Replication for a full description of materialized views and replication facilities

3.3 Passthrough SQL

The passthrough SQL feature enables you to send a statement directly to a non-Oracle system without being interpreted by the Oracle server. This feature can be useful if the non-Oracle system allows for operations in statements for which there is no equivalent in Oracle.

This section contains the following topics:

- Using the DBMS_HS_PASSTHROUGH Package

- Considering the Implications of Using Passthrough SQL

- Executing Passthrough SQL Statements

3.3.1 Using the DBMS_HS_PASSTHROUGH Package

You can execute passthrough SQL statements directly at the non-Oracle system using the PL/SQL package DBMS_HS_PASSTHROUGH. Any statement executed with this package is executed in the same transaction as standard SQL statements.

The DBMS_HS_PASSTHROUGH package is a virtual package. It conceptually resides at the non-Oracle system. In reality, however, calls to this package are intercepted by Heterogeneous Services and mapped to one or more Heterogeneous Services calls. The driver, in turn, maps these Heterogeneous Services calls to the API of the non-Oracle system. The client application should invoke the procedures in the package through a database link in exactly the same way as it would invoke a non-Oracle system stored procedure. The special processing done by Heterogeneous Services is transparent to the user.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about this package

3.3.2 Considering the Implications of Using Passthrough SQL

When you execute a passthrough SQL statement that implicitly commits or rolls back a transaction in the non-Oracle system, the transaction is affected. For example, some systems implicitly commit the transaction containing a data definition language (DDL) statement. Because the Oracle database server is bypassed, the Oracle database server is unaware of the commit in the non-Oracle system. Consequently, the data at the non-Oracle system can be committed while the transaction in the Oracle database server is not.

If the transaction in the Oracle database server is rolled back, data inconsistencies between the Oracle database server and the non-Oracle server can occur. This situation results in global data inconsistency.

Note that if the application executes a regular COMMIT statement, the Oracle database server can coordinate the distributed transaction with the non-Oracle system. The statement executed with the passthrough facility is part of the distributed transaction.

3.3.3 Executing Passthrough SQL Statements

The following table shows the functions and procedures provided by the DBMS_HS_PASSTHROUGH package that enable you to execute passthrough SQL statements.

Procedure/Function

Description

OPEN_CURSOR

Opens a cursor

CLOSE_CURSOR

Closes a cursor

PARSE

Parses the statement

BIND_VARIABLE

Binds IN variables

BIND_OUT_VARIABLE

Binds OUT variables

BIND_INOUT_VARIABLE

Binds IN OUT variables

EXECUTE_NON_QUERY

Executes nonquery

EXECUTE_IMMEDIATE

Executes nonquery without bind variables

FETCH_ROW

Fetches rows from query

GET_VALUE

Retrieves column value from SELECT statement or retrieves OUT bind parameters

3.3.3.1 Executing Nonqueries

Nonqueries include the following statements and types of statements:

- INSERT

- UPDATE

- DELETE

- DDL

To execute nonquery statements, use the EXECUTE_IMMEDIATE function. For example, to execute a DDL statement at a non-Oracle system that you can access using the database link salesdb, execute:

DECLARE
num_rows INTEGER;

BEGIN
num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@salesdb
('CREATE TABLE dept1 (n SMALLINT, loc CHARACTER(10))');
END;

The variable num_rows is assigned the number of rows affected by the execution. For DDL statements, zero is returned. Note that you cannot execute a query with EXECUTE_IMMEDIATE and you cannot use bind variables.

3.3.3.1.1 Using Bind Variables: Overview

Bind variables allow you to use the same SQL statement multiple times with different values, reducing the number of times a SQL statement needs to be parsed. For example, when you need to insert four rows in a particular table, you can parse the SQL statement once and bind and execute the SQL statement for each row. One SQL statement can have zero or more bind variables.

To execute passthrough SQL statements with bind variables, you must:

1. Open a cursor.

2. Parse the SQL statement at the non-Oracle system.

3. Bind the variables.

4. Execute the SQL statement at the non-Oracle system.

5. Close the cursor.

Figure 3–1 shows the flow diagram for executing nonqueries with bind variables.

Figure 3-1 Flow Diagram for Nonquery Passthrough SQL

3.3.3.1.2 Using IN Bind Variables

The syntax of the non-Oracle system determines how a statement specifies a bind variable. For example, in Oracle you define bind variables with a preceding colon. For example:

...
UPDATE emp
SET sal=sal*1.1
WHERE ename=:ename;
...

In this statement, ename is the bind variable. In non-Oracle systems, you may need to specify bind variables with a question mark. For example:

...
UPDATE emp
SET sal=sal*1.1
WHERE ename= ?;
...

In the bind variable step, you must positionally associate host program variables (in this case, PL/SQL) with each of these bind variables. For example, to execute the preceding statement, use the following PL/SQL program:

DECLARE
c INTEGER;
nr INTEGER;
BEGIN
c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@salesdb;
DBMS_HS_PASSTHROUGH.PARSE@salesdb(c,
'UPDATE emp SET SAL=SAL*1.1 WHERE ename=?');
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@salesdb(c,1,'JONES');
nr:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@salesdb(c);
DBMS_OUTPUT.PUT_LINE(nr||' rows updated');
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@salesdb(c);
END;

3.3.3.1.3 Using OUT Bind Variables

In some cases, the non-Oracle system can also support OUT bind variables. With OUT bind variables, the value of the bind variable is not known until after the execution of the SQL statement.

Although OUT bind variables are populated after the SQL statement is executed, the non-Oracle system must know that the particular bind variable is an OUT bind variable before the SQL statement is executed. You must use the BIND_OUT_VARIABLE procedure to specify that the bind variable is an OUT bind variable.

After the SQL statement is executed, you can retrieve the value of the OUT bind variable using the GET_VALUE procedure.

3.3.3.1.4 Using IN OUT Bind Variables

A bind variable can be both an IN and an OUT variable. This means that the value of the bind variable must be known before the SQL statement is executed but can be changed after the SQL statement is executed.

For IN OUT bind variables, you must use the BIND_INOUT_VARIABLE procedure to provide a value before the SQL statement is executed. After the SQL statement is executed, you must use the GET_VALUE procedure to retrieve the new value of the bind variable.

3.3.3.2 Executing Queries

The difference between queries and nonqueries is that queries retrieve a result set from a SELECT statement. The result set is retrieved by iterating over a cursor.

Figure 3–2 illustrates the steps in a passthrough SQL query. After the system parses the SELECT statement, each row of the result set can be fetched with the FETCH_ROW procedure. After the row is fetched, use the GET_VALUE procedure to retrieve the selected list of items into program variables. After all rows are fetched, you can close the cursor.

Figure 3-2 Passthrough SQL for Queries

You do not have to fetch all the rows. You can close the cursor at any time after opening the cursor.

Note:

Although you are fetching one row at a time, Heterogeneous Services optimizes the round-trips between the Oracle server and the non-Oracle system by buffering multiple rows and fetching from the non-Oracle data system in one round-trip.

The following example executes a query:

DECLARE
val  VARCHAR2(100);
c    INTEGER;
nr   INTEGER;
BEGIN
c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@salesdb;
DBMS_HS_PASSTHROUGH.PARSE@salesdb(c,
'select ENAME
from   EMP
where  DEPTNO=10');
LOOP
nr := DBMS_HS_PASSTHROUGH.FETCH_ROW@salesdb(c);
EXIT WHEN nr = 0;
DBMS_HS_PASSTHROUGH.GET_VALUE@salesdb(c, 1, val);
DBMS_OUTPUT.PUT_LINE(val);
END LOOP;
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@salesdb(c);
END;

After the SELECT statement has been parsed, the rows are fetched and printed in a loop until the FETCH_ROW function returns the value 0.

3.4 Result Set Support

Various relational databases allow stored procedures to return result sets. In other words, stored procedures will be able to return one or more sets of rows.

Traditionally, database stored procedures worked exactly like procedures in any high-level programming language. They had a fixed number of arguments which could be of types IN, OUT, or IN OUT. If a procedure had n arguments, it could return at most n values as results. However, suppose that you wanted a stored procedure to execute a query such as SELECT * FROM emp and return the results. The emp table might have a fixed number of columns but there is no way of telling, at procedure creation time, the number of rows it has. Because of this, no traditional stored procedure could be created that returned the results of a such a query. As a result, several relational database vendors added the capability of returning results sets from stored procedures, but each kind of relational database returns result sets from stored procedures in a different way.

Oracle has a data type called a REF CURSOR. Like every other Oracle data type, a stored procedure can take this data type as an IN or OUT argument. In Oracle, a stored procedure must have an output argument of type REF CURSOR. It then opens a cursor for a SQL statement and places a handle to that cursor in that output parameter. The caller can then fetch from the REF CURSOR the same way as from any other cursor.

Oracle can do a lot more than simply return result sets. REF CURSOR can be passed as an input argument to PL/SQL routines to be passed back and forth between client programs and PL/SQL routines or as an input argument between several PL/SQL routines.

3.4.1 Result Set Support In Non-Oracle Systems

Several non-Oracle systems allow stored procedures to return result sets but do so in completely different ways. Result sets are supported to some extent in DB2, Sybase, Microsoft SQL Server, and Informix. Result set support in these databases is based on one of the following two models.

3.4.1.1 Model 1: Result Set Support

When creating a stored procedure, you can explicitly specify the maximum number of result sets that can be returned by that stored procedure. While executing, the stored procedure can open anywhere from zero up to its prespecified maximum number of result sets. After the execution of the stored procedure, a client program obtains handles to these result sets by using either an embedded SQL directive or by calling a client library function. After that, the client program can fetch from the result set in the same way as from a regular cursor.

3.4.1.2 Model 2: Result Set Support

In this model, there is no prespecified limit to the number of result sets that can be returned by a stored procedure. Both Model 1 and Oracle have a limit. For Oracle, the number of result sets returned by a stored procedure can be at most the number of REF CURSOR out arguments. For Model 1, the upper limit is specified using a directive in the stored procedure language. Another way that Model 2 differs from Oracle and Model 1 is that they do not return a handle to the result sets but instead place the entire result set on the wire when returning from a stored procedure. For Oracle, the handle is the REF CURSOR OUT argument. For Model 1, it is obtained separately after the execution of the stored procedure. For both Oracle and Model 1, once the handle is obtained, data from the result set is obtained by doing a fetch on the handle; there are several cursors open and the fetch can be in any order. In the case of Model 2, however, all the data is already on the wire, with the result sets coming in the order determined by the stored procedure and the output arguments of the procedures coming at the end. So the whole of the first result set must be fetched, then the whole of the second one, until all of the results have been fetched. Finally, the stored procedure OUT arguments must be fetched.

3.4.2 Heterogeneous Services Support for Result Sets

As can be seen in the preceding sections, result set support exists among non-Oracle databases in a variety of forms. All of these have to be mapped onto the Oracle REF CURSOR model. Due to the considerable differences in behavior among the various non-Oracle systems, Heterogeneous Services result set support behaves in one of two different ways depending on the non-Oracle system to which it is connected.

Please note the following about Heterogeneous Services result set support:

- Result set support is present in Heterogeneous Services generic code but in order for the feature to work in a gateway, the driver has to implement it as well. Not all drivers have implemented result set support and you should check in the gateway-specific documentation to determine whether it is supported in your gateway.

- Heterogeneous Services supports REF CURSOR OUT arguments from stored procedures. IN and IN OUT arguments are not supported.

- The REF CURSOR OUT arguments are all anonymous REF CURSORs. No typed REF CURSORs are returned by Heterogeneous Services.

3.4.2.1 Results Sets: Cursor Mode

Each result set returned by the non-Oracle system stored procedure is mapped by the Oracle driver to an OUT argument of type REF CURSOR. The client program sees a stored procedure with several OUT arguments of type REF CURSOR. After executing the stored procedure, the client program can fetch from the REF CURSOR in the same way as it would from a REF CURSOR returned by an Oracle stored procedure. When connecting to the gateway as described in Section 3.4.1.1, Heterogeneous Services will be in cursor mode.

3.4.2.2 Result Sets: Sequential Mode

In Oracle, there is a prespecified maximum number of result sets that a particular stored procedure can return. The number of result sets returned is at most the number of REF CURSOR OUT arguments for the stored procedure. It can return fewer result sets, but it can never return more.

For the system described in Section 3.4.1.2, there is no prespecified maximum number of result sets that can be returned. In the case of Model 1 (in Section 3.4.1.1), we know the maximum number of result sets that a procedure can return, and that the driver can return to Heterogeneous Services, is prespecified in the stored procedure by the number of REF CURSOR OUT arguments. If, on execution of the stored procedure, fewer result sets than the maximum are returned, then the other REF CURSOR OUT arguments are set to NULL.

Another problem for Model 2 database servers is that result sets have to be retrieved in the order in which they were placed on the wire by the database. This prevents Heterogeneous Services from running in cursor mode when connecting to these databases. To access result sets returned by these stored procedures, you must operate Heterogeneous Services in sequential mode.

In sequential mode, the procedure description returned by the driver contains the following:

- All the input arguments of the remote stored procedure

- None of the output arguments

- One OUT argument of type REF CURSOR (corresponding to the first result set returned by the stored procedure)

The client fetches from this REF CURSOR and then calls the virtual package function DBMS_HS_RESULT_SET.GET_NEXT_RESULT_SET to fetch the REF CURSOR corresponding to the next result set. This function call is repeated until all result sets have been fetched. The last result set returned will actually be the OUT arguments of the remote stored procedure.

The major limitations of sequential mode are as follows:

- Result sets returned by a remote stored procedure have to be retrieved in the order in which they were placed on the wire.

- On execution of a stored procedure, all result sets returned by a previously executed stored procedure are closed (regardless of whether the data has been completely fetched or not).

See Also:

Your gateway-specific manual for more information about how result sets are supported through the gateway

3.5 Data Dictionary Translations

Most database systems have some form of data dictionary. A data dictionary is a collection of information about the database objects that have been created by various users of the system. For a relational database, a data dictionary is a set of tables and views which contain information about the data in the database. This information includes information on the users who are using the system and on the objects that they have created (such as tables, views, and triggers). For the most part, all data dictionaries (regardless of the database system) contain the same information but each database system organizes the information in a different way.

For example, the ALL_CATALOG Oracle data dictionary view gives a list of tables, views, and sequences in the database. It has three columns: the first is called OWNER and is the name of the owner of the object, the second is called TABLE_NAME and is the name of the object, and the third is called TABLE_TYPE and is the type. This field has value TABLE, VIEW, SEQUENCE and so forth depending on the object type. However, in Sybase, the same information is stored in two tables called sysusers and sysobjects whose column names are quite different from those of the Oracle ALL_CATALOG table. Additionally, in Oracle, the table type is a string with a value such as TABLE or VIEW. In Sybase, it is a letter. For example, in Sybase, U means user table, S means system table, V means view, and so forth.

If the client program wants information from the table ALL_CATALOG at Sybase, it sends a query referencing ALL_CATALOG@database_link to a gateway. Heterogeneous Services translates this query to an appropriate query on systables and then sends the translated query to Sybase. For example:

SELECT SU."name" OWNER, SO."name" TABLE_NAME,
DECODE(SO."type", 'U ','TABLE', 'S ', 'TABLE', 'V ', 'VIEW')
TABLE_TYPE
FROM "dbo"."sysusers"@remote_db  SU, "dbo"."sysobjects"@remote_db SO
WHERE SU."uid" = SO."uid" AND
(SO."type" = 'V' OR SO."type" = 'S' OR SO."type" = 'U');

To relay the translation of a query on an Oracle data dictionary table to the equivalent one on the non-Oracle system data dictionary table, Heterogeneous Services needs data dictionary translations for that non-Oracle system. A data dictionary translation is a view definition (essentially a SELECT statement) of one or more non-Oracle system data dictionary tables that look exactly like the Oracle data dictionary table, with the same column names and the same information formatting. A data dictionary translation may not be as simple as the preceding example. Often the information needed is scattered over many tables and the data dictionary translation is a complex join ofthose tables.

In some cases, an Oracle data dictionary table does not have a translation because the information needed does not exist at the non-Oracle system. In such cases, the gateway can decide not to upload a translation at all or can resort to an alternative approach called mimicking. If the gateway wants to mimic a data dictionary table, it will let Heterogeneous Services know and Heterogeneous Services will obtain the description of the data dictionary table by querying the local database but when asked to fetch data, it will report that no rows were selected.

3.6 Datetime Data Types

Oracle has five datetime data types:

- TIMESTAMP

- TIMESTAMP WITH TIME ZONE

- TIMESTAMP WITH LOCAL TIME ZONE

- INTERVAL YEAR TO MONTH

- INTERVAL DAY TO SECOND

Heterogeneous Services generic code supports Oracle datetime data types in SQL and stored procedures. Heterogeneous Services do not support these data types in data dictionary translations or queries involving data dictionary translations.

Even though Heterogeneous Services generic code supports this, support for a particular gateway depends on whether or not the driver for that non-Oracle system has implemented datetime support. Support, even when the driver implements it, may be partial because of the limitations of the non-Oracle system. You should consult the documentation for your particular gateway on this issue.

You must set the timestamp formats of the non-Oracle system in the gateway initialization file. The parameters to set are HS_NLS_TIMESTAMP_FORMAT and HS_NLS_TIMESTAMP_TZ_FORMAT. You should also set the local time zone for the non-Oracle system in the initialization file by setting HS_TIME_ZONE.

See Also:

Oracle Database SQL Reference for information about datetime data types

3.7 Two-Phase Commit Protocol

Heterogeneous Services provides the infrastructure for the implementation of the two-phase commit mechanism. The extent to which this is supported depends on the gateway and the remote system. Refer to individual gateway manuals for more information.

See Also:

Oracle Database Administrator's Guide for more information about the two-phase commit protocol

3.8 Piecewise LONG Data Type

Earlier versions of gateways had limited support for the LONG data type. LONG is an Oracle data type that can be used to store up to 2 gigabytes (GB) of character data or raw data (LONG RAW). These earlier versions restricted the amount of LONG data to 4 MB because they treated LONG data as a single piece. This led to restrictions of memory and network bandwidth on the size of the data that could be handled. Current gateways have extended the functionality to support the full 2 GB of heterogeneous LONG data. They handle the data piecewise between the agent and the Oracle server, thereby doing away with the large memory and network bandwidth requirements.

The HS_LONG_PIECE_TRANSFER_SIZE Heterogeneous Services initialization parameter can be used to set the size of the transferred pieces. For example, consider fetching 2 GB of LONG data from a heterogeneous source. A smaller piece size means less memory requirement, but more round-trips to fetch all the data. A larger piece size means fewer round-trips, but more of a memory requirement to store the intermediate pieces internally. Thus the initialization parameter can be used to tune a system for the best performance, that is, for the best tradeoff between round-trips and memory requirements. If the initialization parameter is not set, the system defaults to a piece size of 64 KB.

Note:

Do not confuse this feature with piecewise operations on LONG data on the client side. Piecewise fetch and insert operations on the client side did work with the earlier versions of the gateways, and continue to do so. The only difference on the client side is that, where earlier versions of the gateways were able to fetch a maximum of 4 megabytes (MB) of LONG data, now they can fetch the entire 2 GB of LONG data. This is a significant improvement, considering that 4 MB is only 0.2% of the data type's full capacity.

3.9 SQL*Plus DESCRIBE Command

You can describe non-Oracle system objects using the SQL*Plus DESCRIBE command. However, there are some limitations. For example, using heterogeneous links, you cannot describe packages, sequences, synonyms, or types.

3.10 Constraints on SQL in a Distributed Environment

This section explains some of the constraints on SQL in a distributed environment. These constraints apply to distributed environments that access non-Oracle systems or remote Oracle databases.

This section contains the following topics:

- Remote and Heterogeneous References

- Important Restrictions

3.10.1 Remote and Heterogeneous References

Note:

Many of the rules for heterogeneous access also apply to remote references. For more information, please see the distributed database section of the Oracle Database Administrator's Guide.

A statement can, with restrictions, be executed on any database node referenced in the statement or the local node. If all objects referenced are resolved to a single, referenced node, Oracle attempts to execute a query at that node. You can force execution at a referenced node by using the /*+ REMOTE_MAPPED */ or /*+ DRIVING_SITE */ hints. If a statement is forwarded to a node other than the node from where the statement was issued, the statement is said to be remote-mapped.

There is complete data type checking support for remote-mapped statements. The result provides consistent data type checking and complete data type coercion.

See Also:

Section 4.5, "Oracle Database Server SQL Construct Processing"

The ways in which statements can be remote-mapped are subject to specific rules or restrictions. If these rules are not followed, an error occurs. As long as the statements issued are consistent with all these rules, the order in which the rules are applied does not matter. See Section 3.10.2 for these rules or restrictions.

Different constraints exist when you are using SQL for remote mapping in a distributed environment. This distributed environment can include remote Oracle databases as well as non-Oracle databases that are accessed through Oracle Transparent Gateways or Generic Connectivity agents.

3.10.2 Important Restrictions

The following section lists some of the different rules or restrictions that exist when you are using SQL for remote mapping in a distributed environment.

Note:

In the examples that follow, remote_db refers to a remote non-Oracle system while remote_oracle_db refers to a remote Oracle server.

Rule A: A data definition language statement cannot be remote-mapped.

In Oracle data definition language, the target object syntactically has no place for a remote reference. Data definition language statements that contain remote references are always executed locally. For Heterogeneous Services, this means it cannot directly create database objects in a non-Oracle database using SQL.

However, there is an indirect way using passthrough SQL as shown in the following example:

DECLARE
num_rows INTEGER;
BEGIN
num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@remote_db
(
'create table x1 (c1 char, c2 int)'
);
END;

Rule B: INSERT, UPDATE and DELETE statements with a remote target table must be remote-mapped.

This rule is more restrictive for non-Oracle remote databases than for a remote Oracle database. This is because the remote system cannot fetch data from the originating Oracle database while executing data manipulation language (DML) statements targeting tables in a non-Oracle system.

For example, to insert all local employees from the local emp table to a remote non-Oracle emp table, use the following statement:

This statement is remote-mapped to the remote database. The remote-mapped statement sent to the remote database contains a remote reference back to the originating database for emp. Such a remote link received by the remote database is called a callback link.

Note:

Even though callback links are supported in generic Heterogeneous Services, they may not be implemented in all Heterogeneous Services agents. Refer to your transparent gateway documentation to determine if callback links work with the transparent gateway that you are using.

If callback links are not supported by a particular gateway, the previous INSERT statements returns the following error:

ORA-02025: all tables in the SQL statement must be at the remote database

The workaround is to write a PL/SQL block. For example:

DECLARE
CURSOR remote_insert IS SELECT * FROM emp;
BEGIN
FOR rec IN remote_insert LOOP
INSERT INTO emp@remote_db (empno, ename, deptno) VALUES (
rec.empno,
rec.ename,
rec.deptno
);
END loop;
END;

Another special case involves session-specific SQL functions such as USER, USERENV, and SYSDATE. These functions need to be executed at the originating site. A remote-mapped statement containing these functions contains a callback link. For a non-Oracle database for which callbacks are not supported, this can (by default) result in a restriction error.

For example, consider the following statement:

DELETE FROM emp@remote_db WHERE hiredate > sysdate;

The previous statement returns the following error message:

ORA-02070: database REMOTE_DB does not support special functions in this context

This can be resolved by replacing special functions with a bind variable. For example:

DELETE FROM emp@remote_db WHERE hiredate > :1;

Rule C: Object features like tables with nested table columns, ADT columns, Opaque columns, or Ref Columns cannot be remote-mapped.

Currently, these column types are not supported for heterogeneous access. Hence, this limitation is not directly encountered.

Rule D: SQL statements containing operators and constructs that are not supported at the remote site cannot be remote-mapped.

In the case of an INSERT, UPDATE, or DELETE, this means that the SQL statement cannot be executed (see Rule B). However, you might still be able to execute the SQL statement if the unsupported operator or construct can be executed through a callback link.

In the case of a SELECT, you can always execute a statement affected by this rule as long as none of the remaining rules require the statement to be remote mapped. The SELECT statements affected by this rule are executed by fetching all the necessary data through a remote SELECT operation, and processing the unsupported operator or construct locally using the local SQL engine.

A remote SELECT operation is the operation that retrieves rows from the remote table as opposed to an operation that retrieves data from the local table. In the worse possible case of a remote SELECT, Oracle does a full table scan. A full table scan is a fetch of all the data in the remote table across the network without any filtering (for example, SELECT * FROM EMP).

Full table scans are expensive and, therefore, Oracle attempts to avoid them. If there are indexes on the remote table that can be used, these indexes are used in a WHERE clause predicate to reduce the number of rows fetched across the network.

You can check the SQL statement generated by the Oracle server by explaining the statement and querying the OTHER column of the explain plan table for each REMOTE operation.

See Also:

Section 3.11.1 for more information on how to interpret explain plans with remote references

For example, consider the following statement:

SELECT COUNT(*) FROM emp@remote_db WHERE hiredate < sysdate;

The statement returns the following output:

COUNT(*)
----------
14
1 row selected.

The remote table scan is:

SELECT hiredate FROM emp;

The predicate converted to a filter cannot be generated back and passed down to the remote operation because sysdate is not supported by the remote_db or evaluation rules. Thus sysdate must be executed locally.

Note:

Because the remote table scan operation is only partially related to the original query, the number of rows retrieved can be significantly larger than expected and can have a significant impact on performance.

Rule E: SQL statement containing a table expression cannot be remote-mapped.

This limitation is not directly encountered because table expressions are not supported in the heterogeneous access module.

Rule F: If a SQL statement selects LONG data, the statement must be mapped to the node where the table containing the LONG data resides.

Consider the following type of statement:

SELECT long1 FROM table_with_long@remote_db, dual;

The previous statement returns the following error message (if callback links are not supported):

ORA-02025: all tables in the SQL statement must be at the remote database

Rule G: The statement must be mapped to the node on which the table or tables with columns referenced in the FOR UPDATE OF clause resides when the SQL statement is of form "SELECT...FOR UPDATE OF..."

When the SQL statement is of the form SELECT...FOR UPDATE OF..., the statement must be mapped to the node on which the table or tables with columns referenced in the FOR UPDATE OF clause resides.

For example, consider the following statement:

SELECT ename FROM emp@remote_db WHERE hiredate < sysdate FOR UPDATE OF empno;

The previous statement returns the following error message if it cannot be remote-mapped:

ORA-02070: database REMOTE_DB does not support special functions in this context

Rule H: If the SQL statement contains a SEQUENCE or sequences, then the statement must be mapped to the site where each sequence resides.

This rule is not encountered for the heterogeneous access module because remote non-Oracle sequences are not supported.

Rule I: If the statement contains a user-defined operator or operators, then the statement must be mapped to the node where each operator is defined.

If the statement contains a user-defined operator, the entire statement needs to be remote-mapped to the database node where the operator is defined.

Rule J: A statement containing duplicate bind variables cannot be remote-mapped.

The workaround for this restriction is to use unique bind variables and bind by number.

3.11 Using Oracle's Optimizer with Heterogeneous Services

Oracle's optimizer can be used with Heterogeneous Services. Heterogeneous Services collects certain table and index statistics information on the respective non-Oracle system tables and passes this information back to the Oracle server. The Oracle cost-based optimizer uses this information when building the query plan.

There are several other optimizations that the cost-based optimizer performs. The most important ones are remote sort elimination and remote joins.

3.11.1 Example: Using Index and Table Statistics

Consider the following statement where you create a table in the Oracle database with 10 rows:

CREATE TABLE T1 (C1 number);

Analyze the table by issuing the following SQL statement:

ANALYZE TABLE T1 COMPUTE STATISTICS;

Create a table in the non-Oracle system with 1000 rows.

Issue the following SQL statement:

SELECT a.* FROM remote_t1@remote_db a, T1 b
WHERE a.C1 = b.C1;

The Oracle optimizer issues the following SQL statement to the agent:

SELECT C1 FROM remote_t1@remote_db;

This fetches all 1000 rows from the non-Oracle system and performs the join in the Oracle database.

If we add a unique index on the column C1 in the table remote_t1, and issue the same SQL statement again, the agent receives the following SQL statement for each value of C1 in the local t1:

...
SELECT C1 FROM remote_t1@remote_db WHERE C1 = ?;
...

Note:

? is the bind parameter marker. Also, join predicates containing bind variables generated by Oracle are generated only for nested loop join methods.

To verify the SQL execution plan, generate an explain plan for the SQL statement. First, load utlxplan in the admin directory.

Enter the following:

EXPLAIN PLAN FOR SELECT a.* FROM remote_t1@remote_db a, T1 b
WHERE a.C1 = b.C1;

Execute the utlxpls utility script by entering the following statement.

@utlxpls

OPERATION REMOTE indicates that remote SQL is being referenced.

To find out what statement is sent, enter the following statement:

SELECT ID, OTHER FROM PLAN_TABLE WHERE OPERATION = 'REMOTE';

3.11.2 Example: Remote Join Optimization

The following is an example of the remote join optimization capability of the Oracle database.

Note:

The explain plan that uses tables from a non-Oracle system can differ from similar statements with local or remote Oracle table scans. This is because of the limitation on the statistics available to Oracle for non-Oracle tables. Most importantly, column selectivity is not available for non-unique indexes of non-Oracle tables. Because of the limitation of the statistics available, the following example is not necessarily what you encounter when doing remote joins and is intended for illustration only.

Consider the following example:

EXPLAIN PLAN FOR
SELECT e.ename, d.dname, f.ename, f.deptno FROM
dept d,
emp@remote_db e,
emp@remote_db f
WHERE e.mgr = f.empno
AND e.deptno = d.deptno
AND e.empno = f.empno;

@utlxpls

You should see output similar to the following:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id    | Operation                           | Name  | Rows  | Bytes  | Cost
| Inst  |IN-OUT|
---------------------------------------------------------------------------
|   0   | SELECT STATEMENT   |                |  2000   |   197K|   205 |
|*  1   | HASH JOIN          |                |  2000   |   197K|
205 |
|   2   | TABLE ACCESS FULL  | DEPT           |  21     |   462 |     2 |
|*  3   | HASH JOIN          |                |  2000   |   154K|
201 |
|   4   | REMOTE             |                |  2000   | 66000
|    52 |
|   5   | REMOTE             |                |  2000   | 92000
|    52 |
---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Query Block Name / Hint Alias (identified by operation id):
-----------------------------------------------------------

1 - sel$1 / D
2 - sel$1 / D
3 - sel$1 / F
4 - sel$1 / F
5 - sel$1 / E

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------

1 - access("E"."DEPTNO"="D"."DEPTNO")
3 - access("E"."MGR"="F"."EMPNO" AND "E"."EMPNO"="F"."EMPNO")

Issue the following statement:

SET long 300
SELECT other FROM plan_table WHERE operation = 'REMOTE';

You should see output similar to the following:

OTHER
--------------------------------------------------------------------------------

SELECT "EMPNO","ENAME","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","MGR","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","MGR","DEPTNO" FROM "EMP"

3.11.3 Optimizer Restrictions for Non-Oracle Access

The following are optimizer restrictions for non-Oracle system access:

- There are no column statistics for remote objects. This can result in poor execution plans. Verify the execution plan and use hints to improve the plan.

- There is no optimizer hint to force a remote join. However, there is a remote query block optimization that can be used to rewrite the query slightly in order to get a remote join.

The example from the previous section can be rewritten to the following form:

SELECT v.ename, d.dname, d.deptno FROM dept d,
(SELECT /*+ NO_MERGE */
e.deptno deptno, e.ename ename emp@remote_db e, emp@remote_db f
WHERE e.mgr = f.empno
AND e.empno = f.empno;
)
WHERE v.deptno = d.deptno;

This example guarantees a remote join because it has been isolated in a nested query with the NO_MERGE hint.

4 Using Heterogeneous Services Agents

This chapter explains how to use Heterogeneous Services (HS) agents. It contains the following sections:

- Setting Up Access to Non-Oracle Systems

- Setting Initialization Parameters

- Optimizing Data Transfers Using Bulk Fetch

- Registering Agents

- Oracle Database Server SQL Construct Processing

- Executing User-Defined Functions on a Non-Oracle Database

- 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

- Using Heterogeneous Services Data Dictionary Views

- Using the Heterogeneous Services Dynamic Performance Views

4.1 Setting Up Access to Non-Oracle Systems

This section explains the generic steps to configure access to a non-Oracle system.

Note:

The instructions for configuring your agent may differ slightly from the following instructions. Please see the installation and user's guide for your specific agent for more complete installation information.

The steps for setting up access to a non-Oracle system are:

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

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

Step 3: Test the Connection

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.

See Also:

- Oracle Database Net Services Administrator's Guide for more information about configuring the Oracle Net listener and a net service name for heterogeneous connectivity

- Section 4.2.1 about the initialization parameter file

- Section 5.3 for information about starting multithreaded agents

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:

Type of Data

Explanation

Capabilities of the non-Oracle system

The agent specifies whether it can perform a join or a GROUP BY.

SQL translation information

The agent specifies how to translate Oracle functions and operators into functions and operators of the non-Oracle system.

Data dictionary translations

To make the data dictionary information of the non-Oracle system available just as if it were an Oracle data dictionary, the agent specifies how to translate Oracle data dictionary tables into tables and views of the non-Oracle system.

Note:

Most agents upload information into the Oracle data dictionary automatically the first time they are accessed. Some agent vendors may provide scripts, however, that you must run on the Oracle server.

See Also:

Section 4.10 and Appendix C, "Data Dictionary Translation Support"

4.2 Setting Initialization Parameters

Configure the gateway using initialization parameters. This is done by creating an initialization file and setting the desired parameters in this file. See Section 2.5 for configuration information.

Heterogeneous Services initialization parameters are distinct from Oracle database server initialization parameters. Heterogeneous Services initialization parameters are set in the Heterogeneous Services initialization file and not in the Oracle database initialization parameter file (init.ora file). There is a Heterogeneous Services initialization file for each gateway instance.

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.

4.2.3.1 Using the tg4pwd Utility

The tg4pwd utility is used to encrypt initialization parameters that would normally be stored in the initialization parameter file in plain text. The utility works by reading the initialization parameter file in the current directory and looking for parameters having a security marker for the value. The security marker is an unquoted asterisk (*). This designates that the value of this parameter is to be stored in an encrypted form in a password file. The following is an example of an initialization parameter set to this value:

HS_FDS_CONNECT_INFO = *

The initialization parameter file in the current directory is first edited to set the value of the parameter to this security marker. Then the utility is run, specifying the gateway SID on the command line, with an optional user ID to designate a different owner of the encrypted information. The utility reads the initialization parameter file and prompts you to enter the real values that are to be encrypted. The syntax of the command is:

tg4pwd [sid] {userid}

Where [sid] is the SID of the gateway and {userid} is an optional user ID used to encrypt the contents. If no user ID is specified, then the current user's ID is used. Values are encrypted using this ID. In order to decrypt the values, the agent must be run as that user. The following example assumes a gateway SID of SYBASE:

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

In the previous example, the initialization parameter file, initSYBASE.ora, is read. The parameter, HS_FDS_CONNECT_INFO, is identified as requiring encryption. Enter the value (for example, sybasew) and presses enter. If more parameters require encryption, they are prompted for in turn. The encrypted data is stored in the same directory as the initialization file.Any initialization parameters needing encryption should be encrypted before using the Gateway or Generic Connectivity.

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:

HS_CALL_NAME
HS_COMMIT_POINT_STRENGTH
HS_DB_DOMAIN
HS_DB_INTERNAL_NAME
HS_DB_NAME
HS_DESCRIBE_CACHE_HWM
HS_FDS_CONNECT_INFO
HS_FDS_DEFAULT_SCHEMA_NAME
HS_FDS_SHAREABLE_NAME
HS_FDS_TRACE_LEVEL
HS_LANGUAGE
HS_LONG_PIECE_TRANSFER_SIZE
HS_NLS_DATE_FORMAT
HS_NLS_DATE_LANGUAGE
HS_NLS_NCHAR
HS_NLS_NUMERIC_CHARACTERS
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

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.

See Also:

- Appendix A, "Heterogeneous Services Initialization Parameters" for descriptions of the generic Heterogeneous Services initialization parameters

- Individual gateway documentation for the list of initialization parameters specific to a gateway

4.3 Optimizing Data Transfers Using Bulk Fetch

When an application fetches data from a non-Oracle system using Heterogeneous Services, data is transferred:

1. From the non-Oracle system to the agent process.

2. From the agent process to the Oracle database server.

3. From the Oracle database server to the application.

Oracle optimizes all three data transfers, as illustrated in Figure 4-1.

Figure 4-1 Optimizing Data Transfers

This section contains the following topics:

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

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

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

- Controlling the Reblocking of Array Fetches

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 Registering Agents

Registration is an operation through which Oracle stores information about an agent in the data dictionary. Agents do not have to be registered. If an agent is not registered, Oracle stores information about the agent in memory instead of in the data dictionary. When a session involving an agent terminates, this information ceases to be available.

Self-registration is an operation in which a database administrator sets an initialization parameter that lets the agent automatically upload information into the data dictionary. Self-registration occurs when the HS_AUTOREGISTER initialization parameter is set to TRUE (default).

Note:

HS_AUTOREGISTER is an Oracle initialization parameter that you set in the init.ora file; it is not a Heterogeneous Services initialization parameter that is set in the gateway initialization file.

This section contains the following topics:

- Enabling Agent Self-Registration

- Disabling Agent Self-Registration

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:

Heterogeneous Services Configuration Data

Description

Heterogeneous Services initialization parameters

Provide control over various connection-specific details of operation.

Capability definitions

Identify details like SQL language features supported by the non-Oracle data source.

Data dictionary translations

Map references to Oracle data dictionary tables and views into equivalents specific to the non-Oracle data source.

See Also:

Section 4.4.1.3, "Specifying HS_AUTOREGISTER"

4.4.1.1 Using Agent Self-Registration to Avoid Configuration Mismatches

HS configuration data is stored in the data dictionary of the Oracle database server. Because the agent may be remote and may therefore be administered separately, several circumstances can lead to configuration mismatches between servers and agents. For example:

- An agent can be newly installed on a separate machine so that the server has no Heterogeneous Services data dictionary content to represent the agent's Heterogeneous Services configuration data.

- A server can be newly installed and lack the necessary Heterogeneous Services configuration data for existing agents and non-Oracle data stores.

- A non-Oracle instance can be upgraded from an older version to a newer version, requiring modification of the Heterogeneous Services configuration data.

- A Heterogeneous Services agent at a remote site can be upgraded to a new version or patched, requiring modification of the Heterogeneous Services configuration data.

- A database administrator (DBA) at the non-Oracle site can change the agent setup, possibly for tuning or testing purposes, in a manner which affects Heterogeneous Services configuration data.

Agent self-registration permits successful operation of Heterogeneous Services in all these scenarios. Specifically, agent self-registration enhances interoperability between any Oracle database server and any Heterogeneous Services agent (if each is Version 8.0.3 or higher). The basic mechanism for this functionality is the ability to upload Heterogeneous Services configuration data from agents to servers.

Self-registration provides automatic updating of Heterogeneous Services configuration data residing in the Oracle database server data dictionary. This update ensures that the agent self-registration uploads need to be done only once, on the initial use of a previously unregistered agent. Instance information is uploaded on each connection, not stored in the server data dictionary.

4.4.1.2 Understanding Agent Self-Registration

The Heterogeneous Services agent self-registration feature can perform the following tasks:

- Identify the agent and the non-Oracle data store to the Oracle database server

- Permit agents to define Heterogeneous Services initialization parameters for use both by the agent and connected Oracle servers

- Upload capability definitions and data dictionary translations, if available, from a Heterogeneous Services agent during connection initialization

Note:

The upload of class information occurs only when the class is undefined in the server data dictionary. Similarly, instance information is uploaded only if the instance is undefined in the server data dictionary.

The information required for agent self-registration is accessed in the server data dictionary by using these agent-supplied names:

- FDS_CLASS

- FDS_CLASS_VERSION

See Also:

Section 4.10, "Using Heterogeneous Services Data Dictionary Views" to learn how to use the Heterogeneous Services data dictionary views

4.4.1.2.1 FDS_CLASS and FDS_CLASS_VERSION

FDS_CLASS and FDS_CLASS_VERSION are defined by Oracle or by third-party vendors for each individual Heterogeneous Services agent and version. Oracle Heterogeneous Services concatenates these names to form FDS_CLASS_NAME, which is used as a primary key to access class information in the server data dictionary.

FDS_CLASS should specify the type of non-Oracle data store to be accessed and FDS_CLASS_VERSION should specify a version number for both the non-Oracle data store and the agent that connects to it. Note that when any component of an agent changes, FDS_CLASS_VERSION must also change to uniquely identify the new release.

Note:

This information is uploaded when you initialize each connection.

4.4.1.2.2 FDS_INST_NAME

Instance-Specific Information can be stored in the server data dictionary. The instance name, FDS_INST_NAME, is configured by the database administrator (DBA) who administers the agent. How the DBA performs this configuration depends on the specific agent in use.

The Oracle database server uses FDS_INST_NAME to look up instance-specific configuration information in its data dictionary. Oracle uses the value as a primary key for columns of the same name in these views:

- FDS_INST_INIT

- FDS_INST_CAPS

- FDS_INST_DD

Server data dictionary accesses that use FDS_INST_NAME also use FDS_CLASS_NAME to uniquely identify configuration information rows. For example, if you port a database from class Sybase816 to class Sybase817, both databases can simultaneously operate with instance name SCOTT and use separate sets of configuration information.

Unlike class information, instance information is not automatically self-registered in the server data dictionary:

- If available, instance information is always uploaded by the agent. However, it is never stored in the server data dictionary. Instead, the information is kept in memory and it is only valid for that connection.

- If the server data dictionary contains instance information, it represents the DBA's defined setup details which correspond to the instance configuration. Data dictionary defined instance information takes precedence over class information. However, uploaded instance information takes precedence over data dictionary defined instance information.

4.4.1.3 Specifying HS_AUTOREGISTER

The HS_AUTOREGISTER Oracle database server initialization parameter enables or disables automatic self-registration of Heterogeneous Services agents. Note that this parameter is specified in the Oracle initialization parameter file, not the agent initialization file. For example, you can set the parameter as follows:

HS_AUTOREGISTER = TRUE

When set to TRUE, the agent uploads information describing a previously unknown agent class or a new agent version into the server's data dictionary.

Oracle recommends that you use the default value for this parameter (TRUE), which ensures that the server's data dictionary content always correctly represents definitions of class capabilities and data dictionary translations as used in Heterogeneous Services connections.

See Also:

Oracle Database Reference for a description of this parameter

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 Oracle Database Server SQL Construct Processing

Heterogeneous Services and the gateway rewrite SQL statements when the statements need to be translated or postprocessed.

For the following examples, assume the INITCAP function is not supported in the non-Oracle database. Consider a program that requests the following from the non-Oracle database. For example:

SELECT "COLUMN_A" FROM "test"@remote_db
WHERE "COLUMN_A" = INITCAP("COLUMN_B");

The non-Oracle database does not recognize the INITCAP function, so the Oracle database server fetches the data from the table test in the remote database and filters the results locally. The gateway rewrites the SELECT statement as follows:

SELECT "COLUMN_A", "COLUMN_B" FROM "test"@remote_db;

The results of the query are sent from the gateway to Oracle and are filtered by the Oracle database server.

If a string literal or bind variable is supplied in place of "COLUMN_B" as shown in the previous example, the Heterogeneous Services component of the Oracle server would apply the INITCAP function before sending the SQL command to the gateway. For example, if the following SQL command is issued:

SELECT "COLUMN_A" FROM "test"@remote_db WHERE "COLUMN_A" = INITCAP('jones');

The following SQL command would be sent to the gateway:

SELECT "COLUMN_A" FROM "test"@remote_db WHERE "COLUMN_A" = 'Jones';

Consider the following UPDATE request:

UPDATE "test"@remote_db SET "COLUMN_A" = 'new_value'
WHERE "COLUMN_A" = INITCAP("COLUMN_B");

In this case, the Oracle database server and the gateway cannot compensate for the lack of support at the non-Oracle side, so an error is issued.

If a string literal or bind variable is supplied in place of "COLUMN_B" as shown in the preceding example, the Heterogeneous Services component of the Oracle server would apply the INITCAP function before sending the SQL command to the gateway. For example, if the following SQL command is issued:

UPDATE "test"@remote_db SET "COLUMN_A" = 'new_value'
WHERE "COLUMN_A" = INITCAP('jones');

The following SQL command would be sent to the gateway:

UPDATE  "test"@remote_db SET "COLUMN_A" = 'new_value'
WHERE "COLUMN_A" = 'Jones';

In previous releases, the preceding UPDATE statement would have raised an error due to the lack of INITCAP function support in the non-Oracle database.

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.6 Executing User-Defined Functions on a Non-Oracle Database

You can execute user-defined functions in a remote non-Oracle database. For example:

SELECT getdeptforemp@Remote_DB(7782) FROM dual;

In this example, a SELECT statement was issued that executes a user-defined function in the remote database that returns department information for employee 7782.

When the remote function resides in an Oracle database, the Oracle database automatically ensures that the remote function does not update any database state (such as updating rows in a database or updating the PL/SQL package state). The gateway cannot verify this when the remote function resides in a non-Oracle database. Therefore, you are responsible for ensuring that the user-defined functions do not update the state in any database. Ensuring no updates to the database is required to guarantee read consistency.

As a security measure, you must specify the functions that you want to execute remotely and their owners in the HS_CALL_NAME parameter in the gateway-specific initialization parameter file. For example:

HS_CALL_NAME = "owner1.A1, owner2.A2 "

owner1 and owner2 are the remote function owner names. A1 and A2 are the remote function names. You do not need to specify the remote function owner in the SQL statement. By default, the remote function needs to reside in the schema that the Transparent Gateway connects to. If this is not the case, then you must specify the owner of the remote function in the SQL statement.

Some other examples of executing user-defined remote functions are as follows:

- A remote function in a subquery

The function uses the employee_id column data to retrieve the department_id from the EMPLOYEES table in the remote database. The outer query then determines all department numbers in the remote database that match the returned list.

SELECT * FROM departments@remotedb
WHERE department_id IN
(SELECT
getdeptforemp@remotedb (employee_id)
FROM employees@remotedb);

- Applying a local function to the result of a user-defined remote function

This query returns the maximum salary of all employees on the remote database.

SELECT max (getsalforemp@remotedb (employee_id))
FROM employees@remotedb;

- A DML statement

The statement uses the output from a user-defined query in the remote database to update the salary column with new salary information.

UPDDATE employee_history
SET salary = emp_changed_salary@remote_db;

In these examples, the Oracle database passes the function name and owner to the Transparent Gateway. The user-defined function is executed on the remote database.

See Also:

Section A.1, "HS_CALL_NAME"

4.7 Using Synonyms

You can provide complete data location transparency and network transparency by using the synonym feature of the Oracle database server. When a synonym is defined, you do not have to know the underlying table or network protocol. A synonym can be public, which means that all Oracle users can refer to the synonym. A synonym can also be defined as private, which means every Oracle user must have a synonym defined to access the non-Oracle table.

The following statement creates a system-wide synonym for the emp table in the schema of user ORACLE in the Sybase database:

CREATE PUBLIC SYNONYM emp FOR "ORACLE"."EMP"@SYBS;

See Also:

Oracle Database Administrator's Guide for information about synonyms

4.7.1 Example of a Distributed Query

Note:

Modify these examples for your environment. Do not try to execute them as they are written.

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.8 Copying Data from the Oracle Database Server to the Non-Oracle Database System

Heterogeneous Services supports callback links. This enables SQL statements like the following to be executed:

INSERT INTO table_name@dblink SELECT column_list FROM table_name;

Even though Heterogeneous Services supports the callback functionality, not all gateways have implemented it. If the gateway that you are using has not implemented this functionality, the preceding INSERT statement returns the following error message:

ORA-02025: All tables in the SQL statement must be at the remote database

See Also:

Your gateway documentation for information about support for callback links

For gateways that do not support callback links, you can use the SQL*Plus COPY command. The syntax is as follows:

COPY FROM username/password@db_name -
INSERT destination_table -
USING query;

The following example selects all rows from the local Oracle emp table, inserts them into the emp table on the non-Oracle database, and commits the transaction:

COPY FROM SCOTT/TIGER@inst1 -
INSERT EMP@remote_db -
USING SELECT * FROM EMP;

The COPY command supports the APPEND, CREATE, INSERT, and REPLACE options. However, INSERT is the only option supported when copying to non-Oracle databases. The SQL*Plus COPY command does not support copying to tables with lowercase table names. Use the following PL/SQL syntax with lowercase table names:

DECLARE
v1 oracle_table.column1%TYPE;
v2 oracle_table.column2%TYPE;
v3 oracle_table.column3%TYPE;
.
.
.
CURSOR cursor_name IS SELECT * FROM oracle_table;
BEGIN
OPEN cursor_name;
LOOP
FETCH cursor_name INTO v1, v2, v3, ... ;
EXIT WHEN cursor_name%NOTFOUND;
INSERT INTO destination_table VALUES (v1, v2, v3, ...);
END LOOP;

CLOSE cursor_name;
END;

See Also:

SQL*Plus User's Guide and Reference for more information about the COPY command

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

The CREATE TABLE statement lets you copy data from a non-Oracle database to the Oracle database. To create a table on the local database and insert rows from the non-Oracle table, use the following syntax:

CREATE TABLE table_name AS query;

The following example creates the table emp in the local Oracle database and inserts the rows from the EMP table of the non-Oracle database:

CREATE TABLE table1 AS SELECT * FROM "EMP"@remote_db;

Alternatively, you can use the SQL*Plus COPY command to copy data from the non-Oracle database to the Oracle database server.

See Also:

SQL*Plus User's Guide and Reference for more information about the COPY command

4.10 Using Heterogeneous Services Data Dictionary Views

You can use the Heterogeneous Services data dictionary views to access information about Heterogeneous Services. This section addresses the following topics:

- 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

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.

Table 4-1 Data Dictionary Views for Heterogeneous Services

View

Type

Identifies

HS_BASE_CAPS

SQL service

All capabilities supported by Heterogeneous Services

HS_BASE_DD

SQL service

All data dictionary translation table names supported by Heterogeneous Services

HS_CLASS_CAPS

Transaction service, SQL service

Capabilities for each class

HS_CLASS_DD

SQL service

Data dictionary translations for each class

HS_CLASS_INIT

General

Initialization parameters for each class

HS_FDS_CLASS

General

Classes accessible from the Oracle server

HS_FDS_INST

General

Instances accessible from the Oracle server

HS_INST_CAPS

Transaction service, SQL service

Capabilities for each instance (if set up by the DBA)

HS_INST_DD

SQL service

Data dictionary translations for each class (if set up by the DBA)

HS_INST_INIT

General

Initialization parameters for each instance (if set up by the DBA)

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.

Note:

It is not possible to determine positively what capabilities and what data dictionary translations are in use for a given session due to the possibility that an agent can upload instance 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:

View

Contains

HS_FDS_CLASS

Names of the classes that are uploaded into the Oracle data dictionary

HS_FDS_INST

Names of the instances that are uploaded into the Oracle data dictionary

HS_CLASS_INIT

Information about the Heterogeneous Services initialization parameters

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.

Type

Capability

Read-Only (RO)

The non-Oracle system can be queried only with SQL SELECT statements. Procedure calls are not allowed because procedure calls are assumed to write data.

Single-Site (SS)

The non-Oracle system can handle remote transactions but not distributed transactions. That is, it cannot participate in the two-phase commit protocol.

Commit Confirm (CC)

The non-Oracle system can participate in distributed transactions. It can participate in the server's two-phase commit protocol but only as the commit point site. That is, it cannot prepare data, but it can remember the outcome of a particular transaction if asked by the global coordinator.

Two-Phase Commit (2PC)

The non-Oracle system can participate in distributed transactions. It can participate in the server's two-phase commit protocol, as a regular two-phase commit node, but not as a commit point site. That is, it can prepare data, but it cannot remember the outcome of a particular transaction if asked to by the global coordinator.

Two-Phase Commit Confirm (2PCC)

The non-Oracle system can participate in distributed transactions. It can participate in the server's two-phase commit protocol as a regular two-phase commit node or as the commit point site. That is, it can prepare data and it can remember the outcome of a particular transaction if asked by the global coordinator.

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.

4.10.5.1 Using Views for Capabilities and Translations

The HS_*_CAPS data dictionary tables contain information about the SQL capabilities of the non-Oracle data source and required SQL translations. These views specify whether the non-Oracle data store or the Oracle database server implements certain SQL language features. If a capability is turned off, then Oracle does not send any SQL statements to the non-Oracle data source that require this particular capability, but it still performs postprocessing.

4.10.5.2 Using Views for Data Dictionary Translations

In order to make the non-Oracle system appear similar to an Oracle database server, Heterogeneous Services connections map a limited set of Oracle data dictionary views onto the non-Oracle system's data dictionary. This mapping permits applications to issue queries as if these views belonged to an Oracle data dictionary. Data dictionary translations make this access possible. These translations are stored in Heterogeneous Services views whose names have the _DD suffix.

For example, the following SELECT statement transforms into a Sybase query that retrieves information about emp tables from the Sybase data dictionary table:

SELECT * FROM USER_TABLES@remote_db WHERE UPPER(TABLE_NAME)='EMP';
Data dictionary tables can be mimicked instead of translated. If a data dictionary translation is not possible because the non-Oracle data source does not have the required information in its data dictionary, then Heterogeneous Services causes it to appear as if the data dictionary table is available, but the table contains no information.

To retrieve information about which Oracle data dictionary views or tables are translated or mimicked for the non-Oracle system, connect as user SYS and issue the following query on the HS_CLASS_DD view:

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

The T translation type specifies that a translation exists. When the translation type is M, the data dictionary table is mimicked.

See Also:

Appendix C, "Data Dictionary Translation Support" for a list of data dictionary views that are supported through Heterogeneous Services mapping

4.11 Using the Heterogeneous Services Dynamic Performance Views

The Oracle database server stores information about agents, sessions, and parameters. You can use the dynamic performance views to access this information. This section contains the following topics:

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

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

- Determining the Heterogeneous Services Parameters: V$HS_PARAMETER View

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.

Table 4-2 Important Columns in the V$HS_AGENT View

Column

Description

AGENT_ID

Oracle Net session identifier used for connections to agent (listener.ora SID)

MACHINE

Operating system machine name

PROGRAM

Program name of agent

AGENT_TYPE

Type of agent

FDS_CLASS_ID

The ID of the foreign data store class

FDS_INST_ID

The instance name of the foreign data store

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.

Table 4-3 Important Columns in the V$HS_SESSION View

Column

Description

HS_SESSION_ID

Unique Heterogeneous Services session identifier

AGENT_ID

Oracle Net session identifier used for connections to agent (listener.ora SID)

DB_LINK

Server database link name used to access the agent NULL means that no database link is used (for example, when using external procedures)

DB_LINK_OWNER

Owner of the database link in DB_LINK

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.

Table 4-4 Important Columns in the V$HS_SESSION View

Column

Description

HS_SESSION_ID

Unique Heterogeneous Services session identifier

PARAMETER

The name of the Heterogeneous Services parameter

VALUE

The value of the Heterogeneous Services parameter

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.

5 Multithreaded Agents

This chapter explains what multithreaded agents are, how they contribute to the overall efficiency of a distributed database system, and how to administer multithreaded agents.

This chapter contains the following sections:

- Why Use Multithreaded Agents?

- Multithreaded Agent Architecture

- Administering Multithreaded Agents

Note:

Heterogeneous Services supports multithreaded agents, but this functionality may not be available in all Heterogeneous Services gateways. In addition, multithreaded agents support only Oracle Transparent Gateways, not Generic Connectivity Agents like ODBC. Refer to your Transparent Gateway documentation to determine if your gateway is certified to work as a multithreaded agent.

See Also:

Oracle Database Application Developer's Guide - Fundamentals for information about configuring the extproc agent to run in multithreaded agent (MTA) mode

5.1 Why Use Multithreaded Agents?

This section explains how multithreaded agents contribute to the efficiency of Heterogeneous Services and Oracle Transparent Gateways.

This section contains the following topics:

- The Challenge of Dedicated Agent Architecture

- The Advantage of Multithreading

5.1.1 The Challenge of Dedicated Agent Architecture

By default, a Heterogeneous Services agent is started for each user session. When a user session attempts to access a non-Oracle system through a database link, an agent process is started that is dedicated to that user session and that database link. The agent process terminates only when the user session ends or when the database link is closed. Separate agent processes are started under the following conditions:

- The same user session uses two different database links to connect to the same non-Oracle system

- Two different user sessions use the same database link to access the same non-Oracle system

This architecture may consume an unnecessarily large amount of system resources. For example, suppose that there are several thousand user sessions simultaneously accessing the same non-Oracle system. Because an agent process is started for each session, there are several thousand agent processes running concurrently. The agent processes operate regardless of whether each individual agent process is actually active at the moment. Thus agent processes and open connections can consume a disproportionate amount of system resources. When sessions connect to the Oracle database server, this problem is addressed by starting the server in shared server mode. Shared server mode allows database connections to be shared by a small number of server processes.

5.1.2 The Advantage of Multithreading

The Oracle shared server architecture assumes that even when there are several thousand user sessions currently open, only a small percentage of these connections are active at any given time. In shared server mode, there is a pool of shared server processes. User sessions connect to dispatcher processes that place the tasks requested in a queue. The tasks are picked up by the first available shared server processes. The number of shared server processes is usually less that the number of user sessions.

Multithreaded Heterogeneous Services agents provide similar functionality for connections to non-Oracle systems. The multithreaded agent architecture uses a pool of shared agent threads. The tasks requested by the user sessions are put in a queue and are picked up by the first available multithreaded agent thread. Because only a small percentage of user connections are active at a given moment, using a multithreaded architecture allows for more efficient use of system resources.

5.2 Multithreaded Agent Architecture

One multithreaded agent must be started for each system identifier (SID) before any attempt is made to connect to the non-Oracle system. This is done using the agent control utility agtctl. This utility is also used to configure the agent and to shut down the agent.

Each Oracle Net listener that is running on a system listens for incoming connection requests for a set of SIDs. If the SID in an incoming Oracle Net connect string is one of the SIDs that the listener is listening for, then that listener processes the connection. Further, if a multithreaded agent has been started for the SID, then the listener passes the request to that agent.

In the architecture for multithreaded agents, each incoming connection request is processed by different kinds of threads:

- A single monitor thread. The monitor thread is responsible for the following:

- Maintaining communication with the listener

- Monitoring the load on the process

- Starting and stopping threads when required

- Several dispatcher threads. The dispatcher threads are responsible for the following:

- Handling communication with the Oracle server

- Passing task requests to the task threads

- Several task threads. The task threads handle requests from the Oracle processes.

The multithreaded agent architecture is illustrated in Figure 5-1. Each request issued by a user session is represented by a separate type of arrow. There is no representation of the monitor thread in this illustration, because the monitor thread is created once when the multithreaded agent is started, and it creates and monitors the other threads. There are usually many more task threads than dispatcher threads.

Figure 5-1 Multithreaded Agent Architecture

These three thread types roughly correspond to the Oracle multithreaded server PMON, dispatcher, and shared server processes, respectively.

Note:

All requests from a user session go through the same dispatcher thread, but can be serviced by different task threads. It is also possible for several task threads to use the same connection to the non-Oracle system.

Each type of thread is discussed in more detail in the following sections:

- Monitor Thread

- Dispatcher Threads

- Task Threads

See Also:

Section 5.3, "Administering Multithreaded Agents" for more information about starting and stopping multithreaded agents by using the agent control utility

5.2.1 Monitor Thread

When the agent control utility starts a multithreaded agent for a SID, the agent control utility creates the monitor thread. The monitor thread performs the following functions:

- It creates the dispatcher and task threads.

- It registers the dispatcher threads with all the listeners that are handling connections to this agent. While the dispatcher for this SID is running, the listener does not start a new process when it gets an incoming connection. Instead, the listener hands over the connection to this same dispatcher.

- It monitors the other threads and sends load information about the dispatcher threads to all the listener processes handling connections to this agent. This enables the listeners to hand over incoming connections to the least loaded dispatcher.

- It continues to monitors each of the threads it has created.

5.2.2 Dispatcher Threads

Dispatcher threads perform the following functions:

- They accept incoming connections and task requests from Oracle servers.

- They place incoming requests on a queue for a task thread to pick up.

- They send results of a request back to the server that issued the request.

Note:

After a user session establishes a connection with a dispatcher, all requests from that user session go to the same dispatcher until the end of the user session.

5.2.3 Task Threads

Task threads perform the following functions:

- They pick up requests from a queue.

- They perform the necessary operations.

- They place the results on a queue for a dispatcher to pick up.

5.3 Administering Multithreaded Agents

One multithreaded Heterogeneous Services agent must be started for each system identifier (SID) before any attempt is made to connect to the non-Oracle system. Any agent not started in this fashion will not function in multithreaded mode and must be set up as described in Section 4.1, "Setting Up Access to Non-Oracle Systems".

A multithreaded agent is started, stopped, and configured by an agent control utility called agtctl, which works like lsnrctl. However, unlike lsnrctl, which reads a configuration file (listener.ora), agtctl takes configuration information from the command line and writes it to a control file.

The following topics are discussed in this section:

- Agent Control Utility (agtctl) Commands

- Using agtctl in Single-Line Command Mode

- Using Shell Mode Commands

- Configuration Parameters for Multithreaded Agent Control

5.3.1 Agent Control Utility (agtctl) Commands

You can start and stop agtctl and create and maintain its control file by using the commands shown in Table 5-1.

Table 5-1 Agent Control Utility Commands

Command

Description

startup

Starts a multithreaded agent

shutdown

Stops a multithreaded agent

set

Sets a configuration parameter for a multithreaded agent

unset

Causes a parameter to revert to its default value

show

Displays the value of a configuration parameter

delete

Deletes the entry for a particular SID from the control file

exit

Exits shell mode

help

Lists available commands

These commands can be issued in one of two ways:

- You can issue commands from the UNIX or DOS shell. This mode is called single-line command mode.

- You can enter agtctl and an AGTCTL> prompt appears. You then can enter commands from within the agtctl shell. This mode is called shell mode.

The syntax and parameters for agtctl commands depend on the mode in which they are issued.

Note:

- All commands are case-sensitive.

- The agent control utility puts its control file in either the directory pointed to by the AGTCTL_ADMIN environment variable or in the directory pointed to by the TNS_ADMIN environment variable. Ensure that at least one of these environment variables is set and that it points to a directory to which the agent has access.

- If the Heterogeneous Services agent requires an environment variable to be set, or if the ENVS parameter was used when configuring the listener.ora entry for the agent working in dedicated mode, then all required environment variables must be set in the UNIX or DOS shell that runs the agtctl utility.

5.3.2 Using agtctl in Single-Line Command Mode

This section describes the use of agtctl commands. They are presented in single-line command mode.

5.3.2.1 Setting Configuration Parameters for a Multithreaded Agent

Set the configuration parameters for a multithreaded agent before you start the agent. If a configuration parameter is not specifically set, a default value is used. Configuration parameters and their default values are shown in Table 5-2.

Use the set command to set multithreaded agent configuration parameters.

Syntax

agtctl set  parameter  parameter_value  agent_sid

parameter is the parameter that you are setting.
parameter_value is the value being assigned to that parameter.
agent_sid is the SID that this agent will service. This must be specified for single-line command mode.
Example

agtctl  set  max_dispatchers 5 salesDB

5.3.2.2 Starting a Multithreaded Agent

Use the startup command to start an agent in multithreaded mode.

Syntax

agtctl startup  agent_name  agent_sid

agent_name is the name of the agent executable. For example, extproc is an agent name.
agent_sid is the SID that this agent will service. This must be specified for single-line command mode.
Example

agtctl startup  extproc  salesDB

5.3.2.3 Shutting Down a Multithreaded Agent

Use the shutdown command to stop a multithreaded agent. There are three forms of shutdown:

- Normal

Normal shutdown is the default. It causes agtctl to talk to the agent and ask it to terminate itself gracefully. All sessions complete the operations they are currently doing and then shut down.

- Immediate

For an Immediate shutdown, agtctl talks to the agent and tells it to terminate immediately. The agent process exits immediately regardless of the state of current sessions.

- Abort

For an Abort shutdown, agtctl does not talk to the agent at all. It issues a system call to kill the agent process.

Syntax

agtctl shutdown [immediate|abort] agent_sid

agent_sid is the SID that the agent services. It must be specified for single-line command mode.

Example

agtctl shutdown immediate salesDB

5.3.2.4 Examining the Value of Configuration Parameters

To examine the value of a configuration parameter, use the show command.

Syntax

agtctl  show  parameter agent_sid

parameter is the parameter that you are examining.
agent_sid is the SID that this agent will service. This must be specified for single-line command mode.
Example

agtctl  show  max_dispatchers salesDB

5.3.2.5 Resetting a Configuration Parameter to Its Default Value

You can reset a configuration parameter to its default value using the unset command.

Syntax

agtctl  unset  parameter agent_sid

parameter is the parameter that you are modifying (or unsetting).
agent_sid is the SID that this agent services. It must be specified for single-line command mode.
Example

agtctl  unset  max_dispatchers salesDB

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

The delete command deletes the entry for the specified SID from the control file.

Syntax

agtctl  delete  agent_sid

agent_sid is the SID entry to delete.

Example

agtctl  delete  salesDB

5.3.2.7 Requesting Help

Use the help command to view a list of available commands for agtctl or to see the syntax for a particular command.

Syntax

agtctl help [command]

command is the name of the command whose syntax you want to view.

Example

agtctl help set

5.3.3 Using Shell Mode Commands

In shell mode, start agtctl by entering the following:

agtctl

This results in an AGTCTL> prompt. Thereafter, because you are issuing commands from within the agtctl shell, you do not need to prefix the command string with agtctl.

Set the name of the agent SID by entering the following:

AGTCTL> set  agent_sid  agent_sid

All subsequent commands are assumed to be for the specified SID until the agent_sid value is changed. Unlike single-line command mode, you do not specify agent_sid in the command string.

You can set the language for error messages as follows:

AGTCTL> set  language  language

The commands themselves are the same as those for the single-line command mode. To exit shell mode, enter exit.

The following are examples of shell mode commands.

5.3.3.1 Example: Setting a Configuration Parameter

This example sets a new value for the shutdown_address configuration parameter.

AGTCTL> set shutdown_address (address=(protocol=ipc)(key=oraDBsalesDB))

5.3.3.2 Example: Starting a Multithreaded Agent

This example starts a multithreaded agent.

AGTCTL> startup extproc

5.3.4 Configuration Parameters for Multithreaded Agent Control

Table 5-2 lists the configuration parameters for the agent control utility.

Table 5-2 Initialization Parameters for agtctl

Parameter

Description

Default Value

max_dispatchers

Maximum number of dispatchers

1

tcp_dispatchers

Number of dispatchers listening on tcp (the rest are using ipc)

0

max_task_threads

Number of task threads

2

max_sessions

Maximum number of sessions

5

listener_address

Address on which the listener is listening (needed for registration)

(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=IPC)
(KEY=PNPKEY))
(ADDRESS=
(PROTOCOL=IPC)
(KEY=listener_sid))
(ADDRESS=
(PROTOCOL=TCP)
(HOST=127.0.0.1)
(PORT=1521)))

Note: listener_sid is the IPC key to where the listener is listening of the Oracle database.

shutdown_address

Address the agent uses to communicate with the listener. This is the address on which the agent listens for all communication, including shutdown messages from agtctl

(ADDRESS=
(PROTOCOL=IPC)
(KEY=listener_sid || agent_sid))
(ADDRESS=
(PROTOCOL=TCP)
(HOST=127.0.0.1)
(PORT=1521))

Notes:

- agent_sid is the SID of the multithreaded agent.

- || indicates that listener_sid and agent_sid are concatenated into one string.

6 Performance Recommendations

This chapter suggests ways to optimize distributed SQL statements and improve the performance of distributed queries.

This chapter includes the following sections:

- Optimizing Heterogeneous Distributed SQL Statements

- Optimizing Performance of Distributed Queries

6.1 Optimizing Heterogeneous Distributed SQL Statements

When a SQL statement accesses data from non-Oracle systems, it is said to be a heterogeneous distributed SQL statement. To optimize heterogeneous distributed SQL statements, follow the same guidelines as for optimizing distributed SQL statements that access Oracle databases only. However, you must consider that the non-Oracle system usually does not support all the functions and operators that Oracle supports.

The Oracle Transparent Gateways tell Oracle (at connect time) which functions and operators they support. If the non-Oracle data source does not support a function or operator, then Oracle performs that function or operator. In this case, Oracle obtains the data from the other data source and applies the function or operator locally. This affects the way in which the SQL statements are decomposed and can affect performance, especially if Oracle is not on the same machine as the other data source.

6.2 Optimizing Performance of Distributed Queries

You can improve the performance of distributed queries by using the following strategies:

- Choose the best SQL statement.

In many cases, there are several SQL statements that can achieve the same result. If all tables are on the same database, then the difference in performance between these SQL statements may be minimal. If the tables are located on different databases, then the difference in performance may be more significant.

- Use the query optimizer.

The query optimizer uses indexes on remote tables, considers more execution plans than the rule-based optimizer, and generally gives better results. With the query optimizer, performance of distributed queries is generally satisfactory. Only on rare occasions is it necessary to change SQL statements, create views, or use procedural code.

- Use views.

In some situations, views can be used to improve performance of distributed queries. For example:

- Joining several remote tables on the remote database

- Sending a different table through the network

- Use procedural code.

On rare occasions, it can be more efficient to replace a distributed query by procedural code, such as a PL/SQL procedure or a precompiler program.

7 Generic Connectivity

This chapter describes the configuration and usage of Generic Connectivity agents.

This chapter contains these topics:

- What Is Generic Connectivity?

- Supported Oracle SQL Statements and Functions

- Configuring Generic Connectivity Agents

- ODBC Connectivity Requirements

- OLE DB (SQL) Connectivity Requirements

- OLE DB (FS) Connectivity Requirements

7.1 What Is Generic Connectivity?

Generic Connectivity is intended for low-end data integration solutions requiring the ad hoc query capability to connect from an Oracle database server to non-Oracle database systems.

Generic Connectivity is implemented as either a Heterogeneous Services ODBC agent or a Heterogeneous Services OLE DB agent. An ODBC agent and an OLE DB agent are included as part of your Oracle system. ODBC agents are supported on Solaris, HP-UX, AIX, Windows, and Linux (x86) 32-Bit platforms. OLE DB agents are supported only on the Windows platform.

Any data source compatible with the ODBC or OLE DB standards described in this chapter can be accessed using a Generic Connectivity agent.

This section contains the following topics:

- Types of Agents

- Generic Connectivity Architecture

- SQL Execution

- Data Type Mapping

- Generic Connectivity Restrictions

7.1.1 Types of Agents

Generic Connectivity is implemented as one of the following types of Heterogeneous Services agents:

- ODBC agent for accessing ODBC data providers

- OLE DB agent for accessing OLE DB data providers that support SQL processing—sometimes referred to as OLE DB (SQL)

- OLE DB agent for accessing OLE DB data providers without SQL processing support—sometimes referred to as OLE DB (FS)

Each user session receives its own dedicated agent process spawned by the first use in that user session of the database link to the non-Oracle system. The agent process ends when the user session ends.

7.1.2 Generic Connectivity Architecture

To access the non-Oracle data store using Generic Connectivity, the agents work with an ODBC or OLE DB driver. The Generic Connectivity agent provides support for the ODBC or OLE DB driver interface. The driver that you use must be on the same platform as the agent. The non-Oracle data stores can reside on the same machine as the Oracle database server or on a different machine.

7.1.2.1 Oracle and Non-Oracle Systems on Separate Machines

Figure 7-1 shows an example of a configuration in which an Oracle and non-Oracle database are on separate machines, communicating through a Heterogeneous Services ODBC agent.

Figure 7-1 Oracle and Non-Oracle Systems on a Separate Machines

In this configuration:

1. A client connects to the Oracle database server through Oracle Net.

2. The Heterogeneous Services component of the Oracle database server connects through Oracle Net to the Heterogeneous Services ODBC agent.

3. The agent communicates with the following non-Oracle components:

- An ODBC driver manager

- An ODBC driver

- A non-Oracle client application

This client connects to the non-Oracle data store through a network.

7.1.2.2 Oracle and Non-Oracle Systems on the Same Machine

Figure 7-2 shows an example of a configuration in which an Oracle and non-Oracle database are on the same machine, again communicating through a Heterogeneous Services ODBC agent.

Figure 7-2 Oracle and Non-Oracle Systems on the Same Machine

In this configuration:

1. A client connects to the Oracle database server through Oracle Net.

2. The Heterogeneous Services component of the Oracle database server connects through Oracle Net to the Heterogeneous Services ODBC agent.

3. The agent communicates with the following non-Oracle components:

- An ODBC driver manager

- An ODBC driver

The driver then allows access to the non-Oracle data store.

Note:

The ODBC driver may require non-Oracle client libraries even if the non-Oracle database is located on the same machine. Refer to your ODBC driver documentation for information about the requirements for the ODBC driver.

7.1.3 SQL Execution

SQL statements that are sent using a Generic Connectivity agent are executed differently depending on the type of agent you are using: ODBC, OLE DB (SQL), or OLE DB (FS). For example, if a SQL statement involving tables is sent using an ODBC agent for a file-based storage system, the file can be manipulated as if it were a table in a relational database. The naming conventions used at the non-Oracle system can also depend on whether you are using an ODBC or OLE DB agent.

7.1.4 Data Type Mapping

The Heterogeneous Services and Generic Connectivity agent maps the data types used in ODBC-compliant and OLE DB-compliant data sources to supported Oracle data types. When the results of a query are returned, the Oracle database server converts the ODBC or OLE DB data types to Oracle data types. For example, the ODBC data type SQL_TIMESTAMP and the OLE DB data type DBTYPE_DBTIMESTAMP are converted to Oracle's DATE data type.

If a table contains a column whose data type is not supported by Generic Connectivity, the column information is not returned to the Oracle server.

7.1.5 Generic Connectivity Restrictions

Generic Connectivity restrictions include:

- A table including a BLOB column must have a separate column that serves as a primary key.

- BLOB and CLOB data cannot be read by passthrough queries.

- Updates or deletes that include unsupported functions within a WHERE clause are not allowed.

- Generic Connectivity does not support stored procedures.

- Generic Connectivity agents cannot participate in distributed transactions; they support single-site transactions only.

- Generic Connectivity does not support multithreaded agents.

- Updating LONG columns with bind variables is not supported.

- Generic Connectivity does not support rowids.

7.2 Supported Oracle SQL Statements and Functions

Generic Connectivity supports the following statements, but only if the ODBC or OLE DB driver and non-Oracle system can execute them and if the statements contain supported Oracle SQL functions:

- DELETE

- INSERT

- SELECT

- UPDATE

Only a limited set of functions are assumed to be supported by the non-Oracle system. Most Oracle functions have no equivalent function in this limited set. Consequently, although post-processing is performed by the Oracle database server, many Oracle functions are not supported by Generic Connectivity, possibly impacting performance.

If an Oracle SQL function is not supported by Generic Connectivity, this function is not supported in DELETE, INSERT, or UPDATE statements. In SELECT statements, these functions are evaluated by the Oracle database server and processed after they are returned from the non-Oracle system.

If an unsupported function is used in a DELETE, INSERT, or UPDATE statement, it generates the following Oracle error:

ORA-02070: database db_link_name does not support function in this context

Generic Connectivity assumes that the following minimum set of SQL functions is supported by the ODBC driver or OLE DB provider that is being used:

- AVG(exp)

- LIKE(exp)

- COUNT(*)

- MAX(exp)

- MIN(exp)

- NOT

7.3 Configuring Generic Connectivity Agents

To implement Generic Connectivity on a non-Oracle data source, you must set the agent parameters.

This section contains the following topics:

- 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

7.3.1 Creating the Initialization File

You must create and customize an initialization file for your Generic Connectivity agent. Oracle supplies sample initialization files, inithsodbc.ora for ODBC agents and inithsoledb.ora for OLE DB agents. The sample files are stored in the $ORACLE_HOME/hs/admin directory.

To create an initialization file for an ODBC or OLE DB agent, copy the applicable sample initialization file and rename the file to initsid.ora, where sid is the system identifier you want to use for the instance of the non-Oracle system to which the agent connects.

The sid is also used to identify how to connect to the agent when you configure the listener by modifying the listener.ora file. The sid that you add to the listener.ora file must match the sid in an initsid.ora file, because the agent spawned by the listener searches for a matching initsid.ora file. When you copy and rename the initsid.ora file, ensure that it remains in the $ORACLE_HOME/hs/admin directory.

7.3.2 Editing the Initialization File

Customize the initsid.ora file by setting the parameter values used for Generic Connectivity agents to values appropriate for your system, agent, and drivers. You must edit the initsid.ora file to change the HS_FDS_CONNECT_INFO initialization parameter. The HS_FDS_CONNECT_INFO initialization parameter specifies the information required for connecting to the non-Oracle system.

See Also:

Section 4.2, "Setting Initialization Parameters" for more information about parameters

Set the parameter values as follows:

[SET][PRIVATE] parameter=value

SET and PRIVATE are optional keywords. If you do not specify either SET or PRIVATE, the parameter is used only as an initialization parameter for the agent.

SET specifies that, in addition to being used as an initialization parameter, the parameter value is set as an environment variable for the agent process.

PRIVATE specifies that the parameter value is private and not transferred to the Oracle database server. It does not appear in dynamic performance views or in graphical user interfaces.

SET PRIVATE specifies that the parameter value is set as an environment variable for the agent process and is also private (not transferred to the Oracle database server, not appearing in dynamic performance views or graphical user interfaces).

parameter is the Heterogeneous Services initialization parameter that you are specifying. See Section 4.2, "Setting Initialization Parameters" for a description of all Heterogeneous Services parameters. The parameter is case-sensitive.

value is the value you want to specify for the Heterogeneous Services parameter. The value is case-sensitive.

For example, to enable tracing for an agent, set the HS_FDS_TRACE_LEVEL initialization parameter as follows:

HS_FDS_TRACE_LEVEL=ON

Most parameters are needed only as initialization parameters, so you usually do not need to use the SET or PRIVATE keywords. Use SET for parameter values that the drivers or non-Oracle system need as environment variables.

PRIVATE is supported only for the following Heterogeneous Services initialization parameters:

- HS_FDS_CONNECT_INFO

- HS_FDS_SHAREABLE_NAME

- HS_FDS_TRACE_LEVEL

Use PRIVATE for these initialization parameters only if the parameter value includes sensitive information such as a username or password.

7.3.3 Setting Initialization Parameters for an ODBC-Based Data Source

The settings for the initialization parameters vary depending on the type of operating system.

7.3.3.1 Setting Agent Parameters on Windows

Specify a file data source name (DSN) or a system DSN which has previously been defined using the ODBC Driver Manager.

Specify the value as follows:

HS_FDS_CONNECT_INFO=dsn

7.3.3.1.1 Example: Setting Parameters on Windows

Assume that a system DSN has been defined in the Windows ODBC Data Source Administrator. In order to connect to this SQL Server database through the gateway, the following line is required in the initsid.ora file:

HS_FDS_CONNECT_INFO=sqlserver7

sqlserver7 is the name of the system DSN defined in the Windows ODBC Data Source Administrator.

The following procedure enables you to define a system DSN in the Windows ODBC Data Source Administrator, version 3.5:

1. From the Start menu, choose Settings > Control Panel and select ODBC .

2. Select the system DSN tab page to display the system data sources.

3. Click Add.

4. From the list of installed ODBC drivers, select the name of the driver that the data source will use. For example, select SQL Server.

5. Click Finish.

6. Enter a name for the DSN and an optional description. Enter other information depending on the ODBC driver. For example, for SQL Server enter the SQL Server machine name.

Note:

The name entered for the DSN must match the value of the initialization parameter HS_FDS_CONNECT_INFO that is specified in initsid.ora.

7. Refer to your ODBC driver documentation and follow the prompts to complete configuration of the DSN.

8. After creating the system DSN, click OK to exit the ODBC Data Source Administrator.

7.3.3.2 Setting Agent Parameters on UNIX Platforms

Specify a DSN and the path of the ODBC shareable library, as follows:

HS_FDS_CONNECT_INFO=dsn_value
HS_FDS_SHAREABLE_NAME=full_path_of_odbc_driver

The HS_FDS_SHAREABLE_NAME initialization parameter should point to the location of the ODBC driver manager and not to the ODBC driver.

The HS_FDS_CONNECT_INFO initialization parameter is required for all platforms for an ODBC agent. The HS_FDS_SHAREABLE_NAME initialization parameter is required on UNIX platforms for an ODBC agent. Other initialization parameters have defaults or are optional. You can use the default values and omit the optional parameters, or you can specify the parameters with values tailored for your installation.

Note:

Before deciding whether to accept the default values or to change them, see Appendix A for detailed information about all the initialization parameters.

7.3.3.2.1 Example: Setting Parameters on UNIX

The following is an example of an odbc.ini file that uses DataDirect Technologies SQLServer ODBC driver:

[ODBC Data Sources]
SQLServerWP=DataDirect 4.10 SQL Server Wire Protocol

[SQLServerWP]
Driver=/opt/odbc410/lib/ivmsss18.so
Description=DataDirect 4.10 SQL Server Wire Protocol
Database=oratst
LogonID=TKHOUSER
Password=TKHOUSER
Address=sqlserver-pc,1433
QuotedId=No
AnsiNPW=No

[ODBC]
Trace=0
TraceFile=/opt/odbc410/odbctrace.out
TraceDll=/opt/odbc410/lib/odbctrac.so
InstallDir=/opt/odbc410
ConversionTableLocation=/opt/odbc410/tables
UseCursorLib=0

To configure the Generic Connectivity ODBC agent to use this driver, the following lines are required in initsid.ora:

HS_FDS_CONNECT_INFO=SQLServerWP
HS_FDS_SHAREABLE_NAME=/opt/odbc4/lib/libodbc.so
set ODBCINI=/opt/odbc/odbc.ini

Note that the set statements are optional as long as they are specified in the environment where the listener was started. Each database has its own set statements.

The HS_FDS_CONNECT_INFO initialization parameter value must match the ODBC data source name in the odbc.ini file.

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

You can set these parameters only on the Windows platform.

Specify a data link (UDL) that has previously been defined:

SET|PRIVATE|SET PRIVATE HS_FDS_CONNECT_INFO="UDLFILE=data_link"

Note:

If the parameter value includes an equal sign (=), then it must be surrounded by quotation marks.

The HS_FDS_CONNECT_INFO initialization parameter is required for an OLE DB agent. Other initialization parameters have defaults or are optional. You can use the default values and omit the optional parameters, or you can specify the parameters with values that are customized for your installation.

Note:

Before deciding whether to accept the default values or to change them, see Appendix A for detailed information about all the initialization parameters.

7.4 ODBC Connectivity Requirements

To use an ODBC agent, you must have an ODBC driver installed on the same machine as the Oracle database server. On UNIX, you must have an ODBC driver manager available on the same machine. The ODBC driver manager and driver must meet the following requirements:

- The ODBC driver (and the non-Oracle system) must support a minimum transaction isolation level of read committed.

- On Windows machines, the 32-bit ODBC driver must have compliance level to ODBC standard 2.5.

- On Windows machines, the ODBC driver and driver manager must conform to ODBC application program interface (API) conformance Level 1 or higher. If the ODBC driver or driver manager does not support multiple active ODBC cursors, the complexity of SQL statements that you can execute using Generic Connectivity is restricted.

- On UNIX machines, the ODBC driver must be 32-bit and must have compliance level to ODBC Standard 2.5 and have a conformance level 1 or higher. If the ODBC driver works with an ODBC driver manager, the ODBC driver manager must be compliant with ODBC Standard 2.5 or higher.

See Also:

Your ODBC driver documentation for dependencies on an ODBC driver manager, and Oracle Database Conceptsfor more information on transaction isolation levels.

The ODBC driver you use must support all of the core SQL ODBC data types and must support SQL grammar level SQL_92. The ODBC driver should also expose the following ODBC APIs shown in Table 7-1:

Table 7-1 ODBC Functions

ODBC Function

Comment

SQLAllocConnect

-

SQLAllocEnv

-

SQLAllocStmt

-

SQLBindCol

-

SQLBindParameter

-

SQLColumns

-

SQLConnect

-

SQLDescribeCol

-

SQLDisconnect

-

SQLDriverConnect

-

SQLError

-

SQLExecDirect

-

SQLExecute

-

SQLExtendedFetch

Recommended if used by the non-Oracle system.

SQLFetch

-

SQLForeignKeys

Recommended if used by the non-Oracle system.

SQLFreeConnect

-

SQLFreeEnv

-

SQLFreeStmt

-

SQLGetConnectOption

-

SQLGetData

-

SQLGetFunctions

-

SQLGetInfo

-

SQLGetTypeInfo

-

SQLNumParams

Recommended if used by the non-Oracle system.

SQLNumResultCols

-

SQLParamData

-

SQLPrepare

-

SQLPrimaryKeys

Recommended if used by the non-Oracle system.

SQLProcedureColumns

Recommended if used by the non-Oracle system.

SQLProcedures

Recommended if used by the non-Oracle system.

SQLPutData

-

SQLRowCount

-

SQLSetConnectOption

-

SQLSetStmtOption

-

SQLStatistics

-

SQLTables

-

SQLTransact

Recommended if used by the non-Oracle system.

7.5 OLE DB (SQL) Connectivity Requirements

These requirements apply to OLE DB data providers that have a SQL processing capability and expose the OLE DB interfaces.

Generic Connectivity passes the username and password to the provider when calling IDBInitialize::Initialize().

OLE DB (SQL) connectivity requires that the data provider expose the following OLE DB interfaces shown in Table 7-2:

Table 7-2 OLE DB (SQL) Interfaces

Interface

Methods

Notes

IAccessor

CreateAccessor, ReleaseAccessor

-

IColumnsInfo

GetColumnsInfo (Command and Rowset objects)

-

ICommand

Execute

-

ICommandPrepare

Prepare

-

ICommandProperties

SetProperties

-

ICommandText

SetCommandText

-

ICommandWithParameters

GetParameterInfo

-

IDBCreateCommand

CreateCommand

-

IDBCreateSession

CreateSession

-

IDBInitialize

Initialize

-

IDBSchemaRowset

GetRowset (tables, columns, indexes; optionally also procedures, procedure parameters)

-

IErrorInfo

GetDescription, GetSource

You can also use IErrorLookup with the GetErrorDescription method.

IErrorRecords

GetErrorInfo

-

ILockBytes (OLE)

Flush, ReadAt, SetSize, Stat, WriteAt

Required only if BLOB data types are used in the OLE DB provider.

IRowset

GetData, GetNextRows, ReleaseRows, RestartPosition

-

IStream (OLE)

Read, Seek, SetSize, Stat, Write

-

ISupportErrorInfo

InterfaceSupportsErrorInfo

-

ITransactionLocal (optional)

StartTransaction, Commit, Abort

-

7.6 OLE DB (FS) Connectivity Requirements

These requirements apply to OLE DB data providers that do not have SQL processing capabilities. If the provider exposes them, OLE DB (FS) connectivity also uses OLE DB Index interfaces.

7.6.1 OLE DB Interfaces for Data Providers to Expose

OLE DB (FS) connectivity requires that the data provider expose the following OLE DB interfaces shown in Table 7-3:

Table 7-3 OLE DB (FS) Interfaces

Interface

Methods

Notes

IAccessor

CreateAccessor, ReleaseAccessor

-

IColumnsInfo

GetColumnsInfo (Command and Rowset objects)

-

IOpenRowset

OpenRowset

-

IDBCreateSession

CreateSession

-

IRowsetChange

DeleteRows, SetData, InsertRow

-

IRowsetLocate

GetRowsByBookmark

-

IRowsetUpdate

Update (optional)

-

IDBInitialize

Initialize, Uninitialize

-

IDBSchemaRowset

GetRowset (tables, columns, indexes; optionally also procedures, procedure parameters)

-

ILockBytes (OLE)

Flush, ReadAt, SetSize, Stat, WriteAt

Required only if BLOB data types are used in the OLE DB provider

IRowsetIndex

SetRange

Required only if indexes are used in the OLE DB provider

IErrorInfo

GetDescription, GetSource

You can use IErrorLookup with the GetErrorDescription method as well.

IErrorRecords

GetErrorInfo

-

IRowset

GetData, GetNextRows, ReleaseRows, RestartPosition

-

IStream (OLE)

Read, Seek, SetSize, Stat, Write

-

ITransactionLocal (optional)

StartTransaction, Commit, Abort

-

ISupportErrorInfo

InterfaceSupportsErrorInfo

-

ITableDefinition

CreateTable, DropTable

-

IDBProperties

SetProperties

-

Because OLE DB (FS) connectivity is generic, it can connect to a number of different data providers that expose OLE DB interfaces. Every such data provider must meet the certain requirements.

Note:

The data provider must expose bookmarks. This enables tables to be updated. Without bookmarks being exposed, the tables are read-only.

7.6.2 Data Source Properties

The OLE DB data source must support the following initialization properties:

- DBPROP_INIT_DATASOURCE

- DBPROP_AUTH_USERID

Note:

Required if the user ID has been supplied in the security file

- DBPROP_AUTH_PASSWORD

Note:

Required if the user ID and password have been supplied in the security file

The OLE DB data source must also support the following rowset properties:

- DBPROP_IRowsetChange = TRUE

- DBPROP_UPDATABILITY = CHANGE+DELETE+INSERT

- DBPROP_OWNUPDATEDELETE = TRUE

- DBPROP_OWNINSERT = TRUE

- DBPROP_OTHERUPDATEDELETE = TRUE

- DBPROP_CANSCROLLBACKWARDS = TRUE

- DBPROP_IRowsetLocate = TRUE

- DBPROP_OTHERINSERT = FALSE

A Heterogeneous Services Initialization Parameters

The Heterogeneous Services initialization parameter file contains configuration settings stored as a text file.

This section contains the following topics:

- HS_CALL_NAME

- HS_COMMIT_POINT_STRENGTH

- HS_DB_DOMAIN

- HS_DB_INTERNAL_NAME

- HS_DB_NAME

- HS_DESCRIBE_CACHE_HWM

- HS_FDS_CONNECT_INFO

- HS_FDS_DEFAULT_SCHEMA_NAME

- HS_FDS_SHAREABLE_NAME

- HS_FDS_TRACE_LEVEL

- HS_LANGUAGE

- HS_LONG_PIECE_TRANSFER_SIZE

- HS_NLS_DATE_FORMAT

- HS_NLS_DATE_LANGUAGE

- HS_NLS_NCHAR

- HS_NLS_NUMERIC_CHARACTER

- 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

See Also:

- Section 4.2, "Setting Initialization Parameters" contains instructions for setting the Heterogeneous Services initialization parameters

- Section 7.3, "Configuring Generic Connectivity Agents" contains instructions for setting Heterogeneous Services initialization parameters specific to Generic Connectivity

A.1 HS_CALL_NAME

Property

Description

Default value

None

Range of values

Not applicable

Specifies the remote functions that can be referenced in SQL statements. The value is a list of remote functions and their owners, separated by semicolons, in the following format:

owner_name.function_name

For example:

owner1.A1;owner2.A2;owner3.A3

If an owner name is not specified for a remote function, the default owner name becomes the user name used to connect to the remote database (specified when the Heterogeneous Services database link is created or taken from user session if not specified in the DB link).

The entries for the owner names and the function names are case-sensitive.

See Also:

Section 4.6, "Executing User-Defined Functions on a Non-Oracle Database"

A.2 HS_COMMIT_POINT_STRENGTH

Property

Description

Default value

0

Range of values

0 to 255

Specifies a value that determines the commit point site in a heterogeneous distributed transaction. HS_COMMIT_POINT_STRENGTH is similar to COMMIT_POINT_STRENGTH, described in the Oracle Database Reference.

Set the HS_COMMIT_POINT_STRENGTH initialization parameter to a value relative to the importance of the site that is the commit point site in a distributed transaction. The Oracle database server or non-Oracle system with the highest commit point strength becomes the commit point site. To ensure that a non-Oracle system never becomes the commit point site, set the value of the HS_COMMIT_POINT_STRENGTH initialization parameter to zero.

The HS_COMMIT_POINT_STRENGTH initialization parameter is important only if the non-Oracle system can participate in the two-phase protocol as a regular two-phase commit partner and as the commit point site. This is only the case if the transaction model is two-phase commit confirm (2PCC).

A.3 HS_DB_DOMAIN

Property

Description

Default value

WORLD

Range of values

1 to 199 characters

Specifies a unique network sub-address for a non-Oracle system. The HS_DB_DOMAIN initialization parameter is similar to the DB_DOMAIN initialization parameter, described in the Oracle Database Reference. The HS_DB_DOMAIN initialization parameter is required if you use the Oracle Names server. The HS_DB_NAME and HS_DB_DOMAIN initialization parameters define the global name of the non-Oracle system.

Note:

The HS_DB_NAME and HS_DB_DOMAIN initialization parameters must combine to form a unique address in a cooperative server environment.

A.4 HS_DB_INTERNAL_NAME

Property

Description

Default value

01010101

Range of values

1 to 16 hexadecimal characters

Specifies a unique hexadecimal number identifying the instance to which the Heterogeneous Services agent is connected. This parameter's value is used as part of a transaction ID when global name services are activated. Specifying a nonunique number can cause problems when two-phase commit recovery actions are necessary for a transaction.

A.5 HS_DB_NAME

Property

Description

Default value

HO

Range of values

1 to 8 characters

Specifies a unique alphanumeric name for the data store given to the non-Oracle system. This name identifies the non-Oracle system within the cooperative server environment. The HS_DB_NAME and HS_DB_DOMAIN initialization parameters define the global name of the non-Oracle system.

A.6 HS_DESCRIBE_CACHE_HWM

Property

Description

Default value

100

Range of values

1 to 4000

Specifies the maximum number of entries in the describe cache used by Heterogeneous Services. This limit is known as the describe cache high water mark. The cache contains descriptions of the mapped tables that Heterogeneous Services reuses so that it does not have to re-access the non-Oracle data store.

If you are accessing many mapped tables, increase the high water mark to improve performance. Increasing the high water mark improves performance at the cost of memory usage.

A.7 HS_FDS_CONNECT_INFO

Property

Description

Default value

None

Range of values

Not applicable

Specifies the information needed to bind to the data provider, that is, the non-Oracle system. For Generic Connectivity, you can bind to an ODBC-based data source or to an OLE DB-based data source. The information that you provide depends on the platform and whether the data source is ODBC, OLE DB-based, or a transparent gateway.

This parameter is required if you are using Generic Connectivity.

A.7.1 ODBC-Based Data Source on Windows

You can specify a file DSN (data source name) or a system DSN as follows:

HS_FDS_CONNECT_INFO=FILEDSN=dsn

A.7.2 ODBC-Based Data Source on UNIX

Use a DSN with the following format:

HS_FDS_CONNECT_INFO=dsn

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

Use a universal data link (UDL) with the following format:

HS_FDS_CONNECT_INFO="UDLFILE=data_link"

Note:

Whenever the parameter value includes an equal sign (=), it must be enclosed in quotation marks.

A.8 HS_FDS_DEFAULT_SCHEMA_NAME

Property

Description

Default value

None

Range of values

Not applicable

Specifies a default value for the owner column that will be returned in the data dictionary translation, when the value of the owner is null. For example:

HS_FDS_DEFAULT_SCHEMA_NAME = PUBLIC

A.9 HS_FDS_SHAREABLE_NAME

Property

Description

Default value

None

Range of values

Not applicable

Specifies the full path name to the ODBC library. This parameter is required when you are using Generic Connectivity to access data from an ODBC provider on a UNIX machine. The HS_FDS_SHAREABLE_NAME initialization parameter should point to the location of the ODBC driver manager and not to the ODBC driver.

A.10 HS_FDS_TRACE_LEVEL

Property

Description

Default value

OFF

Range of values

ON or OFF

Specifies whether error tracing is enabled or disabled for Generic Connectivity and transparent gateways. Enable the tracing to see which error messages occur when you encounter problems.

For Generic Connectivity, the results are written to a Generic Connectivity log file, in the /log directory under the $ORACLE_HOME/hs directory.

For transparent gateways, the location is the $ORACLE_HOME/<gateway>/log directory.

A.11 HS_LANGUAGE

Property

Description

Default value

System-specific

Range of values

Any valid language name (up to 255 characters)

Provides Heterogeneous Services with character set, language, and territory information of the non-Oracle data source. The value must use the following format:

language[_territory.character_set]

Note:

The globalization support initialization parameters affect error messages, the data for the SQL Service, and parameters in distributed external procedures.

A.11.1 Character Sets

Ideally, the character sets of the Oracle database server and the non-Oracle data source are the same. If they are not the same, Heterogeneous Services attempts to translate the character set of the non-Oracle data source to the Oracle database character set, and back again. The translation can degrade performance. In some cases, Heterogeneous Services cannot translate a character from one character set to another.

Note:

The specified character set must be a superset of the operating system character set on the platform where the agent is installed.

A.11.2 Language

The language component of the HS_LANGUAGE initialization parameter determines:

- Day and month names of dates

- AD, BC, PM, and AM symbols for date and time

- Default sorting mechanism

Note that Oracle does not determine the language for error messages for the generic Heterogeneous Services messages (ORA-25000 through ORA-28000). These are controlled by the session settings in the Oracle database server.

Note:

Use the HS_NLS_DATE_LANGUAGE initialization parameter to set the day and month names, and the AD, BC, PM, and AM symbols for dates and time independently from the language.

A.11.3 Territory

The territory clause specifies the conventions for day and week numbering, default date format, decimal character and group separator, and ISO and local currency symbols. Note that:

- You can override the date format using the initialization parameter HS_NLS_DATE_FORMAT.

- The level of globalization support between the Oracle database server and the non-Oracle data source depends on how the driver is implemented.

A.12 HS_LONG_PIECE_TRANSFER_SIZE

Property

Description

Default value

64 KB

Range of values

Any value up to 2 GB

Sets the size of the piece of LONG data being transferred. A smaller piece size means less memory requirement, but more round-trips to fetch all the data. A larger piece size means fewer round-trips, but more of a memory requirement to store the intermediate pieces internally. Thus, the initialization parameter can be used to tune a system for the best performance, with the best trade-off between round-trips and memory requirements, and network latency.

A.13 HS_NLS_DATE_FORMAT

Property

Description

Default value

Value determined by the HS_LANGUAGE initialization parameter

Range of values

Any valid date format mask (up to 255 characters)

Defines the date format for dates used by the target system. This initialization parameter has the same function as the NLS_DATE_FORMAT initialization parameter for an Oracle database server. The value can be any valid date mask listed in the Oracle Database SQL Reference, but must match the date format of the target system. For example, if the target system stores the date February 14, 2001 as 2001/02/14, set the parameter to yyyy/mm/dd. Note that characters must be lowercase.

A.14 HS_NLS_DATE_LANGUAGE

Property

Description

Default value

Value determined by the HS_LANGUAGE initialization parameter

Range of values

Any valid NLS_LANGUAGE value (up to 255 characters)

Specifies the language used in character date values coming from the non-Oracle system. Date formats can be language independent. For example, if the format is dd/mm/yyyy, all three components of the character date are numeric. In the format dd-mon-yyyy, however, the month component is the name abbreviated to three characters. The abbreviation is language dependent. For example, the abbreviation for the month April is "apr", which in French is "avr" (Avril).

Heterogeneous Services assumes that character date values fetched from the non-Oracle system are in this format. Also, Heterogeneous Services sends character date bind values in this format to the non-Oracle system.

A.15 HS_NLS_NCHAR

Property

Description

Default value

Value determined by the HS_LANGUAGE initialization parameter

Range of values

Any valid national character set (up to 255 characters)

Informs Heterogeneous Services of the value of the national character set of the non-Oracle data source. This value is the non-Oracle equivalent to the NATIONAL CHARACTER SET parameter setting in the Oracle CREATE DATABASE statement. The HS_NLS_NCHAR value should be the character set ID of a character set supported by the Oracle NLSRTL library.

See Also:

Section A.11, "HS_LANGUAGE"

A.16 HS_NLS_NUMERIC_CHARACTER

Property

Description

Default value

Value determined by the HS_LANGUAGE initialization parameter

Range of values

Any valid NLS_NUMERIC_CHARACTERS value (any two valid numeric characters)

Specifies the characters to use as the group separator and the decimal character. The group separator separates integer groups (such as thousands, millions, and billions). The decimal character separates the integer portion of a number from the decimal portion.

A.17 HS_NLS_TIMESTAMP_FORMAT

Property

Description

Default value

Derived from the HS_LANGUAGE initialization parameter

Range of values

Any valid datetime format mask

Defines the timestamp format for dates used by the target system. This initialization parameter has the same function as the NLS_TIMESTAMP_FORMAT initialization parameter for an Oracle database server. The value can be any valid timestamp mask listed in the Oracle Database SQL Reference, but it must match the date format of the target system. Note that characters must be lowercase. For example:

HS_NLS_TIMESTAMP_FORMAT = yyyy-mm-dd hh:mi:ss.ff

A.18 HS_NLS_TIMESTAMP_TZ_FORMAT

Property

Description

Default value

Derived from HS_LANGUAGE

Range of values

Any valid datetime with time zone format mask

Defines the default timestamp with time zone format for the timestamp with time zone format used by the target system. This parameter has the same function as the NLS_TIMESTAMP_TZ_FORMAT parameter for an Oracle database server. The value of can be any valid timestamp with time zone mask listed in the Oracle Database SQL Reference, but must match the date format of the target system. Note that characters must be lowercase. For example:

HS_NLS_TIMESTAMP_TZ_FORMAT = yyyy-mm-dd hh:mi:ss.ff tzh:tzm

A.19 HS_OPEN_CURSORS

Property

Description

Default value

50

Range of values

1 to the value of Oracle's OPEN_CURSORS initialization parameter

Defines the maximum number of cursors that can be open on one connection to a non-Oracle system instance.

The value never exceeds the number of open cursors in the Oracle database server. Therefore, setting the same value as the OPEN_CURSORS initialization parameter in the Oracle database server is recommended.

A.20 HS_ROWID_CACHE_SIZE

Property

Description

Default value

3

Range of values

1 to 32767

Specifies the size of the Heterogeneous Services cache containing the non-Oracle system equivalent of ROWID. The cache contains the non-Oracle system ROWID needed to support the WHERE CURRENT OF clause in a SQL statement or a SELECT FOR UPDATE statement.

When the cache is full, the first slot in the cache is reused, then the second, and so on. Only the last HS_ROWID_CACHE_SIZE non-Oracle system ROWID is cached.

A.21 HS_RPC_FETCH_REBLOCKING

Property

Description

Default value

ON

Range of values

OFF or ON

Controls whether Heterogeneous Services attempts to optimize performance of data transfer between the Oracle database server and the Heterogeneous Services agent connected to the non-Oracle data store.

The following values are possible:

- OFF disables reblocking of fetched data so that data is immediately sent from agent to server.

- ON enables reblocking, which means that data fetched from the non-Oracle system is buffered in the agent and is not sent to the Oracle database server until the amount of fetched data is equal or higher than the value of HS_RPC_FETCH_SIZE initialization parameter. However, any buffered data is returned immediately when a fetch indicates that no more data exists or when the non-Oracle system reports an error.

A.22 HS_RPC_FETCH_SIZE

Property

Description

Default value

4000

Range of values

Decimal integer (byte count)

Tunes internal data buffering to optimize the data transfer rate between the server and the agent process.

Increasing the value can reduce the number of network round-trips needed to transfer a given amount of data, but also tends to increase data bandwidth and to reduce response time or latency as measured between issuing a query and completion of all fetches for the query. Nevertheless, increasing the fetch size can increase latency for the initial fetch results of a query, because the first fetch results are not transmitted until additional data is available.

A.23 HS_TIME_ZONE

Property

Description

Default value for '[+|-]hh:mm'

Derived from the NLS_TERRITORY initialization parameter

Range of values for '[+|-]hh:mm'

Any valid datetime format mask

Specifies the default local time zone displacement for the current SQL session. The format mask, [+|-]hh:mm, is specified to indicate the hours and minutes before or after UTC (Coordinated Universal Time—formerly Greenwich Mean Time). For example:

HS_TIME_ZONE = [+ | -] hh:mm

A.24 IFILE

Property

Description

Default value

None

Range of values

Valid parameter file names

Use the IFILE initialization parameter to embed another initialization file within the current initialization file. The value should be an absolute path and should not contain environment variables. The three levels of nesting limit does not apply.

See Also:

Oracle Database Reference

B Data Type Mapping for Generic Connectivity

Oracle maps the ANSI data types through ODBC and OLE DB interfaces to supported Oracle data types. When the results of a query are returned, Oracle converts the ODBC or OLE DB data types to Oracle data types.

The tables in this appendix show how Oracle maps ANSI data types through ODBC and OLE DB interfaces to supported Oracle data types when it is retrieving data from a non-Oracle system.

This appendix contains the following tables:

- 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

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

Table B-1 maps ODBC data types into equivalent ANSI and Oracle data types. In some cases equivalence to ANSI data types is not guaranteed to be exact because the ANSI SQL standard delegates definition of numeric precision and maximum length of character data to individual implementations. This table reflects a probable mapping between ANSI and ODBC data types for a typical implementation of ANSI SQL.

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

ANSI

ODBC

Oracle

NUMERIC(19,0)

SQL_BIGINT

NUMBER(19,0)

N/A

SQL_BINARY

RAW

CHAR

SQL_CHAR

CHAR

DATE

SQL_DATE

DATE

DECIMAL(p,s)

SQL_DECIMAL(p,s)

NUMBER(p,s)

DOUBLE PRECISION

SQL_DOUBLE

FLOAT(49)

FLOAT

SQL_FLOAT

FLOAT(49)

INTEGER

SQL_INTEGER

NUMBER(10)
Note: It is possible under some circumstance for the INTEGER ANSI data type to map to Precision 38, but it usually maps to Precision 10.

N/A

SQL_LONGVARBINARY

LONG RAW

N/A

SQL_LONGVARCHAR

LONG
Note: If an ANSI SQL implementation defines a large value for the maximum length of VARCHAR data, it is possible that ANSI VARCHAR will map to SQL_LONGVARCHAR and Oracle LONG. The same is true for OLE DB DBTYPE_STRING (long attribute).

REAL

SQL_REAL

FLOAT(23)

SMALLINT

SQL_SMALLINT

NUMBER(5)

TIME

SQL_TIME

DATE

TIMESTAMP

SQL_TIMESTAMP

DATE

NUMERIC(3,0)

SQL_TINYINT

NUMBER(3)

VARCHAR

SQL_VARCHAR

VARCHAR

BOOLEAN

SQL_BIT

RAW(1)

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

Table B-2 shows the ANSI data types to Oracle data types mappings using an OLE DB interface.

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

ANSI

OLE DB

Oracle

NUMERIC(3,0)

DBTYPE_UI1

NUMBER(3)

NUMERIC(3,0)

DBTYPE_I1

NUMBER(3)

SMALLINT

DBTYPE_UI2

NUMBER(5)

SMALLINT

DBTYPE_I2

NUMBER(5)

NUMERIC(3,0)

DBTYPE_BOOL

NUMBER(5)

INTEGER

DBTYPE_UI4

NUMBER(10)

INTEGER

DBTYPE_I4

NUMBER(10)

NUMERIC(19,0)

DBTYPE_UI8

NUMBER(19,0)

NUMERIC(19,0)

DBTYPE_I8

NUMBER(19,0)

NUMERIC(p,s)

DBTYPE_NUMERIC(p,s)

NUMBER(p,s)

FLOAT

DBTYPE_R4

FLOAT(23)

DOUBLE PRECISION

DBTYPE_R8

FLOAT(49)

N/A

DBTYPE_DECIMAL

FLOAT(49)

VARCHAR

DBTYPE_STR

VARCHAR2

VARCHAR

DBTYPE_WSTR

VARCHAR2

NUMERIC(19,0)

DBTYPE_CY

NUMBER(19,0)

DATE

DBTYPE_DBDATE

DATE

TIME

DBTYPE_DBTIME

DATE

TIMESTAMP

DBTYPE_TIMESTAMP

DATE

N/A

DBTYPE_BYTES

RAW

N/A

DBTYPE_BYTES (long attribute)

LONG RAW

N/A

DBTYPE_STRING (long attribute)

LONG

C Data Dictionary Translation Support

Data dictionary information is stored in the non-Oracle system as system tables and is accessed through ODBC or OLE DB application programming interfaces (APIs). This appendix documents data dictionary translation support. It explains how to access non-Oracle data dictionaries, lists Heterogeneous Services data dictionary views, describes how to use supported views and tables, and explains data dictionary mapping.

This appendix contains the following topics:

- Accessing the Non-Oracle Data Dictionary

- Heterogeneous Services Data Dictionary Views

- Views and Tables Supported by Generic Connectivity

C.1 Accessing the Non-Oracle Data Dictionary

Accessing a non-Oracle data dictionary table or view is identical to accessing a data dictionary in an Oracle database. You issue a SELECT statement specifying a database link. The Oracle data dictionary view and column names are used to access the non-Oracle data dictionary. Synonyms of supported views are also acceptable.

For example, the following statement queries the data dictionary table ALL_USERS to retrieve all users in the non-Oracle system:

SQL SELECT * FROM all_users@sid1;

When you issue a data dictionary access query, the ODBC or OLE DB agent:

1. Maps the requested table, view, or synonym to one or more ODBC or OLE DB APIs (see Section C.3.1, "Data Dictionary Mapping"). The agent translates all data dictionary column names to their corresponding non-Oracle column names within the query.

2. Sends the sequence of APIs to the non-Oracle system.

3. Possibly converts the retrieved non-Oracle data to give it the appearance of the Oracle data dictionary table.

4. Passes the data dictionary information from the non-Oracle system table to Oracle.

Note:

The values returned when querying the Generic Connectivity data dictionary may not be the same as those returned by the Oracle SQL*Plus DESCRIBE command.

C.2 Heterogeneous Services Data Dictionary Views

Heterogeneous Services mapping supports the following list of data dictionary views:

ALL_CATALOG
ALL_COL_COMMENTS
ALL_COL_PRIVS
ALL_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD
ALL_CONSTRAINTS
ALL_CONS_COLUMNS
ALL_DB_LINKS
ALL_DEF_AUDIT_OPTS
ALL_DEPENDENCIES
ALL_ERRORS
ALL_INDEXES
ALL_IND_COLUMNS
ALL_OBJECTS
ALL_SEQUENCES
ALL_MVIEWS
ALL_SOURCE
ALL_SYNONYMS
ALL_TABLES
ALL_TAB_COLUMNS
ALL_TAB_COMMENTS
ALL_TAB_PRIVS
ALL_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD
ALL_TRIGGERS
ALL_USERS
ALL_VIEWS
AUDIT_ACTIONS
COLUMN_PRIVILEGES
DBA_CATALOG
DBA_COL_COMMENTS
DBA_COL_PRIVS
DBA_OBJECTS
DBA_ROLES
DBA_ROLE_PRIVS
DBA_SYS_PRIVS
DBA_TABLES
DBA_TAB_COLUMNS
DBA_TAB_COMMENTS
DBA_TAB_PRIVS
DBA_USERS
DICTIONARY
DICT_COLUMNS
DUAL
INDEX_STATS
PRODUCT_USER_PROFILE
RESOURCE_COST
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
SESSION_PRIVS
SESSION_ROLES
TABLE_PRIVILEGES
USER_AUDIT_OBJECT
USER_AUDIT_SESSION
USER_AUDIT_STATEMENT
USER_AUDIT_TRAIL
USER_CATALOG
USER_CLUSTERS
USER_CLU_COLUMNS
USER_COL_COMMENTS
USER_COL_PRIVS
USER_COL_PRIVS_MADE
USER_COL_PRIVS_RECD
USER_CONSTRAINTS
USER_CONS_COLUMNS
USER_DB_LINKS
USER_DEPENDENCIES
USER_ERRORS
USER_EXTENTS
USER_FREE_SPACE
USER_INDEXES
USER_IND_COLUMNS
USER_OBJECTS
USER_OBJ_AUDIT_OPTS
USER_RESOURCE_LIMITS
USER_ROLE_PRIVS
USER_SEGMENTS
USER_SEQUENCES
USER_MVIEW_LOGS
USER_SOURCE
USER_SYNONYMS
USER_SYS_PRIVS
USER_TABLES
USER_TABLESPACES
USER_TAB_COLUMNS
USER_TAB_COMMENTS
USER_TAB_PRIVS
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD
USER_TRIGGERS
USER_TS_QUOTAS
USER_USERS
USER_VIEWS

C.3 Views and Tables Supported by Generic Connectivity

Generic Connectivity supports only the views and tables shown in Table C-1.

If you use an unsupported view, you receive an Oracle error message stating no rows were selected.

If you want to query data dictionary views using SELECT... FROM DBA_*, first connect as Oracle user SYSTEM or SYS. Otherwise, you receive the following error message:

ORA-28506: Parse error in data dictionary translation for %s stored in %s

Using Generic Connectivity, queries of the supported data dictionary tables and views beginning with the characters ALL_ may return rows from the non-Oracle system when you do not have access privileges for those non-Oracle objects. When querying an Oracle database with the Oracle data dictionary, rows are returned only for those objects you are permitted to access.

C.3.1 Data Dictionary Mapping

The tables in this section list Oracle data dictionary view names and the equivalent ODBC or OLE DB APIs used. Table C-1 shows a list of all Oracle data dictionary view names supported by Generic Connectivity.

Table C-1 Generic Connectivity Data Dictionary Mapping

View

ODBC API

OLE DB API

ALL_CATALOG

SQLTables

DBSCHEMA_CATALOGS

ALL_COL_COMMENTS

SQLColumns

DBSCHEMA_COLUMNS

ALL_CONS_COLUMNS

SQLPrimaryKeys, SQLForeignKeys

DBSCHEMA_PRIMARY_KEYS, DBSCHEMA_FOREIGN_KEYS

ALL_CONSTRAINTS

SQLPrimaryKeys, SQLForeignKeys

DBSCHEMA_PRIMARY_KEYS, DBSCHEMA_FOREIGN_KEYS

ALL_IND_COLUMNS

SQLStatistics

DBSCHEMA_STATISTICS

ALL_INDEXES

SQLStatistics

DBSCHEMA_STATISTICS

ALL_OBJECTS

SQLTables, SQLProcedures, SQLStatistics

DBSCHEMA_TABLES, DBSCHEMA_PROCEDURES, DBSCHEMA_STATISTICS

ALL_TAB_COLUMNS

SQLColumns

DBSCHEMA_COLUMNS

ALL_TAB_COMMENTS

SQLTables

DBSCHEMA_TABLES

ALL_TABLES

SQLStatistics

DBSCHEMA_STATISTICS

ALL_USERS

SQLTables

DBSCHEMA_TABLES

ALL_VIEWS

SQLTables

DBSCHEMA_TABLES

DICTIONARY

SQLTables

DBSCHEMA_TABLES

USER_CATALOG

SQLTables

DBSCHEMA_TABLES

USER_COL_COMMENTS

SQLColumns

DBSCHEMA_COLUMNS

USER_CONS_COLUMNS

SQLPrimaryKeys, SQLForeignKeys

DBSCHEMA_PRIMARY_KEYS, DBSCHEMA_FOREIGN_KEYS

USER_CONSTRAINTS

SQLPrimaryKeys, SQLForeignKeys

DBSCHEMA_PRIMARY_KEYS, DBSCHEMA_FOREIGN_KEYS

USER_IND_COLUMNS

SQLStatistics

DBSCHEMA_STATISTICS

USER_INDEXES

SQLStatistics

DBSCHEMA_STATISTICS

USER_OBJECTS

SQLTables, SQLProcedures, SQLStatistics

DBSCHEMA_TABLES, DBSCHEMA_PROCEDURES, DBSCHEMA_STATISTICS

USER_TAB_COLUMNS

SQLColumns

DBSCHEMA_COLUMNS

USER_TAB_COMMENTS

SQLTables

DBSCHEMA_TABLES

USER_TABLES

SQLStatistics

DBSCHEMA_STATISTICS

USER_USERS

SQLTables

DBSCHEMA_TABLES

USER_VIEWS

SQLTables

DBSCHEMA_TABLES

C.3.2 Generic Connectivity Data Dictionary Descriptions

The Generic Connectivity data dictionary tables and views provide the following information:

- Name, data type, and width of each column

- The contents of columns with fixed values

In the descriptions that follow, the values in the Null? column may differ from the Oracle data dictionary tables and views. Any default value is shown to the right of an item.

C.3.2.1 ALL_CATALOG

Name

Null?

Type

Value

OWNER

NOT NULL

VARCHAR2(30)

-

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

TABLE_TYPE

-

VARCHAR2(11)

"TABLE" or "VIEW" or SYNONYM

C.3.2.2 ALL_COL_COMMENTS

Name

Null?

Type

Value

OWNER

NOT NULL

VARCHAR2(30)

-

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

COLUMN_NAME

NOT NULL

VARCHAR2(30)

-

COMMENTS

-

VARCHAR2(4000)

NULL

C.3.2.3 ALL_CONS_COLUMNS

Name

Null?

Type

Value

OWNER

NOT NULL

VARCHAR2(30)

-

CONSTRAINT_NAME

NOT NULL

VARCHAR2(30)

-

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

COLUMN_NAME

-

VARCHAR2(4000)

-

POSITION

-

NUMBER

-

C.3.2.4 ALL_CONSTRAINTS

Name

Null?

Type

Value

OWNER

NOT NULL

VARCHAR2(30)

-

CONSTRAINT_NAME

NOT NULL

VARCHAR2(30)

-

CONSTRAINT_TYPE

-

VARCHAR2(1)

"R" or "P"

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

SEARCH_CONDITION

-

LONG

NULL

R_OWNER

-

VARCHAR2(30)

-

R_CONSTRAINT_NAME

-

VARCHAR2(30)

-

DELETE_RULE

-

VARCHAR2(9)

"CASCADE" or "NO ACTION" or "SET NULL"

STATUS

-

VARCHAR2(8)

NULL

DEFERRABLE

-

VARCHAR2(14)

NULL

DEFERRED

-

VARCHAR2(9)

NULL

VALIDATED

-

VARCHAR2(13)

NULL

GENERATED

-

VARCHAR2(14)

NULL

BAD

-

VARCHAR2(3)

NULL

RELY

-

VARCHAR2(4)

NULL

LAST_CHANGE

-

DATE

NULL

C.3.2.5 ALL_IND_COLUMNS

Name

Null?

Type

Value

INDEX_OWNER

NOT NULL

VARCHAR2(30)

-

INDEX_NAME

NOT NULL

VARCHAR2(30)

-

TABLE_OWNER

NOT NULL

VARCHAR2(30)

-

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

COLUMN_NAME

-

VARCHAR2(4000)

-

COLUMN_POSITION

NOT NULL

NUMBER

-

COLUMN_LENGTH

NOT NULL

NUMBER

-

DESCEND

-

VARCHAR2(4)

"DESC" or "ASC"

C.3.2.6 ALL_INDEXES

Name

Null?

Type

Value

OWNER

NOT NULL

VARCHAR2(30)

-

INDEX_NAME

NOT NULL

VARCHAR2(30)

-

INDEX_TYPE

-

VARCHAR2(27)

NULL

TABLE_OWNER

NOT NULL

VARCHAR2(30)

-

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

TABLE_TYPE

-

CHAR(5)

"TABLE"

UNIQUENESS

-

VARCHAR2(9)

"UNIQUE" or "NONUNIQUE"

COMPRESSION

-

VARCHAR2(8)

NULL

PREFIX_LENGTH

-

NUMBER

0

TABLESPACE_NAME

-

VARCHAR2(30)

NULL

INI_TRANS

-

NUMBER

0

MAX_TRANS

-

NUMBER

0

INITIAL_EXTENT

-

NUMBER

0

NEXT_EXTENT

-

NUMBER

0

MIN_EXTENTS

-

NUMBER

0

MAX_EXTENTS

-

NUMBER

0

PCT_INCREASE

-

NUMBER

0

PCT_THRESHOLD

-

NUMBER

0

INCLUDE_COLUMNS

-

NUMBER

0

FREELISTS

-

NUMBER

0

FREELIST_GROUPS

-

NUMBER

0

PCT_FREE

-

NUMBER

0

LOGGING

-

VARCHAR2(3)

NULL

BLEVEL

-

NUMBER

0

LEAF_BLOCKS

-

NUMBER

0

DISTINCT_KEYS

-

NUMBER

AVG_LEAF_BLOCKS_PER_KEY

-

NUMBER

0

AVG_DATA_BLOCKS_PER_KEY

-

NUMBER

0

CLUSTERING_FACTOR

-

NUMBER

0

STATUS

-

VARCHAR2(8)

NULL

NUM_ROWS

-

NUMBER

0

SAMPLE_SIZE

-

NUMBER

0

LAST_ANALYZED

-

DATE

NULL

DEGREE

-

VARCHAR2(40)

NULL

INSTANCES

-

VARCHAR2(40)

NULL

PARTITIONED

-

VARCHAR2(3)

NULL

TEMPORARY

-

VARCHAR2(1)

NULL

GENERATED

-

VARCHAR2(1)

NULL

SECONDARY

-

VARCHAR2(1)

NULL

BUFFER_POOL

-

VARCHAR2(7)

NULL

USER_STATS

-

VARCHAR2(3)

NULL

DURATION

-

VARCHAR2(15)

NULL

PCT_DIRECT_ACCESS

-

NUMBER

0

ITYP_OWNER

-

VARCHAR2(30)

NULL

ITYP_NAME

-

VARCHAR2(30)

NULL

PARAMETERS

-

VARCHAR2(1000)

NULL

GLOBAL_STATS

-

VARCHAR2(3)

NULL

DOMIDX_STATUS

-

VARCHAR2(12)

NULL

DOMIDX_OPSTATUS

-

VARCHAR2(6)

NULL

FUNCIDX_STATUS

-

VARCHAR2(8)

NULL

C.3.2.7 ALL_OBJECTS

Name

Null?

Type

Value

OWNER

NOT NULL

VARCHAR2(30)

-

OBJECT_NAME

NOT NULL

VARCHAR2(30)

-

SUBOBJECT_NAME

-

VARCHAR2(30)

NULL

OBJECT_ID

NOT NULL

NUMBER

0

DATA_OBJECT_ID

-

NUMBER

0

OBJECT_TYPE

-

VARCHAR2(18)

"TABLE" or "VIEW" or "SYNONYM" or "INDEX" or "PROCEDURE"

CREATED

NOT NULL

DATE

NULL

LAST_DDL_TIME

NOT NULL

DATE

NULL

TIMESTAMP

-

VARCHAR2(19)

NULL

STATUS

-

VARCHAR2(7)

NULL

TEMPORARY

-

VARCHAR2(1)

NULL

GENERATED

-

VARCHAR2(1)

NULL

SECONDARY

-

VARCHAR2(1)

NULL

C.3.2.8 ALL_TAB_COLUMNS

Name

Null?

Type

Value

OWNER

NOT NULL

VARCHAR2(30)

-

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

COLUMN_NAME

NOT NULL

VARCHAR2(30)

-

DATA_TYPE

-

VARCHAR2(106)

-

DATA_TYPE_MOD

-

VARCHAR2(3)

NULL

DATA_TYPE_OWNER

-

VARCHAR2(30)

NULL

DATA_LENGTH

NOT NULL

NUMBER

-

DATA_PRECISION

-

NUMBER

-

DATA_SCALE

-

NUMBER

-

NULLABLE

-

VARCHAR2(1)

"Y" or "N"

COLUMN_ID

NOT NULL

NUMBER

-

DEFAULT_LENGTH

-

NUMBER

0

DATA_DEFAULT

-

LONG

NULL

NUM_DISTINCT

-

NUMBER

0

LOW_VALUE

-

RAW(32)

NULL

HIGH_VALUE

-

RAW(32)

NULL

DENSITY

-

NUMBER

0

NUM_NULLS

-

NUMBER

0

NUM_BUCKETS

-

NUMBER

0

LAST_ANALYZED

-

DATE

NULL

SAMPLE_SIZE

-

NUMBER

0

CHARACTER_SET_NAME

-

VARCHAR2(44)

NULL

CHAR_COL_DEC_LENGTH

-

NUMBER

0

GLOBAL_STATS

-

VARCHAR2(3)

NULL

USER_STATS

-

VARCHAR2(3)

NULL

AVG_COL_LEN

-

NUMBER

0

C.3.2.9 ALL_TAB_COMMENTS

Name

Null?

Type

Value

OWNER

NOT NULL

VARCHAR2(30)

-

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

TABLE_TYPE

-

VARCHAR2(11)

"TABLE" or "VIEW"

COMMENTS

-

VARCHAR2(4000)

NULL

C.3.2.10 ALL_TABLES

Name

Null?

Type

Value

OWNER

NOT NULL

VARCHAR2(30)

-

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

TABLESPACE_NAME

-

VARCHAR2(30)

NULL

CLUSTER_NAME

-

VARCHAR2(30)

NULL

IOT_NAME

-

VARCHAR2(30)

NULL

PCT_FREE

-

NUMBER

0

PCT_USED

-

NUMBER

0

INI_TRANS

-

NUMBER

0

MAX_TRANS

-

NUMBER

0

INITIAL_EXTENT

-

NUMBER

0

NEXT_EXTENT

-

NUMBER

0

MIN_EXTENTS

-

NUMBER

0

MAX_EXTENTS

-

NUMBER

0

PCT_INCREASE

-

NUMBER

0

FREELISTS

-

NUMBER

0

FREELIST_GROUPS

-

NUMBER

0

LOGGING

-

VARCHAR2(3)

NULL

BACKED_UP

-

VARCHAR2(1)

NULL

NUM_ROWS

-

NUMBER

-

BLOCKS

-

NUMBER

-

EMPTY_BLOCKS

-

NUMBER

0

AVG_SPACE

-

NUMBER

0

CHAIN_CNT

-

NUMBER

0

AVG_ROW_LEN

-

NUMBER

0

AVG_SPACE_FREELIST_BLOCKS

-

NUMBER

0

NUM_FREELIST_BLOCKS

-

NUMBER

0

DEGREE

-

VARCHAR2(10)

NULL

INSTANCES

-

VARCHAR2(10)

NULL

CACHE

-

VARCHAR2(5)

NULL

TABLE_LOCK

-

VARCHAR2(8)

NULL

SAMPLE_SIZE

-

NUMBER

0

LAST_ANALYZED

-

DATE

NULL

PARTITIONED

-

VARCHAR2(3)

NULL

IOT_TYPE

-

VARCHAR2(12)

NULL

TEMPORARY

-

VARCHAR2(1)

NULL

SECONDARY

-

VARCHAR2(1)

NULL

NESTED

-

VARCHAR2(3)

NULL

BUFFER_POOL

-

VARCHAR2(7)

NULL

ROW_MOVEMENT

-

VARCHAR2(8)

NULL

GLOBAL_STATS

-

VARCHAR2(3)

NULL

USER_STATS

-

VARCHAR2(3)

NULL

DURATION

-

VARHCAR2(15)

NULL

SKIP_CORRUPT

-

VARCHAR2(8)

NULL

MONITORING

-

VARCHAR2(3)

NULL

C.3.2.11 ALL_USERS

Name

Null?

Type

Value

USERNAME

NOT NULL

VARCHAR2(30)

-

USER_ID

NOT NULL

NUMBER

0

CREATED

NOT NULL

DATE

NULL

C.3.2.12 ALL_VIEWS

Name

Null?

Type

Value

OWNER

NOT NULL

VARCHAR2(30)

-

VIEW_NAME

NOT NULL

VARCHAR2(30)

-

TEXT_LENGTH

-

NUMBER

0

TEXT

NOT NULL

LONG

NULL

TYPE_TEXT_LENGTH

-

NUMBER

0

TYPE_TEXT

-

VARCHAR2(4000)

NULL

OID_TEXT_LENGTH

-

NUMBER

0

OID_TEXT

-

VARCHAR2(4000)

NULL

VIEW_TYPE_OWNER

-

VARCHAR2(30)

NULL

VIEW_TYPE

-

VARCHAR2(30)

NULL

C.3.2.13 DICTIONARY

Name

Null?

Type

Value

TABLE_NAME

-

VARCHAR2(30)

-

COMMENTS

-

VARCHAR2(4000)

NULL

C.3.2.14 USER_CATALOG

Name

Null?

Type

Value

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

TABLE_TYPE

-

VARCHAR2(11)

"TABLE" or, "VIEW" or "SYNONYM"

C.3.2.15 USER_COL_COMMENTS

Name

Null?

Type

Value

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

COLUMN_NAME

NOT NULL

VARCHAR2(30)

-

COMMENTS

-

VARCHAR2(4000)

NULL

C.3.2.16 USER_CONS_COLUMNS

Name

Null?

Type

Value

OWNER

NOT NULL

VARCHAR2(30)

-

CONSTRAINT_NAME

NOT NULL

VARCHAR2(30)

-

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

COLUMN_NAME

-

VARCHAR2(4000)

-

POSITION

-

NUMBER

-

C.3.2.17 USER_CONSTRAINTS

Name

Null?

Type

Value

OWNER

NOT NULL

VARCHAR2(30)

-

CONSTRAINT_NAME

NOT NULL

VARCHAR2(30)

-

CONSTRAINT_TYPE

-

VARCHAR2(1)

R or P

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

SEARCH_CONDITION

-

LONG

NULL

R_OWNER

-

VARCHAR2(30)

-

R_CONSTRAINT_NAME

-

VARCHAR2(30)

-

DELETE_RULE

-

VARCHAR2(9)

"CASCADE" or "NOACTION" or "SET NULL"

STATUS

-

VARCHAR2(8)

NULL

DEFERRABLE

-

VARCHAR2(14)

NULL

DEFERRED

-

VARCHAR2(9)

NULL

VALIDATED

-

VARCHAR2(13)

NULL

GENERATED

-

VARCHAR2(14)

NULL

BAD

-

VARCHAR2(3)

NULL

RELY

-

VARCHAR2(4)

NULL

LAST_CHANGE

-

DATE

NULL

C.3.2.18 USER_IND_COLUMNS

Name

Null?

Type

Value

INDEX_NAME

-

VARCHAR2(30)

-

TABLE_NAME

-

VARCHAR2(30)

-

COLUMN_NAME

-

VARCHAR2(4000)

-

COLUMN_POSITION

-

NUMBER

-

COLUMN_LENGTH

-

NUMBER

-

DESCEND

-

VARCHAR2(4)

"DESC" or "ASC"

C.3.2.19 USER_INDEXES

Name

Null?

Type

Value

INDEX_NAME

NOT NULL

VARCHAR2(30)

-

INDEX_TYPE

-

VARCHAR2(27)

NULL

TABLE_OWNER

NOT NULL

VARCHAR2(30)

-

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

TABLE_TYPE

-

VARCHAR2(11)

"TABLE"

UNIQUENESS

-

VARCHAR2(9)

"UNIQUE" or "NONUNIQUE"

COMPRESSION

-

VARCHAR2(8)

NULL

PREFIX_LENGTH

-

NUMBER

0

TABLESPACE_NAME

-

VARCHAR2(30)

NULL

INI_TRANS

-

NUMBER

0

MAX_TRANS

-

NUMBER

0

INITIAL_EXTENT

-

NUMBER

0

NEXT_EXTENT

-

NUMBER

0

MIN_EXTENTS

-

NUMBER

0

MAX_EXTENTS

-

NUMBER

0

PCT_INCREASE

-

NUMBER

0

PCT_THRESHOLD

-

NUMBER

0

INCLUDE_COLUMNS

-

NUMBER

0

FREELISTS

-

NUMBER

0

FREELIST_GROUPS

-

NUMBER

0

PCT_FREE

-

NUMBER

0

LOGGING

-

VARCHAR2(3)

NULL

BLEVEL

-

NUMBER

0

LEAF_BLOCKS

-

NUMBER

0

DISTINCT_KEYS

-

NUMBER

-

AVG_LEAF_BLOCKS_PER_KEY

-

NUMBER

0

AVG_DATA_BLOCKS_PER_KEY

-

NUMBER

0

CLUSTERING_FACTOR

-

NUMBER

0

STATUS

-

VARCHAR2(8)

NULL

NUM_ROWS

-

NUMBER

0

SAMPLE_SIZE

-

NUMBER

0

LAST_ANALYZED

-

DATE

NULL

DEGREE

-

VARCHAR2(40)

NULL

INSTANCES

-

VARCHAR2(40)

NULL

PARTITIONED

-

VARCHAR2(3)

NULL

TEMPORARY

-

VARCHAR2(1)

NULL

GENERATED

-

VARCHAR2(1)

NULL

SECONDARY

-

VARCHAR2(1)

NULL

BUFFER_POOL

-

VARCHAR2(7)

NULL

USER_STATS

-

VARCHAR2(3)

NULL

DURATION

-

VARHCAR2(15)

NULL

PCT_DIRECT_ACCESS

-

NUMBER

0

ITYP_OWNER

-

VARCHAR2(30)

NULL

ITYP_NAME

-

VARCHAR2(30)

NULL

PARAMETERS

-

VARCHAR2(1000)

NULL

GLOBAL_STATS

-

VARCHAR2(3)

NULL

DOMIDX_STATUS

-

VARCHAR2(12)

NULL

DOMIDX_OPSTATUS

-

VARCHAR2(6)

NULL

FUNCIDX_STATUS

-

VARCHAR2(8)

NULL

C.3.2.20 USER_OBJECTS

Name

Null?

Type

Value

OBJECT_NAME

-

VARCHAR2(128)

-

SUBOBJECT_NAME

-

VARCHAR2(30)

NULL

OBJECT_ID

-

NUMBER

0

DATA_OBJECT_ID

-

NUMBER

0

OBJECT_TYPE

-

VARCHAR2(18)

"TABLE" or "VIEW" or "SYNONYM" or "INDEX" or "PROCEDURE"

CREATED

-

DATE

NULL

LAST_DDL_TIME

-

DATE

NULL

TIMESTAMP

-

VARCHAR2(19)

NULL

STATUS

-

VARCHAR2(7)

NULL

TEMPORARY

-

VARCHAR2(1)

NULL

GENERATED

-

VARCHAR2(1)

NULL

SECONDARY

-

VARCHAR2(1)

NULL

C.3.2.21 USER_TAB_COLUMNS

Name

Null?

Type

Value

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

COLUMN_NAME

NOT NULL

VARCHAR2(30)

-

DATA_TYPE

-

VARCHAR2(106)

-

DATA_TYPE_MOD

-

VARCHAR2(3)

NULL

DATA_TYPE_OWNER

-

VARCHAR2(30)

NULL

DATA_LENGTH

NOT NULL

NUMBER

-

DATA_PRECISION

-

NUMBER

-

DATA_SCALE

-

NUMBER

-

NULLABLE

-

VARCHAR2(1)

"Y" or "N"

COLUMN_ID

NOT NULL

NUMBER

-

DEFAULT_LENGTH

-

NUMBER

NULL

DATA_DEFAULT

-

LONG

NULL

NUM_DISTINCT

-

NUMBER

NULL

LOW_VALUE

-

RAW(32)

NULL

HIGH_VALUE

-

RAW(32)

NULL

DENSITY

-

NUMBER

0

NUM_NULLS

-

NUMBER

0

NUM_BUCKETS

-

NUMBER

0

LAST_ANALYZED

-

DATE

NULL

SAMPLE_SIZE

-

NUMBER

0

CHARACTER_SET_NAME

-

VARCHAR2(44)

NULL

CHAR_COL_DECL_LENGTH

-

NUMBER

0

GLOBAL_STATS

-

VARCHAR2(3)

NULL

USER_STATS

-

VARCHAR2(3)

NULL

AVG_COL_LEN

-

NUMBER

0

C.3.2.22 USER_TAB_COMMENTS

Name

Null?

Type

Value

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

TABLE_TYPE

-

VARCHAR2(11)

"TABLE" or "VIEW"

COMMENTS

-

VARCHAR2(4000)

NULL

C.3.2.23 USER_TABLES

Name

Null?

Type

Value

TABLE_NAME

NOT NULL

VARCHAR2(30)

-

TABLESPACE_NAME

-

VARCHAR2(30)

NULL

CLUSTER_NAME

-

VARCHAR2(30)

NULL

IOT_NAME

-

VARCHAR2(30)

NULL

PCT_FREE

-

NUMBER

0

PCT_USED

-

NUMBER

0

INI_TRANS

-

NUMBER

0

MAX_TRANS

-

NUMBER

0

INITIAL_EXTENT

-

NUMBER

0

NEXT_EXTENT

-

NUMBER

0

MIN_EXTENTS

-

NUMBER

0

MAX_EXTENTS

-

NUMBER

0

PCT_INCREASE

-

NUMBER

0

FREELISTS

-

NUMBER

0

FREELIST_GROUPS

-

NUMBER

0

LOGGING

-

VARCHAR2(3)

NULL

BACKED_UP

-

VARCHAR2(1)

NULL

NUM_ROWS

-

NUMBER

-

BLOCKS

-

NUMBER

-

EMPTY_BLOCKS

-

NUMBER

0

AVG_SPACE

-

NUMBER

0

CHAIN_CNT

-

NUMBER

0

AVG_ROW_LEN

-

NUMBER

0

AVG_SPACE_FREELIST_BLOCKS

-

NUMBER

0

NUM_FREELIST_BLOCKS

-

NUMBER

0

DEGREE

-

VARCHAR2(10)

NULL

INSTANCES

-

VARCHAR2(10)

NULL

CACHE

-

VARCHAR2(5)

NULL

TABLE_LOCK

-

VARCHAR2(8)

NULL

SAMPLE_SIZE

-

NUMBER

0

LAST_ANALYZED

-

DATE

NULL

PARTITIONED

-

VARCHAR2(3)

NULL

IOT_TYPE

-

VARCHAR2(12)

NULL

TEMPORARY

-

VARHCAR2(1)

NULL

SECONDARY

-

VARCHAR2(1)

NULL

NESTED

-

VARCHAR2(3)

NULL

BUFFER_POOL

-

VARCHAR2(7)

NULL

ROW_MOVEMENT

-

VARCHAR2(8)

NULL

GLOBAL_STATS

-

VARCHAR2(3)

NULL

USER_STATS

-

VARCHAR2(3)

NULL

DURATION

-

VARCHAR2(15)

NULL

SKIP_CORRUPT

-

VARCHAR2(8)

NULL

MONITORING

-

VARCHAR2(3)

NULL

C.3.2.24 USER_USERS

Name

Null?

Type

Value

USERNAME

NOT NULL

VARCHAR2(30)

-

USER_ID

NOT NULL

NUMBER

0

ACCOUNT_STATUS

NOT NULL

VARCHAR2(32)

OPEN

LOCK_DATE

-

DATE

NULL

EXPIRY_DATE

-

DATE

NULL

DEFAULT_TABLESPACE

NOT NULL

VARCHAR2(30)

NULL

TEMPORARY_TABLESPACE

NOT NULL

VARCHAR2(30)

NULL

CREATED

NOT NULL

DATE

NULL

INITIAL_RSRC_CONSUMER_GROUP

-

VARCHAR2(30)

NULL

EXTERNAL_NAME

-

VARCHAR2(4000)

NULL

C.3.2.25 USER_VIEWS

Name

Null?

Type

Value

VIEW_NAME

NOT NULL

VARCHAR2(30)

-

TEXT_LENGTH

-

NUMBER

0

TEXT

-

LONG

NULL

TYPE_TEXT_LENGTH

-

NUMBER

0

TYPE_TEXT

-

VARCHAR2(4000)

NULL

OID_TEXT_LENGTH

-

NUMBER

0

OID_TEXT

-

VARCHAR2(4000)

NULL

VIEW_TYPE_OWNER

-

VARCHAR2(30)

NULL

VIEW_TYPE

-

VARCHAR2(30)

NULL

Index
A  B  C  D  E  F  G  H  I  L  M  O  P  Q  R  S  T  U  V

A

agent control utility. See agtctl.

agents

Generic Connectivity, 2.3.2

Heterogeneous Services

architecture, 2.1

disabling self-registration, 4.4.2

registering, 4.4, 4.4.1, 4.4.1.1, 4.4.1.2

types of agents, 2.3

multithreaded Heterogeneous Services, 5 specifying initialization parameters for, 4.1.2 Transparent Gateways, 2.3.1 agtctl, 5.2, 5.3

commands, 5.3.1

shell mode commands, 5.3.3

single-line command mode, 5.3.2

application development

Heterogeneous Services

controlling array fetches between non-Oracle server and agent, 4.3.3

controlling array fetches between Oracle server and agent, 4.3.2

controlling reblocking of array fetches, 4.3.4

DBMS_HS_PASSTHROUGH package, 3.3

pass-through SQL, 3.3

using bulk fetches, 4.3

using OCI for bulk fetches, 4.3.1

array fetches, 4.3.1

agents, 4.3.3

B

bind queries

executing using pass-through SQL, 3.3.3.2

BIND_INOUT_VARIABLE procedure, 3.3.3, 3.3.3.1.4 BIND_OUT_VARIABLE procedure, 3.3.3, 3.3.3.1.3 BIND_VARIABLE procedure, 3.3.3 buffers

multiple rows, 3.3.3.2

bulk fetches

optimizing data transfers using, 4.3

C

callback link, 3.10.2

character sets

Heterogeneous Services, A.11.1

CLOSE_CURSOR function, 3.3.3 commit point site

commit point strength, A.2

configuring

Generic Connectivity, 7.3

Transparent Gateways, 4.1

copying data

from Oracle database server to SQL Server, 4.8

from SQL Server to Oracle database server, 4.9

INSERT statement, 4.9

D

data dictionary

contents with Generic Connectivity, C.3

mapping for Generic Connectivity, C.3.1

Oracle server name/SQL Server name, C.3.1

translation support for Generic Connectivity, C

data dictionary views

Generic Connectivity, C.3

Heterogeneous Services, 4.10, C.2

data type checking support

for remote-mapped statements, 4.5.1

database links

heterogeneous systems, 4.1.2

date formats

Heterogeneous Services, A.13, A.17

DBMS_HS_PASSTHROUGH package, 3.3.1

list of functions and procedures, 3.3.3

describe cache high water mark

definition, A.6

dispatcher threads

multithreaded Heterogeneous Services agents, 5.2, 5.2.2

distributed queries

optimizing performance, 6.2

drivers

ODBC, 7.4

OLE DB (FS), 7.6

OLE DB (SQL), 7.5

dynamic performance views

Heterogeneous Services, 4.11

determining open sessions, 4.11.2

determining which agents are on host, 4.11.1

E

EXECUTE_IMMEDIATE procedure, 3.3.3

restrictions, 3.3.3.1

EXECUTE_NON_QUERY procedure, 3.3.3

F

FDS_CLASS, 4.4.1.2.1

FDS_CLASS_VERSION, 4.4.1.2.1

FDS_INST_NAME, 4.4.1.2.2

features

new, Preface

FETCH_ROW procedure, 3.3.3

executing queries using pass-through SQL, 3.3.3.2

fetches

bulk, 4.3

optimizing round-trips, 3.3.3.2

G

gateways

how they work, 2.7

Generic Connectivity

architecture, 7.1.2

Oracle and non-Oracle on same machine, 7.1.2.2

Oracle and non-Oracle on separate machines, 7.1.2.1

configuration, 7.3 creating initialization file, 7.3.1 data dictionary

translation support, C

defined, 1.2 definition, 7.1 DELETE statement, 7.2 editing initialization file, 7.3.2 Heterogeneous Services, 2.3.2 INSERT statement, 7.2 non-Oracle data dictionary access, C.1 ODBC connectivity requirements, 7.4 OLE DB (FS) connectivity requirements, 7.6 OLE DB (SQL) connectivity requirements, 7.5 restrictions, 7.1.5 setting parameters for ODBC source, 7.3.3

UNIX, 7.3.3.2

Windows NT, 7.3.3.1

setting parameters for OLE DB source, 7.3.4 SQL execution, 7.1.3 supported functions, 7.2 supported SQL syntax, 7.2 types of agents, 7.1.1 UPDATE statement, 7.2 generic connectivity

error tracing, A.10

GET_VALUE procedure, 3.3.3, 3.3.3.1.3, 3.3.3.1.4 globalization support

Heterogeneous Services, A.11

character set of non-Oracle source, A.15

date format, A.13

languages in character date values, A.14

H

heterogeneous distributed systems

accessing, 4.1

Heterogeneous Services

agent control utility (agtctl), 5.3.1

agent registration, 4.4

avoiding configuration mismatches, 4.4.1.1

disabling, 4.4.2

enabling, 4.4.1

self-registration, 4.4.1.2

application development

controlling array fetches between non-Oracle server and agent, 4.3.3

controlling array fetches between Oracle server and agent, 4.3.2

controlling reblocking of array fetches, 4.3.4

DBMS_HS_PASSTHROUGH package, 3.3

pass-through SQL, 3.3

using bulk fetches, 4.3

using OCI for bulk fetches, 4.3.1

creating database links, 4.1.2 data dictionary views, 4.10, C.2

types, 4.10.1

understanding sources, 4.10.2

using general views, 4.10.3

using SQL service views, 4.10.5

using transaction service views, 4.10.4

defining maximum number of open cursors, A.19 dynamic performance views, 4.11

V$HS_AGENT view, 4.11.1

V$HS_SESSION view, 4.11.2

Generic Connectivity

architecture, 7.1.2

creating initialization file, 7.3.1

definition, 7.1

editing initialization file, 7.3.2

non-Oracle data dictionary access, C.1

ODBC connectivity requirements, 7.4

OLE DB (FS) connectivity requirements, 7.6

OLE DB (SQL) connectivity requirements, 7.5

restrictions, 7.1.5

setting parameters for ODBC source, 7.3.3

setting parameters for OLE DB source, 7.3.4

SQL execution, 7.1.3

supported functions, 7.2

supported SQL syntax, 7.2

supported tables, C.3

types of agents, 7.1.1

initialization parameters, 2.5.2, 4.2, 7.3.1, A multithreaded agents, 5 optimizing data transfer, A.21 setting global name, A.5 setting up access using Transparent Gateway, 4.1 setting up environment, 4.1.1 specifying cache high water mark, A.6 specifying cache size, A.20 specifying commit point strength, A.2 SQL service, 2.4.2 testing connections, 4.1.3 transaction service, 2.4.1 tuning internal data buffering, A.22 tuning LONG data transfer, A.12 HS_AUTOREGISTER initialization parameter

using to enable agent self-registration, 4.4.1.3

HS_BASE_CAPS view, 4.10.1 HS_BASE_DD view, 4.10.1 HS_CALL_NAME initialization parameter, A.1 HS_CLASS_CAPS view, 4.10.1 HS_CLASS_DD view, 4.10.1 HS_CLASS_INIT view, 4.10.1 HS_COMMIT_POINT_STRENGTH initialization parameter, A.2 HS_DB_NAME initialization parameter, A.5 HS_DESCRIBE_CACHE_HWM initialization parameter, A.6 HS_FDS_CLASS view, 4.10.1 HS_FDS_CONNECT_INFO initialization parameter, A.7

specifying connection information, 7.3.2

HS_FDS_DEFAULT_SCHEMA_NAME initialization parameter, A.8 HS_FDS_FETCH_ROWS initialization parameter, 4.3.3 HS_FDS_INST view, 4.10.1 HS_FDS_SHAREABLE_NAME initialization parameter, A.9 HS_FDS_TRACE_LEVEL initialization parameter, A.10

enabling agent tracing, 7.3.2

HS_INST_CAPS view, 4.10.1 HS_INST_DD view, 4.10.1 HS_INST_INIT view, 4.10.1 HS_LANGUAGE initialization parameter, A.11 HS_LONG_PIECE_TRANSFER_SIZE initialization parameter, A.12 HS_NLS_DATE_FORMAT initialization parameter, A.13 HS_NLS_DATE_LANGUAGE initialization parameter, A.14 HS_NLS_NCHAR initialization parameter, A.15 HS_NLS_NUMERIC_CHARACTER initialization parameter, A.16 HS_NLS_TIMESTAMP_FORMAT initialization parameter, A.17 HS_NLS_TIMESTAMP_TZ_FORMAT initialization parameter, A.18 HS_OPEN_CURSORS initialization parameter, A.19 HS_ROWID_CACHE_SIZE initialization parameter, A.20 HS_RPC_FETCH_REBLOCKING initialization parameter, 4.3.4, A.21 HS_RPC_FETCH_SIZE initialization parameter, 4.3.2, 4.3.4, A.22 HS_TIME_ZONE initialization parameter, A.23

I

IFILE initialization parameter, A.24

information integration

benefits of Oracle solutions, 1.3

challenges, 1.1

Generic Connectivity, 1.2

how Oracle addresses, 1.2

Messaging Gateway, 1.2

Open System Interfaces, 1.2

Oracle Streams, 1.2

Oracle Transparent Gateways, 1.2

initialization parameters

Heterogeneous Services (HS), 2.5.2, 4.2, 7.3.1, A

initialization parameters (HS)

common to all gateways, 4.2

descriptions, A

Generic Connectivity, 7.3.1

purpose, 2.5.2

L

listeners, 4.1.1

M

Messaging Gateway

defined, 1.2

monitor thread

multithreaded Heterogeneous Services agents, 5.2, 5.2.1

multiple rows

buffering, 3.3.3.2

multithreaded Heterogeneous Services agents

administering, 5.3

advantages, 5.1.2

agent control utility (agtctl), 5.3.1

architecture, 5.2

configuration parameters, 5.3.4

dispatcher threads, 5.2, 5.2.2

monitor thread, 5.2, 5.2.1

task threads, 5.2, 5.2.3

O

OCI

optimizing data transfers using, 4.3.1

ODBC agents

connectivity requirements, 7.4

functions, 7.4

ODBC connectivity

data dictionary mapping, C.3.1

ODBC driver, 7.4

requirements, 7.4

specifying connection information

UNIX, A.7.2

Windows NT, A.7.1

specifying path to library, A.9 OLE DB (FS) drivers, 7.6 OLE DB (SQL) drivers, 7.5 OLE DB agents

connectivity requirements, 7.5, 7.6

OLE DB connectivity

data dictionary mapping, C.3.1

setting connection information, A.7.3

OLE DB drivers

data provider requirements, 7.6.1

initialization properties, 7.6.2

rowset properties, 7.6.2

Open System Interfaces

defined, 1.2

OPEN_CURSOR procedure, 3.3.3 Oracle database server

SQL construct processing, 4.5

Oracle Net Services listener, 2.2, 4.1.1 Oracle precompiler

optimizing data transfers using, 4.3.1

Oracle Streams

defined, 1.2

Oracle Transparent Gateways

defined, 1.2

optimizing SQL statements, 6.1

OUT bind variables, 3.3.3.1.3

P

PARSE procedure, 3.3.3

pass-through SQL

avoiding SQL interpretation, 3.3

executing statements, 3.3.3

non-queries, 3.3.3.1

queries, 3.3.3.2

with bind variables, 3.3.3.1.1

with IN bind variables, 3.3.3.1.2

with IN OUT bind variables, 3.3.3.1.4

with OUT bind variables, 3.3.3.1.3

implications of using, 3.3.2 overview, 3.3 restrictions, 3.3.2

Q

queries

pass-through SQL, 3.3.3.2

R

reblocking, 4.3.4

remote functions

referenced in SQL statements, A.1

remote-mapped statements

and data type checking support, 4.5.1

rows

buffering multiple, 3.3.3.2

S

SELECT statement

accessing non-Oracle system, C.1

service names

specifying in database links, 4.1.2

SQL capabilities

data dictionary tables, 4.10.5.1

SQL service

data dictionary views, 2.6.2, 4.10.1

Heterogeneous Services, 2.4.2

views

Heterogeneous Services, 4.10.5

SQL statements

optimizing distributed, 6.1

Streams

using for heterogeneous connectivity, 3.2

Synonyms, 4.7

T

task threads

multithreaded Heterogeneous Services agents, 5.2, 5.2.3

transaction service

Heterogeneous Services, 2.4.1

views

Heterogeneous Services, 4.10.4

transparent gateways

accessing Heterogeneous Services agents, 4.1.1

creating database links, 4.1.2

testing connections, 4.1.3

U

unsupported functions

Generic Connectivity, 7.2

user-defined functions

executing on non-Oracle database, 4.6

V

variables

bind, 3.3.3.1.1

Oracle Legal Notices

Copyright Notice

Copyright © 1994-2016, Oracle and/or its affiliates. All rights reserved.

License Restrictions Warranty/Consequential Damages Disclaimer

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

Warranty Disclaimer

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

Restricted Rights Notice

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

Hazardous Applications Notice

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

Third-Party Content, Products, and Services Disclaimer

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.

Alpha and Beta Draft Documentation Notice

If this document is in preproduction status:

This documentation is in preproduction status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.

Private Alpha and Beta Draft Documentation Notice

If this document is in private preproduction status:

The information contained in this document is for informational sharing purposes only and should be considered in your capacity as a customer advisory board member or pursuant to your beta trial agreement only. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described in this document remains at the sole discretion of Oracle.

This document in any form, software or printed matter, contains proprietary information that is the exclusive property of Oracle. Your access to and use of this confidential material is subject to the terms and conditions of your Oracle Master Agreement, Oracle License and Services Agreement, Oracle PartnerNetwork Agreement, Oracle distribution agreement, or other license agreement which has been executed by you and Oracle and with which you agree to comply. This document and information contained herein may not be disclosed, copied, reproduced, or distributed to anyone outside Oracle without prior written consent of Oracle. This document is not part of your license agreement nor can it be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.