kcaran.com

Installing ODBC on Linux

At work, we use Microsoft SQL Server and IBM AS400 databases. Here’s how I set our Linux boxes to allow them to connect to the databases through ODBC. There are seperate instructions for the Debian and RedHat distributions.

ODBC connections require several layers of software to work. The bottom layer consists of the individual ODBC drivers for each database system. Our top layer is the DBI/DBD interface for Perl. In between these layers is the ODBC driver manager, which keeps track of the DSN’s and their corresponding ODBC drivers.

Packages and Software to Install

Debian Version (“unstable” distribution)

We use unixODBC as our ODBC driver manager. First, install unixODBC:
NOTE: Perl’s DBD::ODBC module requires the developer’s version of unixODBC.

  # apt-get install unixodbc-dev

Finding an ODBC driver for SQL Server was a challenge, since Microsoft refuses to directly support Linux. Fortunately, freeTDS (version 0.61 or later) seems to work fine.

  # apt-get install tdsodbc

NOTE: Originally, I installed the libsybdb3 package to get freetds. But somewhere along the road, libsybdb3 was replaced with libsybd5. When I upgraded my Debian distribution, freetds was uninstalled in the process! Hopefully, the tdsobdc package won’t suffer from name changes.

The ODBC driver to the AS400 comes directly from IBM: http://www-1.ibm.com/servers/eserver/iseries/linux/odbc/

  # apt-get install rpm
  # apt-get install alien
  # alien -i iSeriesODBC-5.1.0-0.16.i386.rpm
  # ln -s /opt/ibm/iSeriesODBC/lib/libcwb* /usr/lib

RedHat Version

We are using unixODBC as our ODBC driver manager. Version 2.2.3-6 of unixODBC comes with the RedHat 9.0 installation, but we need the developer’s version in order for Perl’s DBD::ODBC module to work.

Using http://www.rpmseek.com/, find, download, and install the unixODBC-devel-2.2.3-6.i386.rpm package.

Finding an ODBC driver for SQL Server was a challenge, since Microsoft refuses to directly support Linux. Fortunately, freeTDS version 0.61 seems to work fine. Under RedHat, we needed to compile the application.

1. Download the source code from http://www.freetds.org/
2. Uncompress the gzipped tar file:

   # gzip -cd freetds-0.61.tgz | tar xf -
   

3. Enter the freetds directory and configure, make, and install:

   # cd freetds-0.61
   # configure --with-tdsver=7.0 --with-unixodbc=/usr/include
   # make
   # make install
   

The ODBC driver to the AS400 comes directly from IBM: http://www-1.ibm.com/servers/eserver/iseries/linux/odbc/

Configuration

1. Add SQL Server hosts to the FreeTDS configuration file:
   /etc/freetds/freetds.conf (Debian)
   /usr/local/etc/freetds.conf (Redhat)

   [TDSproduction]
           host = 10.28.78.52
           port = 1433
           tds version = 7.0
   

2. Confirm the new drivers are in the driver config file:
   /etc/odbcinst.ini (Debian and Redhat)

   [FreeTDS]
   Description     = MS SQL driver
   Driver          = /usr/local/lib/libtdsodbc.so
   FileUsage       = 2
   
   [ODBC]
   Trace           = No       ;(=Yes if tracing using unixODBC)
   
   [iSeries Access ODBC Driver]
   Description     = iSeries Access for Linux ODBC Driver
   Driver          = /opt/ibm/iSeriesODBC/lib/libcwbodbc.so
   Setup           = /opt/ibm/iSeriesODBC/lib/libcwbodbc.so
   Threading       = 2
   FileUsage       = 1
   

3. Add servers to the server file:
   /etc/odbc.ini (Debian and Redhat)
   /usr/local/etc/odbc.ini (Redhat).

   [Production]
   Driver                  = FreeTDS
   Description             = Production MS SQL Database
   Servername              = TDSproduction
   Database                = AVC
   UID                     = content1_badsg-1
   
   [AS400]
   Driver                  = iSeries Access ODBC Driver
   Description             = Production AS/400 Database
   Servername              = AS400.APPN.SNA.IBM.COM
   System                  = AS400.APPN.SNA.IBM.COM
   DefaultLibraries        = "TESTMS"
   UID                     = webodbc
   

Test the Connections

1. Verify unixODBC setup using odbcinst:

   # odbcinst -q -d
   	[FreeTDS]
   	[iSeries Access ODBC Driver]
   
   # odbcinst -q -s
   	[Production]
   	[AS400]
   

2. Test connection to FreeTDS servers:

   # /usr/local/bin/tsql -STDSproduction -Usa
   

3. Test connections through unixODBC:

   # isql AS400 username PASSWORD
   

4. Debugging tools for connecting to AS400:

   # /opt/ibm/iSeriesODBC/bin/cwbping as400.appn.sna.ibm.com
   

Installing DBD::ODBC module for Perl

# cd
# perl -MCPAN -eshell
# get DBD::ODBC
<>quit
# tcsh
# cd .cpan/build/DBD-ODBC-1.09
# setenv DBI_DSN dbi:ODBC:LocalServer
# setenv DBI_USER sa
# setenv DBI_PASS sa
# setenv ODBCHOME /usr
# setenv LANG en_US
# perl Makefile.PL
# make
# make test
# make install

Last Updated: Thu, 17 Nov 2005