Data Migration & Integration Configuration

Configuring an ODBC data source using Oracle Heterogeneous Services (Release 8.1.7 and below)

PURPOSE

A step by step guide to setting up and troubleshooting Heterogeneous Services using Generic Connectivity on a Unix platform.

Instructions

1. Install Heterogeneous Services with the Oracle Installer. If it's already installed, you'll see an "hs" directory under $ORACLE_HOME,

2. Install the data dictionary tables and views for Heterogeneous Services.

Log in to the Oracle database as sys and run the "caths.sql" script. This
script is located in $ORACLE_HOME/rdbms/admin.

The data dictionary tables and views may already be installed on the
server. You can query the data dictionary to check for their existence.

   select table_name from dba_tables where table_name like 'HS%';
   select view_name from dba_views where view_name like 'HS%';

   If they are not, run the script as follows:
           cd $ORACLE_HOME
           sqlplus  (provide authentication)
           @rdbms/admin/caths.sql

3. Install your ODBC driver and configure an ODBC DSN. Ensure that connections work by testing via the "iodbctest" sample application and/or the iODBC HTTP Administrator.

4. Make sure the following entries are added to the "network/admin/tnsnames.ora" and "network/admin/listener.ora" files. Change the "host" / ORACLE_HOME values according to your system:

Tnsnames.ora

       hsodbc=
         (description=
           (address=(protocol=tcp)(host=solaris_server)(port=1521))
           (connect_data=(sid=hsodbc))
           (hs=ok)
     )
}}}}

   Listener.ora
   
{{{
       sid_list_listener=
         (sid_list=
           (sid_desc=
             (sid_name=hsodbc)
             (ORACLE_HOME = /dbs/oracle8i/64-bit/8.1.6)
             (program= hsodbc)
           )
         )

5. Start the Oracle listener:

       lsnrctl
       start

You should now have a service handler for hsodbc

6. Make sure the following noted entries are in the inithsodbc.ora located in $ORACLE_HOME/hs/admin (sample values -- your paths may vary):

   # This is a sample agent init file that contains the HS parameters that are
   # needed for an ODBC Agent.

   #
   # HS init parameters
   #
   HS_FDS_CONNECT_INFO = <Your ODBC DSN Name>
   HS_FDS_TRACE_LEVEL = 4
   HS_FDS_TRACE_FILE_NAME = hs.log
   # *** Full path to ODBC Driver Manager ***
   HS_FDS_SHAREABLE_NAME = /dbs/openlink/32bit/v42/lib/libiodbc.so

   #
   # ODBC specific environment variables
   #
   set ODBCINI=/dbs/openlink/32bit/v42/bin/odbc.ini
   set ODBCINSTINI=/dbs/openlink/32bit/v42/bin/odbcinst.ini
   # *** The following variables are extra requirements of the OpenLink ODBC drivers ***
   set OPENLINKINI=/dbs/openlink/32bit/v42/bin/openlink.ini
   set PATH=$PATH:/dbs/openlink/32bit/v42/bin
   set LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/dbs/openlink/32bit/v42/lib

7. Connect to the Oracle database and create a database link to access the target database. Be sure to use the appropriate quotes as noted below.

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

Once this is done, you should be able to select from a remote table in your DSN:

   SQL> SELECT * FROM authors@hsodbc;