anyone using HS Generic Connectivity?

#1

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

#2

Thanks for the reply.

My primary objective is to query SQL Server on demand. I
agree it would be infinitely easier to send the data from
SQL to Oracle, but I don't have that luxury.

I already found the install notes from Oracle. I think the problem
I'm having is because we are running the 64bit version of Oracle on
the Solaris box. The odbc drivers are 32bit. Do you know if
this is a conflict?

Thanks.

Netrista Khatam wrote:

Quote:

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.

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 "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@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 (DOT) com> wrote

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

anyone using HS Generic Connectivity?

comp.databases.oracle.misc comp.databases.oracle.misc

anyone using HS Generic Connectivity? - 12-18-2002 , 07:24 PM

Re: anyone using HS Generic Connectivity? - 12-20-2002 , 09:49 AM