Data Migration & Integration Configuration

Configuring an ODBC data source using Oracle Heterogeneous Services (Oracle 9i and later) - Works for dg4odbc

This example setup is from a Sun Solaris Platform running Oracle 9.2 to a target Microsoft SQL Server database on Windows NT using the Openlink Generic ODBC driver.

1. Install Heterogeneous Services with the Oracle Installer. If it's already installed, you should have an $ORACLE_HOME/hs/ directory.
   
2. Install the data dictionary tables and views for Heterogeneous Services.
   
   1. Log in to the Oracle database as sys and run the script $ORACLE_HOME/rdbms/admin/caths.sql.
      
   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, 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 (found in ~/openlink/bin/ or ~/openlink/samples/ODBC/) and/or the HTTP-based OpenLink ODBC Administrator.
   
4. Add or correct the following entries in the $ORACLE_HOME/network/admin/tnsnames.ora and $ORACLE_HOME/network/admin/listener.ora files (changing the $ORACLE_HOME and host values to match your local environment):
   
   1. tnsnames.ora NOTE: The syntax used here differs from Oracle 8i (and earlier) syntax.
      
      

      
      hsodbc =
         (description=
            (address_list =
               (address=(protocol=tcp)(host=oplussol3)(port=1521))
            )
            (connect_data=
               (SID=hsodbc)
            )
            (HS=)
         )
      

      
      
   2. Listener.ora NOTE: The ENVS value must be entered on a single line, with no whitespace. This entry is only necessary if the OpenLink environment cannot reliably be set before starting the Listener, and is only recommended for such cases, as there are many variables and values which cannot be set here.
      
      

      
      sid_list_listener=
         (sid_list=
            (sid_desc=
               (sid_name=hsodbc)
               (ORACLE_HOME = /dbs/oracle8i/64-bit/8.1.6)
               (program = hsodbc)
               (ENVS = "LD_LIBRARY_PATH=/dbs/oracle8i/64-bit/8.1.6/lib:/dbs/openlink/32bit/v42/lib,PATH=/usr/ucb:/dbs/openlink/32bit/v42/bin:$PATH,OPL_LICENSE_DIR=/dbs/openlink/32bit/v42/bin" )
            )
         )
      

      
      
5. Set the OpenLink environment, and start the Oracle listener:
   
   

   
   source openlink.sh
   lsnrctl
   start
   

   
   
6. You should now have a service handler for hsodbc
   
7. Add or correct the following in the inithsodbc.ora located in $ORACLE_HOME/hs/admin/ (sample values -- your paths may vary):
   
   

   
   #
   # HS init parameters
   #
   # HS_FDS_CONNECT_INFO is the target ODBC DSN, defined in $ODBCINI 
   HS_FDS_CONNECT_INFO = pubs
   # HS_FDS_SHAREABLE_NAME is the full path to the Driver Manager, 
   # whether iODBC or otherwise
   HS_FDS_SHAREABLE_NAME = /dbs/openlink/32bit/v42/lib/libiodbc.so
   
   #
   # ODBC-specific environment variables -- should match your 
   # active openlink.sh
   #
   set ODBCINI=/dbs/openlink/32bit/v42/bin/odbc.ini
   set ODBCINSTINI=/dbs/openlink/32bit/v42/bin/odbcinst.ini
   #
   # OpenLink-specific environment variables -- should match your 
   # active openlink.sh
   #
   set OPENLINKINI=/dbs/openlink/32bit/v42/bin/openlink.ini
   set PATH=/dbs/openlink/32bit/v42/bin:$PATH
   # LD_LIBRARY_PATH might be SHLIB_PATH or LIBPATH, depending on 
   # operating system and bitness
   set LD_LIBRARY_PATH=/dbs/openlink/32bit/v42/lib:$LD_LIBRARY_PATH
   #
   # Optional OpenLink-specific environment variables -- generally 
   # match your active openlink.sh.  REQUIRED for Release 6.x and later
   #
   # set OPL_LICENSE_DIR=/dbs/openlink/32bit/v42/bin
   

   
   
8. Connect to the Oracle database and create a database link to access the target database. Be sure to use the appropriate single and double quotation marks as shown below.
   
   

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

   
   
9. Once that's done, you should be able to select from a remote table in your DSN:
   
   

   
   SQL> SELECT * FROM authors@hsodbc;