Re: anyone using HS Generic Connectivity?

From: Netrista Khatam <nkhatam_at_openlinksw.com>
Date: 19 Dec 2002 13:13:56 -0800
Message-ID: <f1e2cfd1.0212191313.cc26c54@posting.google.com>

Hello Tim,

What's your primary objective? If you want to query SQLServer through Oracle HS--via iodbc/tds--I can provide you with basic instructions and tips to start you on your way. In fact, I'll post my modified Oracle notes at the bottom of this reply. The example uses the OpenLink ODBC Driver. Therefore, you will need to modify some sections for your own Free TDS driver.

If you really want to bypass HS, there are other options. It all depends on what you want to do. If you are trying to Migrate data from SQLServer to Oracle, you could try the SQLServer DTS export feature. It is substantially easy to configure in comparison to HS. However, you will need an Oracle ODBC driver on Windows. OpenLink Software has a product that can allow you to take snapshots of SQLServer data and push them into Oracle. It's similar to the DTS Wizard, but it removes some of the headache involved with OLE DB providers and data type translation. OpenLink also has a product that can allow you to query heterogeneous data in Oracle and SQLServer simultaneously. Finally, let me know if I can help in any way. I work with a variety of database and database products, and I'm quite familiar with data migration techniques.

SCOPE & Application

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

Install Heterogeneous Services with the Oracle Installer. If it's already installed, you'll see an "hs" directory under $ORACLE_HOME
Install the data dictionary tables and views for Heterogeneous Services.

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 "odbctest" 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 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 that's done, you should be able to select from a remote table in your DSN:

SQL> SELECT * FROM authors_at_hsodbc;

Best regards,
Netrista Khatam
Technical Support Manager
OpenLink Software Product Support
E-Business Infrastructure Technology Provider http://www.openlinksw.com

Timbo <tjbacs_nospam_at_attbi.com> wrote in message news:<3E011FE4.4040908_at_attbi.com>...

> I'm running 9.2.0 on Solaris 8.  Need to query SQL Server 2000 on NT.
> Trying to avoid the licensing expense of transparent gateway, as only
> simple queries are required.
> 
> The Generic Connectivity feature of Heterogeneous Services uses ODBC to
> talk to other databsases.  It requires quite a bit of setup.
> 
> Is anyone doing this?  I'm attempting to use the freeTDS driver and
> the iODBC driver manager.
> 
> Any experiences, tips, etc, would be appreciated.
> 
> Tim

Received on Thu Dec 19 2002 - 15:13:56 CST