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.
   
   1. Log in to the Oracle database as sys.
      
   2. 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%';
      

      
      
   3. If they are not already present, run the "caths.sql" script. This script is located in rdbms/admin.
      
      

      
      cd $ORACLE_HOME
      sqlplus   # provide authentication when prompted
      @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 (found in ~/openlink/bin/ or ~/openlink/samples/ODBC/) and/or the HTTP-based OpenLink ODBC Administrator.
     
   • If your driver has an HSODBC_FIX or similar option, be sure it is enabled in the DSN you'll be using. This is known to be present in OpenLink drivers for PostgreSQL.
     
4. Make sure the following entries are added to the $ORACLE_HOME/network/admin/tnsnames.ora and $ORACLE_HOME/network/admin/listener.ora files. Change the host and $ORACLE_HOME values to suit your installation.
   
   • 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
   

   
   
6. You should now have a service handler for hsodbc.
   
7. Make sure all environment variables set by the openlink.sh are also set in the $ORACLE_HOME/hs/admin/inithsodbc.ora (sample values -- your paths may vary). Pay particular attention to the shared library path settings, as this variable name varies on different platforms.
   
   

   
   # 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 FREETDSCONF=/dbs/openlink/32bit/v42/bin/freetds.conf
   set OPL_LICENSE_DIR=/dbs/openlink/32bit/v42/bin/
   set PATH=$PATH:/dbs/openlink/32bit/v42/bin
   set LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/dbs/openlink/32bit/v42/lib
   

   
   
8. 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                      # <= the link you'll reference in Oracle queries 
   SQL>   CONNECT TO "user" IDENTIFIED BY "password"     # <= valid user/pwd on target DB
   SQL>   USING 'hsodbc';                                # <= the TNSname you defined earlier
   

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

   
   SQL> SELECT * FROM table@hsodbc;