Oracle 9i. Heterogeneous Connectivity Administrator’s Guide

Burroughs T.

Oracle 9i. Heterogeneous Connectivity Administrator’s Guide is intended for the following users:■ Database administrators who want to administer distributed database systems that involve the following:■ Oracle to Oracle database links■ Oracle to non-Oracle database links■ Regular Oracle database server users who want to make use of the Heterogeneous Services feature in the Oracle database server■ Readers who want an high-level understanding of this product and how it works. To use this document, you should be familiar with the following information:■ Relational database concepts and basic database administration as described in Oracle 9i. Dorobase Concepts and the OracleQi Database Administrator’s Guide.■ The operating system environment under which database administrators are running Oracle.

---

Table of contents :
Send Us Your Comments……Page 13
Preface……Page 15
1 Introduction……Page 23
The Heterogeneous Services Module in the Oracle Database Server……Page 24
Integrating Heterogeneous Services Into the Oracle Server……Page 26
Elimination of Unnecessary Data Duplication……Page 27
Query Optimization……Page 28
Pass-Through Feature……Page 29
2 Oracle Transparent Gateways and Generic Connectivity……Page 31
Heterogeneous Services Agents……Page 32
Oracle Transparent Gateways……Page 33
Transaction Service……Page 34
Configuring Heterogeneous Services……Page 35
The Heterogeneous Services Data Dictionary……Page 36
Classes and Instances……Page 37
Gateway Process Flow……Page 38
Oracle Transparent Gateways for Non-Oracle Database Systems……Page 40
3 Major Features……Page 41
SQL and PL/SQL Support……Page 42
Heterogeneous Replication……Page 43
Using the DBMS_HS_PASSTHROUGH package……Page 45
Executing Pass-Through SQL Statements……Page 46
Using Bind Variables: Overview……Page 47
Using IN Bind Variables……Page 49
Using IN OUT Bind Variables……Page 50
Executing Queries……Page 51
Result Set Support In Non-Oracle Systems:……Page 54
Heterogeneous Services Support for Result Sets……Page 55
Sequential Mode……Page 56
Code Examples:……Page 57
OCI program fetching from result sets in cursor mode……Page 58
OCI program fetching from result sets in sequential mode……Page 59
PL/SQL program fetching from result sets in cursor mode……Page 61
Data Dictionary Translations……Page 64
Example 2: Check current session’s user ID on Oracle and on Informix…….Page 65
Example 3: Check constraints defined on a non-Oracle system for tables owned by an arbitrary user…….Page 66
Date Time……Page 67
Piecewise Long……Page 68
Resolving Remote and Heterogeneous References……Page 69
Rule B: Insert, Update and Delete statements with a remote target table must be remote mapped…….Page 70
Rule D: SQL statements containing operators and constructs that are not supported at the remote s………Page 72
Rule G: The statement must be mapped to the node on which the table or tables with columns refere………Page 73
Updates, Inserts and Deletes……Page 74
Using Index and Table Statistics……Page 75
Other Optimizations……Page 76
Remote Join Optimization……Page 77
Optimizer Restrictions for non-Oracle Access……Page 78
4 Using the Gateway……Page 79
Step 2: Set Up the Environment to Access Heterogeneous Services Agents……Page 80
A Sample Listener Entry……Page 81
Step 4: Test the Connection……Page 82
Initialization Parameters……Page 84
Optimizing Data Transfers Using Bulk Fetch……Page 86
Controlling the Reblocking of Array Fetches……Page 87
Enabling Agent Self-Registration……Page 89
Using Agent Self-Registration to Avoid Configuration Mismatches……Page 90
FDS_CLASS and FDS_CLASS_VERSION……Page 91
Specifying HS_AUTOREGISTER……Page 92
Disabling Agent Self-Registration……Page 93
Oracle Database Server SQL Construct Processing……Page 94
Example of a Distributed Query……Page 95
Copying Data from the Oracle Database Server to the Non-Oracle Database System……Page 97
Copying Data from the Non-Oracle Database System to the Oracle Database Server……Page 99
Understanding the Types of Views……Page 100
Understanding the Sources of Data Dictionary Information……Page 101
Using the General Views……Page 102
Using the Transaction Service Views……Page 103
Using Views for Data Dictionary Translations……Page 104
Determining the Open Heterogeneous Services Sessions……Page 106
Determining the Heterogeneous Services Parameters……Page 107
5 Using Multithreaded Agents……Page 109
The Advantage of Multithreading……Page 110
Overview……Page 112
The Monitor Thread……Page 114
Task Threads……Page 115
Single Command Mode Commands……Page 116
Shell Mode Commands……Page 117
6 Performance Tips……Page 119
Using Gateways and Partition Views……Page 120
Use views…….Page 121
7 Generic Connectivity……Page 123
Types of Agents……Page 124
Oracle and Non-Oracle Systems on Separate Machines……Page 125
Oracle and Non-Oracle Systems on the Same Machine……Page 126
Generic Connectivity Restrictions……Page 128
Functions Supported by Generic Connectivity……Page 129
Editing the Initialization File……Page 130
Setting Parameters on NT: Example……Page 132
Setting Parameters on UNIX: Example……Page 133
Setting Initialization Parameters for an OLE DB-based Data Source……Page 134
ODBC Connectivity Requirements……Page 135
OLE DB (SQL) Connectivity Requirements……Page 137
OLE DB (FS) Connectivity Requirements……Page 138
Data Source Properties……Page 140
A Heterogeneous Services Initialization Parameters……Page 141
HS_DB_INTERNAL_NAME……Page 143
HS_FDS_CONNECT_INFO……Page 144
OLE DB-based Data Source (Windows NT Only):……Page 145
HS_LANGUAGE……Page 146
Territory……Page 147
HS_NLS_DATE_LANGUAGE……Page 148
HS_NLS_TIMESTAMP_FORMAT……Page 149
HS_ROWID_CACHE_SIZE……Page 150
HS_RPC_FETCH_SIZE……Page 151
IFILE……Page 152
B Data Type Mapping……Page 153
Mapping ANSI Data Types to Oracle Data Types Through an ODBC Interface……Page 154
Mapping ANSI Data Types to Oracle Data Types Through an OLE DB Interface……Page 156
C DBMS_HS_PASSTHROUGH for Pass-Through SQL……Page 157
Summary of Subprograms……Page 159
Syntax…….Page 160
Pragmas……Page 161
Pragmas……Page 162
Exceptions……Page 163
Syntax……Page 164
Exceptions……Page 165
Parameters……Page 166
Parameters……Page 167
BIND_INOUT_VARIABLE procedure……Page 168
Parameters……Page 169
Syntax……Page 170
Pragmas……Page 171
Parameters……Page 172
Syntax……Page 173
Parameter Description……Page 174
Syntax……Page 175
FETCH_ROW function……Page 176
Pragmas……Page 177
Syntax……Page 178
Pragmas……Page 179
Parameters……Page 180
Syntax……Page 181
Pragmas……Page 182
Pragmas……Page 183
Pragmas……Page 184
Accessing the Non-Oracle Data Dictionary……Page 187
Heterogeneous Services Data Dictionary Views……Page 188
Supported Views and Tables……Page 191
Data Dictionary Mapping……Page 193
ALL_CATALOG……Page 194
ALL_CONSTRAINTS……Page 195
ALL_INDEXES……Page 196
ALL_OBJECTS……Page 198
ALL_TAB_COLUMNS……Page 199
ALL_TABLES……Page 200
ALL_VIEWS……Page 202
USER_CONS_COLUMNS……Page 203
USER_IND_COLUMNS……Page 204
USER_INDEXES……Page 205
USER_TAB_COLUMNS……Page 207
USER_TAB_COMMENTS……Page 208
USER_TABLES……Page 209
USER_USERS……Page 210
USER_VIEWS……Page 211
Index……Page 213