8
Managing Heterogeneous Services Using Generic Connectivity

The following topics describe the configuration and usage of generic connectivity agents:

• What Is Generic Connectivity?

• Supported Oracle SQL Statements

• Configuring Generic Connectivity Agents

• ODBC Connectivity Requirements

• OLE DB (SQL) Connectivity Requirements

• OLE DB (FS) Connectivity Requirements

What Is Generic Connectivity?

Generic connectivity is intended for low-end data integration solutions requiring the ad hoc query capability to connect from Oracle8i 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 Oracle8i system. Be sure to use the agents shipped with your particular Oracle system and 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:

• Types of Agents

• Generic Connectivity Architecture

• SQL Execution

• Datatype Mapping

• Generic Connectivity Restrictions

Types of Agents

Generic connectivity is implemented as one of the following types of HS 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.

Generic Connectivity Architecture

To access the non-Oracle data store using generic connectivity, the agents work with an ODBC or OLE DB driver. Oracle8i 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 Oracle8i or a different machine.

Oracle and Non-Oracle Systems on Separate Machines

Figure 8-1 shows an example of one configuration in which an Oracle and non-Oracle database are on separate machines, communicating through an HS ODBC agent:

Figure 8-1 Non-Oracle System on Separate Computer

In this configuration, a client connects to Oracle8i through Net8. The HS part of the Oracle database server then connects through Net8 to the Heterogeneous Services ODBC agent. This 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.

Oracle and Non-Oracle Systems on Same Machine

Figure 8-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 HS ODBC agent:

Figure 8-2 Accessing Heterogeneous Non-Oracle Systems

In this configuration, a client connects to Oracle8i through Net8. The HS part of the Oracle database server then connects through Net8 to the Heterogeneous Services ODBC agent. This 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.

SQL Execution

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 may be manipulated as if it were a table in a relational database. The naming conventions used at the non-Oracle system may also depend on whether you are using an ODBC or OLE DB agent.

Datatype Mapping

Oracle8i maps the datatypes used in ODBC and OLE DB compliant data sources to supported Oracle datatypes. When the results of a query are returned, Oracle8i 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.

See Also: Appendix D, "Datatype Mapping" for information on how the datatypes are mapped for each data source.

Generic Connectivity Restrictions

Following are some restrictions for generic connectivity:

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

• BLOB/CLOB data cannot be read through passthrough queries.

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

• Stored procedures are not supported.

• Generic connectivity agents cannot participate in distributed transactions--they support single-site transactions only.

Supported Oracle SQL Statements

Generic connectivity supports the following statements, but only if your ODBC or OLE DB driver and non-Oracle system can execute them and 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, many Oracle functions are not supported by generic connectivity, although post-processing is performed by Oracle8i, 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 Oracle8i 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

Functions Supported by Generic Connectivity

Generic connectivity assumes that this minimum set of SQL functions is supported:

• AVG(exp)

• LIKE(exp)

• COUNT(*)

• MAX(exp)

• MIN(exp)

• NOT

Configuring Generic Connectivity Agents

To implement generic connectivity to a non-Oracle data source, you need to 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

Creating the Initialization File

You must create and customize an initialization file for your generic connectivity agent. Oracle supplies sample initialization files named initagent.ora, where agent might be odbc, olesql, or olefs, to indicate which agent the sample file can be used for, as in the following:

initodbc.ora
initolesql.ora
initolefs.ora

The sample files are stored in the /admin directory for that particular agent, in the $ORACLE_HOME/rdbms/hs path.

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

The HS_SID is also used to identify how to connect to the agent when you configure the listener by modifying your listener.ora file. The HS_SID you add to the listener.ora file must match the HS_SID in an initHS_SID.ora file, because the agent spawned by the listener searches for a matching initHS_SID.ora file. That is how each agent process gets its initialization information. When you copy and rename your initHS_SID.ora file, ensure it remains in the /admin directory for that particular agent in the $ORACLE_HOME/rdbms/hs path.

See Also: "Step 2: Set Up the Environment to Access Heterogeneous Services Agents" for more information on configuring the listener.

Editing the Initialization File

Customize the initHS_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 your initHS_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.

To set the parameter values, use the syntax:

[SET][PRIVATE] parameter=value

where:

[SET][PRIVATE]	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 HS parameters and their possible values. The parameter is case-sensitive.
value	is the value you want to specify for the HS 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 your drivers or non-Oracle system need as environment variables.

PRIVATE is only supported for these Heterogeneous Services parameters:

• HS_FDS_CONNECT_INFO

• HS_FDS_SHAREABLE_NAME

• HS_FDS_TRACE_LEVEL

• HS_FDS_TRACE_FILE_NAME

You should only use PRIVATE for these parameters if the parameter value includes sensitive information such as a username or password.

Setting Initialization Parameters for an ODBC-based Data Source

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

Setting Agent Parameters on Windows NT

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 using the following syntax:

HS_FDS_CONNECT_INFO=FILEDSN=file_dsn

When connecting using a System DSN, specify the value using:

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 (;).

Setting Parameters on NT: Example

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 initHS_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:

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

2. Select the System DSN tab 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 your ODBC driver. For example, for SQL Server enter the SQL Server machine.

   Note: The name entered for the DSN must match the name used for the gateway in initHS_SID.ora.

7. Continue clicking Next and answering the prompts until you reach the end (that is, you click Finish).

8. Click OK until you exit the ODBC Data Source Administrator.

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

Setting Parameters on UNIX: Example

Assume that the odbc.ini file to connect 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=Informix7
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 initHS_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 will have its own set statements.

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

Setting Initialization Parameters for an OLE DB-based Data Source

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

ODBC Connectivity Requirements

To use an ODBC agent, you must have an ODBC driver installed on the same machine as Oracle8i. On Windows NT, you must have an ODBC driver manager also located on the same machine. The ODBC driver manager and driver must meet these requirements:

• On Windows NT machines, a thread-safe, 32-bit ODBC driver Version 2.x or 3.x is required. You can use the native driver manager supplied with your Windows NT system.

• On UNIX machines, ODBC driver Version 2.5 is required. A driver manager is not required.

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 expose the following ODBC APIs:

Table 8-1 ODBC Functions (Page 1 of 3)

ODBC Function	Comment
SQLAllocConnect
SQLAllocEnv
SQLAllocStmt
SQLBindCol
SQLBindParameter
SQLColumns
SQLConnect
SQLDescribeCol
SQLDisconnect
SQLDriverConnect
SQLError
SQLExecDirect
SQLExecute
SQLExtendedFetch	Recommended if used by your non-Oracle system.
SQLFetch
SQLForeignKeys	Recommended if used by your non-Oracle system.
SQLFreeConnect
SQLFreeEnv
SQLFreeStmt
SQLGetConnectOption
SQLGetData
SQLGetFunctions
SQLGetInfo
SQLGetTypeInfo
SQLNumParams	Recommended if used by your non-Oracle system.
SQLNumResultCols
SQLParamData
SQLPrepare
SQLPrimaryKeys	Recommended if used by your non-Oracle system.
SQLProcedureColumns	Recommended if used by your non-Oracle system.
SQLProcedures	Recommended if used by your non-Oracle system.
SQLPutData
SQLRowCount
SQLSetConnectOption
SQLSetStmtOption
SQLStatistics
SQLTables
SQLTransact	Recommended if used by your non-Oracle system.

OLE DB (SQL) Connectivity Requirements

These requirements apply to OLE DB data providers that have an SQL processing capability and expose the OLD DB interfaces. The data providers in this case are the non-Oracle system you want to connect to using generic connectivity and OLE DB (SQL).

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

Data Provider Requirements

OLE DB (SQL) connectivity requires that the data provider expose the following OLE DB interfaces:

Table 8-2 OLE DB (SQL) Interfaces (Page 1 of 2)

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)
IErrorInfo1	GetDescription, GetSource
IErrorRecords	GetErrorInfo
ILockBytes (OLE)2	Flush, ReadAt, SetSize, Stat, WriteAt
IRowset	GetData, GetNextRows, ReleaseRows, RestartPosition
IStream (OLE)b	Read, Seek, SetSize, Stat, Write
ISupportErrorInfo	InterfaceSupportsErrorInfo
ITransactionLocal (optional)	StartTransaction, Commit, Abort

1 You can use IErrorLookup with the GetErrorDescription method as well. 2 Required only if BLOBs are used in the OLE DB provider.

OLE DB (FS) Connectivity Requirements

These requirements apply to OLE DB data providers that do not have SQL processing capabilities. The data providers in this case are the non-Oracle systems you want to connect to using a generic connectivity agent and OLE DB (FS). OLE DB (FS) connectivity uses OLE DB Index interfaces, if the provider exposes them.

Required usernames and passwords are passed to the provider when the application calls IDBInitialize::Initialize().

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.

Bookmarks

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

OLE DB Interfaces

The data provider must provide the following OLE DB interfaces:

Table 8-3 OLE DB (FS) 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)1	Flush, ReadAt, SetSize, Stat, WriteAt
IRowsetIndex2	SetRange
IErrorInfo3	GetDescription, GetSource
IErrorRecords	GetErrorInfo
IRowset	GetData, GetNextRows, ReleaseRows, RestartPosition
IStream (OLE)a	Read, Seek, SetSize, Stat, Write
ITransactionLocal (optional)	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.

Data Source Properties

The OLE DB data source must support these initialization properties:

• DBPROP_INIT_DATASOURCE

• DBPROP_AUTH_USERID

  required if the userid has been supplied in the security file

• DBPROP_AUTH_PASSWORD

  required if the userid and password have been supplied in the security file

The OLE DB data source must also support these 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