Oracle Heterogeneous Services Release 8.1.7 Part Number A88714-01 |
|
This chapter describes the configuration and usage of generic connectivity agents.
This chapter contains these topics:
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 enabled by Oracle Heterogeneous Services, allowing you to connect to non-Oracle systems with improved performance and throughput.
Generic connectivity is implemented as either a Heterogeneous Services ODBC agent or a Heterogeneous Services OLE DB agent. An ODBC agent and OLE DB agent are included as part of your Oracle system. Be sure to use the agents shipped with your particular Oracle system, installed in the same $ORACLE_HOME
.
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:
Generic connectivity is implemented as one of the following types of Heterogeneous Services agents:
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.
To access the non-Oracle data store using generic connectivity, the agents work with an ODBC or OLE DB driver. The Oracle database server 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 a different machine.
Figure 3-1 shows an example of a configuration in which an Oracle and non-Oracle database are on separate machines, communicating through an Heterogeneous Services ODBC agent.
In this configuration:
This client connects to the non-Oracle data store through a network.
Figure 3-2 shows an example of a different configuration in which an Oracle and non-Oracle database are on the same machine, again communicating through an Heterogeneous Services ODBC agent.
In this configuration:
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. |
SQL statements 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.
The Oracle database server maps the datatypes used in ODBC and OLE DB compliant data sources to supported Oracle datatypes. When the results of a query are returned, the Oracle database server converts the ODBC or OLE DB datatypes to Oracle datatypes. For example, the ODBC datatype SQL_TIMESTAMP and the OLE DB datatype DBTYPE_DBTIMESTAMP are converted to Oracle's DATE datatype.
Generic connectivity restrictions include:
Generic connectivity supports the following statements, but only if the ODBC or OLE DB driver and non-Oracle system can execute them and the statements contain supported Oracle SQL functions:
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, many Oracle functions are not supported by generic connectivity, although post-processing is performed by the Oracle database server, possibly impacting performance.
If an Oracle SQL function is not supported by generic connectivity, then this function is not supported in DELETE, INSERT, or UPDATE statements. In SELECT statements, these functions are evaluated by the Oracle database server and post-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 this 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:
To implement generic connectivity on a non-Oracle data source, you must set the agent parameters.
This section contains the following topics:
You must create and customize an initialization file for your generic connectivity agent. Oracle Corporation supplies sample initialization files named init
agent.ora
, where agent is odbc
or oledb
, indicating which agent the sample file can be used for, as in the following:
initodbc.ora initoledb.ora
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 init
HS_SID.ora
, where HS_SID is the system identifier you want to use for the instance of the non-Oracle system to which the agent connects.
The HS_SID is also used to identify how to connect to the agent when you configure the listener by modifying the listener.ora
file. The HS_SID you add to the listener.ora
file must match the HS_SID in an init
HS_SID.ora
file, because the agent spawned by the listener searches for a matching init
HS_SID.ora
file. That is how each agent process gets its initialization information. When you copy and rename your init
HS_SID.ora
file, ensure it remains in the $ORACLE_HOME/hs/admin
directory.
See Also:
"Step 2: Set Up the Environment to Access Heterogeneous Services Agents" for more information on configuring the listener. |
Customize the init
HS_SID.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 init
HS_SID.ora
file to change the HS_FDS_CONNECT_INFO initialization parameter. HS_FDS_CONNECT_INFO specifies the information required for connecting to the non-Oracle system.
See Also:
Appendix A, "Heterogeneous Services Initialization Parameters" for more information on parameters. |
Set the parameter values as follows:
[SET][PRIVATE] parameter=value
where:
|
are optional keywords. If you do not specify either SET or PRIVATE, the parameter and value are simply used 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 and does not appear in V$ tables or in an 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 V$ tables or graphical user interfaces). |
parameter |
is the Heterogeneous Services initialization parameter that you are specifying. See Appendix A, "Heterogeneous Services Initialization Parameters" for a description of all Heterogeneous Services parameters and their possible values. 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 parameter as follows:
HS_FDS_TRACE_LEVEL=ON
Typically, most parameters are only needed as initialization parameters, so you do not need to use SET or PRIVATE. Use SET for parameter values that the drivers or non-Oracle system need as environment variables.
PRIVATE is only supported for the follow Heterogeneous Services parameters:
You should only use PRIVATE for these parameters if the parameter value includes sensitive information such as a username or password.
The settings for the initialization parameters vary depending on the type of operating system.
Specify a File DSN or a System DSN which has previously been defined using the ODBC Driver Manager.
When connecting using a File DSN, specify the value as follows:
HS_FDS_CONNECT_INFO=FILEDSN=
file_dsn
When connecting using a System DSN, specify the value as follows:
HS_FDS_CONNECT_INFO=
system_dsn
If you are connecting to the data source through the driver for that data source, precede the DSN by the name of the driver, followed by a semi-colon (;).
Assume 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 init
HS_SID.ora
:
HS_FDS_CONNECT_INFO=sqlserver7
where 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:
Specify a DSN and the path of the ODBC shareable library, as follows:
HS_FDS_CONNECT_INFO=
dsn_valueHS_FDS_SHAREABLE_NAME=
full_odbc_library_path_of_odbc_driver
HS_FDS_CONNECT_INFO is required for all platforms for an ODBC agent. HS_FDS_SHAREABLE_NAME 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 to accept the default values or change them, see Appendix A, "Heterogeneous Services Initialization Parameters" for detailed information on all the initialization parameters. |
Assume that the odbc.ini
file for connecting to Informix using the Intersolve ODBC driver is located in /opt/odbc
and includes the following information:
[ODBC Data Sources] Informix=INTERSOLV 3.11 Informix Driver [Informix] Driver=/opt/odbc/lib/ivinf13.so Description=Informix Database=personnel@osf_inf72 HostName=osf LogonID=uid Password=pwd
In order to connect to this Informix database through the gateway, the following lines are required in init
HS_SID.ora
:
HS_FDS_CONNECT_INFO=Informix
HS_FDS_SHAREABLE_NAME=/opt/odbc/lib/libodbc.so
set INFORMIXDIR=/users/inf72
set INFORMIXSERVER=osf_inf72
set ODBCINI=/opt/odbc/odbc.ini
Note that the set statements are optional as long as they are specified in the working account. Each database has its own set statements.
The HS_FDS_CONNECT_INFO parameter value must match the ODBC data source name in the odbc.ini
file.
You can only set these parameters on the Windows NT platform.
Specify a data link (UDL) that has previously been defined:
SET|PRIVATE|SET PRIVATE HS_FDS_CONNECT_INFO="UDLFILE=data_link"
Or, specify the connection details directly:
SET|PRIVATE|SET PRIVATE HS_FDS_CONNECT_INFO="provider;db[,CATALOG=catalog]"
where:
provider |
is the name of the provider as it appears in the registry. The value is case sensitive. |
db |
is the name of the database |
catalog |
is the name of the catalog |
HS_FDS_CONNECT_INFO 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 tailored for your installation.
Note: Before deciding to accept the default values or change them, see Appendix A, "Heterogeneous Services Initialization Parameters" for detailed information on all the initialization parameters. |
To use an ODBC agent, you must have an ODBC driver installed on the same machine as the Oracle database server. On Windows NT, you must have an ODBC driver manager also located on the same machine. The ODBC driver manager and driver must meet the following requirements:
The ODBC driver and driver manager on Windows NT must conform to ODBC API conformance Level 1 or higher. If the ODBC driver or driver manager does not support multiple active ODBC cursors, then it restricts the complexity of SQL statements that you can execute using generic connectivity.
The ODBC driver you use must support all of the core SQL ODBC datatypes and should support SQL grammer level SQL_92. The ODBC driver should also expose the following ODBC APIs:
These requirements apply to OLE DB data providers that have an 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:
Interface | Methods |
---|---|
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) |
IErrorInfoFoot 1 |
GetDescription, GetSource |
IErrorRecords |
GetErrorInfo |
ILockBytes (OLE)Foot 2 |
Flush, ReadAt, SetSize, Stat, WriteAt |
IRowset |
GetData, GetNextRows, ReleaseRows, RestartPosition |
IStream (OLE) |
Read, Seek, SetSize, Stat, Write |
ISupportErrorInfo |
InterfaceSupportsErrorInfo |
ITransactionLocal (optional) |
StartTransaction, Commit, Abort |
1
You can also use IErrorLookup with the GetErrorDescription method. 2 Required only if BLOBs are used in the OLE DB provider. |
These requirements apply to OLE DB data providers that do not have SQL processing capabilities. If the provider exposes them, then OLE DB (FS) connectivity uses OLE DB Index interfaces.
OLE DB (FS) connectivity requires that the data provider expose the following OLE DB interfaces:
Interface | Methods |
---|---|
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)Foot 1 |
Flush, ReadAt, SetSize, Stat, WriteAt |
IRowsetIndexFoot 2 |
SetRange |
IErrorInfoFoot 3 |
GetDescription, GetSource |
IErrorRecords |
GetErrorInfo |
IRowset |
GetData, GetNextRows, ReleaseRows, RestartPosition |
IStream (OLE) |
Read, Seek, SetSize, Stat, Write |
ITransactionLocal |
StartTransaction, Commit, Abort |
ISupportErrorInfo |
InterfaceSupportsErrorInfo |
ITableDefinition |
CreateTable, DropTable |
IDBProperties |
SetProperties |
1
Required only if BLOBs are used in the OLE DB provider. 2 Required only if indexes are used in the OLE DB provider. 3 You can use IErrorLookup with the GetErrorDescription method as well. |
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.
The OLE DB data source must support the following initialization properties:
The OLE DB data source must also support the following rowset properties:
|
![]() Copyright © 1996-2001 Oracle Corporation. All Rights Reserved. |
|