7 Generic Connectivity

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

Description of the illustration heter004.gif

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

Description of the illustration heter005.gif

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

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.

HS_FDS_CONNECT_INFO=dsn

HS_FDS_CONNECT_INFO=sqlserver7

HS_FDS_CONNECT_INFO=dsn_value
HS_FDS_SHAREABLE_NAME=full_path_of_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

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

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"

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.

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:

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.

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