« April 2009 | Main | September 2009 »

July 23, 2009

Connecting Oracle 10g on Solaris Sparc 64 to SQL Server on Windows using FREE ODBC Driver - Part Two

In Part One of this two-part mini-series we reviewed the setup required to connect to a remote SQL Server database using a unix/Solaris 9 free odbs driver. Now comes the tricky part - making this connection useful. The goal is to be able to call this remote data directly from sql/code within our Oracle 10gR2 database. A noble goal it is....

From the first article, we know we have a working odbc connection. If you don't have this, go back and make sure that much is working before proceeding. This is one of those times when you need to go step by step or you will have very little chance of making the whole thing work. Such things as useful error messages and/or log files aren't available.

In order to complete this project, we need the following:

We can start with the HS setup. Oracle has two "things" that connect to odbc drivers - HS (which has the advantage of being free) and Oracle Gateway. I only know about HS, but there is a lot of documentation on Oracle Gateway as well. The Gateway provides a more robust connection environment. In order to get HS going, we go to the hs admin folder which is located at ORACLE_HOME/hs/admin.

Create an initclmd.ora file in the hs/admin directory. You create this config file to match the name you gave to your connection when we first started. For me it is "clmd", so the config file is named initclmd.ora - you get the idea.

initclmd.ora -

# 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 = clmd
HS_FDS_TRACE_LEVEL = 4
HS_FDS_SHAREABLE_NAME = /[your path]/odbc/lib/libodbc.so
HS_FDS_TRACE_FILE_NAME = /[your path]/odbc/tmp/freetds.trc
#
# ODBC specific environment variables
#
set ODBCINI=/[your path]/odbc/etc/odbc.ini


#
# Environment variables required for the non-Oracle system
#
#set =

Fill in your connection name, which you should now know by heart. trace_level - 4, I don't know, it was suggested in the documentation and works.
shareable_name = this is the path to your odbc libraries. Be sure to look in there and verify that you have the libodbc.so file or you are just wasting your time.
trace_file_name = make something up.
ODBCINI = path and name to your odbc.ini file
Save it, you are done configuring HS. Now it knows where to go to find your odbc driver.

Here's the part that always gives me trouble - SQL Net. Here, we need to configure the tnsnames.ora and listener.ora files to get a listener for our requests to the remote database. I wound up stopping and starting the listener quite a few times before getting this part right, but maybe that's just me. I hope that these copies of my config files will help you.

tnsnames.ora - add in a section like this

CLM_Pay = (DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)
(HOST=[name of server with Oracle database on it])(PORT=1521))
(CONNECT_DATA = (SID=clmd)
)
(HS=OK)
)

The name of this tnsnames entry isn't too significant, you just use that same name when configuring the database link. The HOST and PORT should mirror the host name and port number your Oracle database listener uses. Port 1521 is the standard port, but your system might be using a different port. Under the CONNECT_DATA use SID, not SERVICE_NAME. You recognize the now familiar odbc connection name. The HS=OK entry tells Oracle to go look in the initclmd.ora for the connection information. Done here.

back up your listener.ora file (just in case - I needed the original several times...)

listener.ora - putting it all into action

# listener.ora Network Configuration File: /[your OH]/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =[your OH])
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME=clmd)
(ORACLE_HOME=[your OH])
(PROGRAM=hsodbc)
(ENVS=LD_LIBRARY_PATH=/[your OH]/lib32:/[your path]/odbc/lib)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = [local server])(PORT = 1521))

This is by far the most complicated config file. There are a lot of unwritten rules. What we need to do is to add an item to the sid_list_listener= sid_list. I'm showing you the PLSExtProc entry so that you can see the syntax for adding in another "sid_list" item. PLSExtProc has nothing to do with the odbc connection. It is just another listener item we happen to have on our database. So we add a SID_DESC item to the SID_LIST.
SID_NAME = if you don't know it by now, it's hopeless ;-)
ORACLE_HOME= copy paste from your other entries
PROGRAM = hsodbc That, you just need to know (now you do).
ENVS=LD_LIBRARY_PATH= ORACLE_HOME/lib32: /[your path]/odbc/lib
OK, this is important. I have not seen this in any documentation, and would not have gotten this right without Oracle Support's help. First is the syntax for adding two library paths. Just copy mine - it's right. Second, notice the first part of the library path - what is that? 32 bit libraries! This is where you convert your 64 bit database to 32 bit - Oracle has already provided the 32 bit libraries for you, you just have to point to them. Everything else - odbc, freetds, use 32 bit libraries to match the remote database (NOT 64 bit libraries to match your OS/database!). Dave reminded me that the 64 bit OS can read 32 bit libraries (but not vice versa). So all the confusion about 64 - 32 bit libraries comes down to right here. Just have your 64 bit Oracle database call its own 32 bit libraries and use 32 bit libraries everywhere else.

One more thing before leaving this part - only two items should be aligned on the leftmost column of the listener.ora file. The SID_LIST_LISTENER =, and the LISTENER = are the only items that go against the left margin. Everything else must be indented (it's hard to see on my example because they aren't indented very much). Learned that the hard way....

stop and restart the listener. If it restarts without error, you should see your new listener item displayed in the output. Once you get this piece going, you're nearly done.

Create a database link:

create public database link [any name you choose]
connect to useridforremotedatabase identified by remotepassword
using 'CLM_Pay';

This is standard stuff - the using 'xxxxxxxx' part must match whatever you put in the tnsnames.ora entry. Here's the final product:

From your favorite SQL spot:
select * from school_codes@clmd;

RESULT SET:

1 1 WOU 00320900 WO Western Oregon University wou.jpg Dona Vasas N N


Gee, it looks so simple! Well, you know better, but that's often what we work for - making something that's pretty darn complex look easy!

By the way, the second connection you create takes a whole lot less time! We added a connection to a MySQL database in a matter of a few hours. I hope this article will help you get your Oracle database talking to other data sources.

Posted by rossm at 8:17 AM | Comments (0)

July 22, 2009

Connecting Oracle 10g on Solaris Sparc 64 to SQL Server on Windows using FREE ODBC Driver

Well, a new twist has been added to our production Oracle database. It became necessary to connect our Oracle 10gR2 database up to a Microsoft SQL Server database. The goal was to be able to create a database link in our Oracle database and directly fetch data from the SQL Server database. As we worked through this process we learned that connecting to a "remote" server (that is, each database is on a different server) is a complicating factor. Other complications included: Oracle database is on Solaris 9 - 64 bit. SQL Server database is on a 32 bit Windows OS. The final complicating factor - we needed to use free/open source software - no budget for buying a commercial solution.

Here are the pieces you need to make this work. (Finding this out required a lot of time!)


What we found was that a lot of people have tried to make these connections, and that the documentation was either incomplete or very confusing. Much of the confusion came from understanding when to use 64 bit libraries and when to use 32 bit libraries. Jose at Oracle helped a lot (SR). He modified the listener.ora file to tell the Oracle database to use 32 bit libraries. Once this was in place, then everything else used 32 bit libraries even though it was installed on a 64 bit OS. The 32 bit libraries work just fine on the 64 bit Solaris 9 OS we were using.

I can't say too much about the Windows ODBC setup, except to say it was done pretty quickly on the Windows server, and there was a good amount of supporting documentation that was accurate enough to get that job done. This was set up by Mike Huber and Brian Berkley (our Windows sysadmin). All of our challenges came on the unix/Solaris side.

After installing the odbc driver manager obtained from unixodbc.org, the next step was to configure this to connect with the windows ODBC driver. This should all be using 32 bit libraries (I know this now...). We found that adding freeTDS into the unixodbc driver was necessary to actually make our systems talk to each other. It may be that the freetds portion is not always necessary, but it was fairly easy to install and configure, and we could not get any connectivity without it. Dave Diemer, our unix sysadmin, installed the odbcdriver manager, and freetds for us - this must be done as root. These things want to install in the /usr/bin path, but Dave forced them into a different path in our Oracle area. Dave used a command like this:

It was quite helpful keeping all of this within our Oracle area.

The unixodbc driver uses two config files - odbc.ini and odbcinst.ini
Here's what our files look like:

odbc.ini -

[clmd]
Driver = TDS
Description = clm development
Trace = Yes
TraceFile = /[oracle path]/odbc/tmp/clmd.log
#Servername = winchester.wou.edu
Server = ip.address.of.your.server
Database = [name of database you want to connect to]
Port = 1433

Things to note about this file:
[clmd] - clmd is a common name we used throughout the setup. At first we had CLM_Development. Don't use such a long name, it can cause problems. Just pick something simple. We had to eventually go back through all of our setup and change to the simpler name.
Server = Use the ip address, at least initially. Even though the unixodbc.org instructions say to use "Servername" don't do it! It must say "Server = "
Database = This is the name of the database you want to connect to.
Port = 1433 is the standard port for this sort of thing.

odbcinst.ini -

[TDS]
Description = FreeTDS driver
Driver = /[oracle path]/odbc/lib/libtdsodbc.so
Setup = /[oracle path]/odbc/lib/libtdsodbc.so
Trace = Yes
TraceFile = /[oracle path]/odbc/tmp/freetds.log
FileUsage = 1

Our driver and setup entries refer to the freetds libraries. If you are not using freetds, then these entries point to the odbc libraries for your situation. The libraries come in pairs, for example the MySQL libraries are named libmyodbc3.so and libmyodbc3S.so (The Setup library has the extra "S", the other library is the Driver.)

freetds.conf -


# $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[global]
# TDS protocol version
; tds version = 4.2

# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff

# Command and connection timeouts
; timeout = 10
; connect timeout = 10

# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
text size = 64512

# A typical Sybase server
[egServer50]
host = symachine.domain.com
port = 5000
tds version = 5.0

# A typical Microsoft server
[clmd]
host = your.server.ip.address
port = 1433
tds version = 8.0

The last section is really the only thing we had to configure in this file.

At this point, you should try connecting to the remote database using the odbc drivers you just configured and installed. There is a nifty utility for doing this called "isql". Don't let the name throw you, it is just a utility that comes with the unixodbc install. It is located in the /[oracle path]/odbc/bin folder. You call it like this:

isql -v tds(our situation, use your odbc.ini name) useridtodatabase password;

A successful connection returns a box, praises you for your success and opens a sql session with the remote database. You can then do an appropriate select and it will display the data in a cryptic, command line sort of way. Here's an example:

isql -v clmd sqlserveruserid password
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from school_codes;
+---------------+------------+-------------+-------+---------------------------------------------------+-------------------------------+-------------------+-------------------------------+-------------------------------+-------------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+-------------------------+
| SCHOOL_LINK_ID| SCHOOL_CODE| OPEID | CAMPUS| NAME | BANNER_IMAGE | MULTIPLE_ADDRESSES| MAIN_CONTACT | TITLE | ARCHIVE_FLAG| BUTTON_IMAGE | BILL_TEXT | CLOSE_AMT | LONG_AMT | INCLUDE_IN_CLEARINGHOUSE|
+---------------+------------+-------------+-------+---------------------------------------------------+-------------------------------+-------------------+-------------------------------+-------------------------------+-------------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+-------------------------+
| 1 | WOU | 00320900 | WO | Western Oregon University | wou.jpg | | Dona Vasas | | N | | | | | N |
+---------------+------------+-------------+-------+---------------------------------------------------+-------------------------------+-------------------+-------------------------------+-------------------------------+-------------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+-------------------------+
SQLRowCount returns 1
1 rows fetched
SQL>

At this point you know you have the unix odbc driver and everything on your Windows box configured properly. This is a long process, so I'm going to quit right here. Next time, we'll look at the remaining part - getting the Oracle database to make this connection directly.

One final comment. To make this work, it took quite a number of people from the UCS Department here at WOU. Mike Huber, who is a Banner programmer that needs this functionality. Mike helped in many ways including finding unixodbc.org, freetds, and providing details in the various configuration files. Brian Berkley helped set up the Windows box and database to accept a remote connection from our Oracle database. Dave Diemer did the actual installs and provided the unix guru advice to put the pieces together. Oracle support - Jose Franco. Took our setup, analyzed it, pointed out where we had gone wrong, and actually made it work. Opening an SR (tar) for this was definitely the way to go! There are a lot of pieces that need to fit together just right in order for this to work.

Posted by rossm at 1:09 PM | Comments (0)