oracle和sqlserver互访

ODBC with Oracle Heterogeneous Services

Using DataDirect Connect® for ODBC with Oracle Heterogeneous Services

View PDF    摘自Datadirect http://www.datadirect.com/developer/odbc/oracle_heterogeneous/index.ssp

Introduction

Corporate developers frequently need to integrate and consolidate data residing in diverse database systems. Oracle users make use of Oracle Heterogeneous Services to integrate non-Oracle data into their primary database. Using the Generic Connectivity agent, Oracle users can communicate to another database or data store, such as XML, via ODBC.

DataDirect Connect for ODBC provides the most reliable and highest performing data access for users of Oracle Heterogeneous Services. Connect for ODBC does not require the installation and configuration of database client libraries. This dramatically improves performance and scalability and simplifies configuration of Oracle Generic Connectivity, while providing access to a larger number of various data sources.

This paper explains how to use DataDirect Connect for ODBC with Oracle Heterogeneous Services.

DataDirect Connect for ODBC in an Oracle Heterogeneous Services Environment

Generic Connectivity Architecture

Generic Connectivity is implemented by using a Heterogeneous Services ODBC agent. An ODBC agent is included as part of your Oracle system. Be sure to use the agent shipped with your particular Oracle system and installed in the same $ORACLE_HOME.

To access the non-Oracle data store using Generic Connectivity, the agent works with an ODBC driver. The ODBC driver that you use must be on the same platform as the ODBC agent. The non-Oracle data stores can reside on the same machine as the Oracle database or a different machine.

Installation Steps

This example shows the configuration of Generic Connectivity on a SUN Solaris system using DataDirect's ODBC driver to connect to Microsoft SQL Server. Please check your Oracle documentation for specific version and platform support.

1) Install the data dictionary tables and views for Heterogeneous Services.

Using the server manager or sqlplus logged on as sys, run caths.sql. For example using the server manager you can use the following example:

SQL> connect internal
SQL> @
   
   /rdbms/admin/caths.sql;

This script is located in $ORACLE_HOME/rdbms/admin

2) Install the DataDirect Connect for ODBC Driver.

Some non-Oracle data stores will require that particular database's client library components to be installed. If the database is DB2, Sybase, SQL Server or Informix you should use the Connect for ODBC Wire Protocol driver for the particular database you are trying to access. These drivers do not require any additional components to be installed to connect to the database.

3) Configure your odbc data source in the .odbc.ini file. The example below is a data source to connect to MS SQL Server 2000

Note: Annotations to following examples file begin with the symbol and should not be included in the actual file.

[SQLServer Wire Protocol]
Driver=/opt/odbc/lib/ivmsssXX.so    Configured during ODBC driver installation
Description=SQL Server
Database=dbname                     Name of target database.
Address=120.2.200.176,1433          IP address and port of target database.
Quoteld=No
AnsiNPW=No

4) Make sure the following entries are in the tnsnames.ora and listener.ora.

TNSNAMES.ORA

hsodbc=
  (description=
    (address=(protocol=tcp)(host=hostname)(port=1521))
    (connect_data=(sid=hsmsql))      Needs to match the sid in listener.ora.
    (hs=ok)                          hs clause goes in the description.
  )

LISTENER.ORA

listener =
  (description_list =
    (description =
      (address_list =
        (address = (protocol = tcp)(host = unixhost)(port = 1521))
      )
    )

sid_list_listener=
  (sid_list=
    (sid_desc=
      (sid_name=hsmsql)                        Match the sid in tnsnames.ora.
      (oracle_home=/db/oracle/product/8.1.6)   Appropriate $ORACLE_HOME
      (program= hsodbc)                        Agent Executable 
    )
  )

5) Before starting the listener, make sure the ODBC lib directory is specified in the shared library environment variable.

LD_LIBRARY_PATH=/db/oracle/product/8.1.6/bin:/opt/odbc/lib/        odbc lib path

After the LD_LIBRARY_PATH has been modified, start the listener.

Run "lsnrctl services" to verify that you now have a service handler for the hsmsql sid.

LSNRCTL> services
   Connecting to (address=(protocol=tcp)(host=unixhost)(port=1521))
   Services Summary...
     hsmsql        has 1 service handler(s)     
       DEDICATED SERVER established:0 refused:0
         LOCAL SERVER
   The command completed successfully

6) Create the Initialization file. You must create and customize an initialization file for your generic connectivity agent. Oracle supplies a sample initialization file named "inithsodbc.ora" which is stored in the $ORACLE_HOME/hs/admin directory.

To create an initialization file, copy the appropriate sample file and rename the file to initHS_SID.ora. In this example the sid noted in the listener and tnsnames files is "hsmsql" so the new initialization file is called inithsmsql.ora.

7) Make sure the following noted entries are in the inithsmsql.ora now located in $ORACLE_HOME/hs/admin

INITMSQL.ORA

# HS init parameters
#
HS_FDS_CONNECT_INFO = MS_SQLServer7         odbc data_source_name
HS_FDS_TRACE_LEVEL = 0                      trace levels 0 - 4  (4 is verbose)
HS_FDS_TRACE_FILE_NAME = hsmsql.trc         trace file name
HS_FDS_SHAREABLE_NAME = /opt/odbc/lib/libodbc.so   full path to odbc driver. 
# 
#
# ODBC specific environment variables
#
set ODBCINI=/opt/odbc/odbc.ini              location of odbc.ini 
#
# Environment variables required for the non-Oracle system
#

8) Create a database link to access target database. Be sure to use the appropriate quotes as noted below.

SQL> create database link hsmsql
SQL> connect to "user" identified by "password" <= valid user/pwd on target DB
SQL> using  'hsodbc';

9) To test, run a simple query of a known table on the target datastore.

SQL> select * from employee@hsmsql;

empid	firstname	lastname	department	job
----------	---------------	---------------	----------	---
10000	Joseph	Johnston	Sales	CDW
10001	John	Ladd	Sales	WNV
10002	Ronald	Wall	Relations	NPI
10003	Julie	Reynolds	Relations	NPO
10004	Bill	Baird	Telemarket	PHN
10005