7
Generic Connectivity

This chapter describes the configuration and usage of Generic Connectivity a gents.

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

What Is Generic Connectivity?

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

Generic Connectivity is im plemented 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. ODBC agents are supported on Solaris, HP-UX, AIX, and Windows NT platforms. OLE DB agents are supported only on the Windows NT 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 Connectiv ity Architecture
• SQL Execution
• Datatype Mapping
• Generic Connectivity Restrictions

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 fir st use in that user session of the database link to the non-Oracle system. The agent process ends when the user session ends.

Generic Connecti vity Architecture

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 drive r 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 mac hine as the Oracle database server or on a different machine.

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 Machin es

Text description of the illustration heter004.gif

In this configuratio n:

1. A client connects to the Oracle database se rver through Oracle Net.
2. The Heterogeneous Services component of the Or acle 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 cli ent connects to the non-Oracle data store through a network.

Oracle and Non-Oracle Systems on the Same Machine

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

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

Text description of the illustration heter005.gif

In this c onfiguration:

1. A client connects to the Oracle database server through Oracle Net.
2. The Heterogeneous Services componen t 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.

SQL Exe cution

SQL statements sent using a Generic Connectivity agent are ex ecuted 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.

Da tatype Mapping

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 O LE DB datatype DBTYPE_DBTIMESTAMP are converted to Oracle's DATE datatype.

If a table contains a column whose datatype is not supported by Generic Connectivity, then the column information is not re turned to the Oracle server.

Generic Connectivity Restrictions

Generic Connec tivity restrictions include:

• A table including a BLOB column must have a separate column that serves as a primary key.
• BL OB and CLOB data cannot be read through passthrough queries.
• < /a>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.

Supported Oracle SQL Sta tements and Functions

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

• DELETE
• INSERT
• SE LECT
• UPDATE

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

If an Oracle SQL f unction 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 p ost-processed after they are returned from the non-Oracle system.

If an unsupported functio n 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 b
y the ODBC driver or OLE DB provider that is being used:

AVG(exp)
LIKE(exp)
COUNT(*)
MAX(exp)
MIN(exp)
NOT

 
Configuring Generic Connectivity Agents

 
To implement Generic Conn
ectivity on a non-Oracle data source, you must set the agent parameters.
 
This section conta
ins the following topics:

Creatin
g the Initialization File
Editing the Initial
ization File
Setting Initialization Parameter
s for an ODBC-based Data Source
Setting Initi
alization Parameters for an OLE DB-based Data Source
 
Creating the Initialization File

<
/a> 
You must create and customize an initialization file for your Generic Connectivity agent. Oracle Corporation suppl
ies 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 i
nitialization 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 to which the agent connects.

 
The H
S_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 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 initia
lization information. When you copy and rename your initHS_SID.ora file, ensure it
remains in the $ORACLE_HOME/hs/admin directory.

 
Editing the Initialization File


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



See Also:  
"Setting Initialization Parameters" for more information on parameters
<
/td>
 
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, then the parameter and value are simply used as an initialization parameter for the agent.<
/p>
 
SET specifies that in addition to being used as an initialization parameter, t
he 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 databa
se server, not appearing in V$ tables or graphical user interfaces).
 
parameter is the Heterogeneous Services initialization parameter that you are specifying. See "Setting Initialization Parameters" for a description of all Heterogeneous Services parameter
s 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, mo
st parameters are only needed as initialization parameters, so you do not need to use SET or PRIVATE. Use <
code>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:

HS_FDS_CONNECT_INFO
HS_FDS_SHAREABLE_NAME
HS_FDS_TRACE_LEVEL
 
You should only use PRIVATE for these parameters if the parameter value includes sensi
tive 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 Age
nt Parameters on Windows NT

 
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




Setting Parameters on NT: Exam
ple

 
Assume a system DSN has been defined in the Windows ODBC Data So
urce 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 followin
g procedure enables you to define a system DSN in the Windows ODBC Data Source Administrator, version 3.5:

From the Start menu, choose Settings > Control Panel and select the ODBC icon.
Select the system DSN tab to display the sys
tem data sources.
Click Add.
From the list of installed ODBC drivers, select the name of the driver that the dat
a source will use. For example, select SQL Server.
Click Finish.
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. 






Note: <
a name="1006158"> 
The name entered for the DSN must match the value of the parameter HS_FDS_CONNECT_INFO that is specified in initHS_SID.ora.


Refer to your ODBC driver documentation and follow the prompts to complete configu
ration of the DSN.
After creating the system DSN, click OK to 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=ful
l_path_of_odbc_driver




If your ODBC driver requires an ODBC d
river manager, then HS_FDS_SHAREABLE_NAME should point to the location of the ODBC driver manager and not to the ODBC dr
iver.
 
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 fo
r your installation.
 





Note:
  
Before deciding to accept the default values or change them, see "
Setting Initialization Parameters" for detailed information on all the initialization parameters.


 
Setting Parameters on UNIX: Example

 
The followi
ng 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=Data
Direct 4.10 SQL Server Wire Protocol
Database=oratst
LogonID=TKHOUSER
<
/a>Password=TKHOUSER
Address=sqlserver-pc,1433
QuotedId=No
AnsiNPW=
No

[ODBC]
Trace=0
TraceFile=/opt/odbc410/odb
ctrace.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 
initHS_SID.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 working account. Each database has its own set statements.
 
The HS_FDS_CONNECT_IN
FO 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 Windo
ws NT platform.
 
Specify a data link (UDL) that has previously been defined:
SET|PRIVATE|SET PRIVATE HS_FDS_CONNECT_INFO="UDLFILE=data_link<
/em>"








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


 
HS_FDS_CONNECT_INFO is required for an OLE DB agent. Othe
r initialization parameters have defaults or are optional. You can use the default values and omit the optional parameters, or you ca
n specify the parameters with values tailored for your installation.
 





Note:  
Before deciding to accept the default values o
r change them, see "Setting Initialization Parameters" for detailed i
nformation 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 the Oracle databa
se server. On Windows NT, you must have an ODBC driver manager also located on the same machine. The ODBC driver manager and driver m
ust meet the following requirements:

On Windows NT machines, a
 32-bit ODBC driver that conforms to ODBC version 2.5 is required.
On UNIX mac
hines, a 32-bit ODBC driver which conforms to ODBC version 2.5 is required. If the ODBC driver works with an ODBC driver manager that
 conforms to ODBC version 2.5, then the ODBC driver version can be 2.5 or higher. 





See Also:  
Your ODBC driver doc
umentation for dependencies on an ODBC driver manager
 
The
 ODBC driver and driver manager on Windows NT 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, then it restricts the complexity of SQL statemen
ts that you can execute using Generic Connectivity.
 
The ODBC driver you use must support al
l of the core SQL ODBC datatypes and should support SQL grammar level SQL_92. The ODBC driver should also e
xpose the following ODBC APIs:
 
 Table 7-1 ODBC Functions 
<
/tr>
 
-
<
td class="Formal">
SQLGetTypeInfo
<
td class="Formal">
Recommended if used by the non-Oracle system.



 ODBC Function
 <
strong>Comment


 
SQLAllocConnect
 
-

 
SQLAllocEnv<
/p>
 
-

 
SQLAllocStmt
 
-

 
SQLBindCol
 
-

 
SQLBindParameter
 
-

 
SQLColumns
 
-

 
SQLConnect


 
SQLDescribeCol
 
-

 
SQLDis
connect
 
-

 
SQLDriverConnect
 
-


 
SQLError
 
-

 
SQLExecDirect
 
-

 
SQLExecute
 
-


 
SQLExtendedFetc
h
 
Recommended if used by the non-Oracle system.

 
SQLFetch
 
-

 
SQLForeignKeys
 
Recommended if used by the non-Oracle system.

<
a name="1006313"> 
SQLFreeConnect
 
-<
/p>

 
SQLFreeEnv

 
-

 
SQLFreeStmt
 
-

 
SQLGetConnectOption
 
-

 
SQLGetData
 
-

 
SQLGetFunctions
 
-
<
/td>

 
SQLGetInfo
 
-
 

 
-

 
SQLNumParams
 
Recommended if used by the non-Oracle
system.

 
SQ
LNumResultCols
 
-

 
SQLParamData
 
-

<
/a> 
SQLPrepare
 
-

 
SQLPrimaryKeys
 


 
SQLProcedureColumns
 
Recommended if used by the non-Oracle system.

 
SQLProcedures
 
Recommended if used by the non-Oracle system.

 
SQLPutData
 
-



SQLRowCount
 
-

 
SQLSetConnectOption

 
-

 
SQLSetStmtOption
 
-

 
S
QLStatistics
 
-

 
SQLTables
 
-

 
SQLTransact
 
Recommended if used by the n
on-Oracle system.
 
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.
<
a name="1006406"> 
Generic Connectivity passes the username and password to the provider when calling IDBInit
ialize::Initialize().
 
OLE DB (SQL) connectivity requires that the data provider expo
se the following OLE DB interfaces:
 
 <
font face="Arial, Helvetica, sans-serif">Table 7-2 OLE DB (SQL) Interfaces
 
-
<
tr class="Formal" align="left" valign="top">

<
td class="Formal">
GetData, GetNextRows, ReleaseRows, RestartPosition
 
ISupportErrorInfo



 Interface
 Methods
 Notes



 
IAccessor
 
CreateAccessor, ReleaseAccessor
 
-

 
ICo
lumnsInfo
 
GetColumnsInfo (Command
and Rowset objects)
 
-

 
ICommand
 
Execute
 
-
<
/td>

 
ICommandPrepar
e
 
Prepare
 
-



ICommandProperties
 
SetProperties<
/code>
 
-

 
ICommandText
 
SetCommandText
 
-

 
ICommandWithParameters

 
GetParameterInfo
 
-


 
IDBCreateCommand
 
CreateCommand
 
-


 
IDBCreateSession
 
CreateSession
 
-

 
IDBInitialize
 
Initialize
 
-

 
IDBSchemaRowset
 
GetRowset (tables, col
umns, indexes; optionally also procedures, procedure parameters)
 
-<
/p>

 
IErrorInfo

 
GetDescription, GetSource
 
You can also use IErrorLookup with the GetErrorDescript
ion method.

 
IErrorRecords
 
GetErrorInfo


 
ILockBytes (OLE)
 
Flush, ReadAt, SetSize, Stat, WriteAt
 
Required only if BLOB datatypes are used in the OLE DB provider.
 
IRowset
 

 
-

 
IStream (OLE)

 
Read, Seek, SetSize, Stat, Write
 
-

<
/a> 
InterfaceSupportsErrorInfo
 
-

 
ITransactionLoca
l (optional)
 
StartTransaction, Commit, Abort
 
-
 
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, then OLE DB (FS) connectivity uses OLE DB Index interfaces.
 
OLE DB Int
erfaces for Data Providers to Expose

 
OLE DB (FS) connectivity requir
es that the data provider expose the following OLE DB interfaces:
 
 Table 7-3 OLE DB (FS) Interfaces  




 Interface
 Methods
 Notes


 
IAccessor
 
CreateAccessor, ReleaseAccessor
 
-

 
IColumnsInfo
 
GetColum
nsInfo (Command and Rowset objects)
 
-

 
IOpenR
owset
 
OpenRowset
 
-

 
IDBCreateSession
 
CreateSes
sion
 
-

 
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
 
Requ
ired only if BLOB datatypes 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
<
/td>
 
-

 
IStream (OLE)
 
Read, Seek, SetSize, Stat, Write
 
-

 
ITransactionLocal (optional)
 

StartTransaction, Commit, Abort
 
-

 
ISup
portErrorInfo
 
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 bookm
arks being exposed, the tables are read-only.

 
Data Source Properties

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

DBPROP_INIT_DATASOURCE

DBPROP_AUTH_USERID 


DBPROP_AUTH_PASSWORD 






Note:  
Required if the userid has been supplied in the security file





Note:  
Required if th
e userid 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_UP
DATABILITY = CHANGE+DELETE+INSERT
DBPROP_OWNUPDATEDELETE = TRUE

DBPROP_OWNINSERT = TRUE
DBPROP_OTHERUPDATEDELETE = TRUE
DBPROP_
CANSCROLLBACKWARDS = TRUE
DBPROP_IRowsetLocate = TRUE

DBPROP_OTHERINSERT = FALSE