SAP IQ and SQL Anywhere UnixODBC UserDSN and SystemDSN issues: WORKAROUND

SAP’s IQ / SQLAnywhere ODBC driver on Linux/*nix doesn’t work with a system that has both UserDSNs (~/.odbc.ini) and SystemDSNs (/etc/odbc.ini).

Currently when the driver looks for a DSN (db instance), the resolution is:

  1. $ODBCINI – must contain the exact full path name of the .odbc.ini file.
  2. $HOME
  3. Current directory
  4. $PATH

Notice that /etc/odbc.ini is not processed. A workaround to use SystemDSNs is to set the ODBCINI variable to /etc/odbc.ini but it doesn’t help if you have a mix of SystemDSNs and UserDSNs.

I’ve requested SAP to fix this. They’ve opened KBA2571931 CR812127. Unfortunately, SAP requires a support contract to view the KBA 🙁

CR812127 order of resolution:

  1. $ODBCINI – must contain the exact full path name of the .odbc.ini file.
  2. $HOME
  3. Current directory
  4. $PATH
  5. /etc/odbc.ini
Share Button

HOWTO: IBM DB2, ODBC, and Linux (*nix) configuration

The DB2 ODBC driver works like anything else IBM. Nonstandard to the rest of the universe. How to set up ODBC:

Unix admin needs to do:
Retrieve the config file locations:

$ odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/jason/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

/etc/odbcinst.ini : list of the available drivers and default configuration for each driver
/etc/odbc.ini : list of the instances/databases for *all* users aka “System DSNs”
${HOME}/.odbc.ini : list of the instances/databases specific to *you* aka “User DSNs”

Add/update the following lines in the /etc/odbcinst.ini file (or wherever the file is as reported by odbcinst -j). Make sure there is no whitespace at the beginnings of the lines.

[DB2]
Description = DB2 Driver
Driver = /opt/ibm/db2/db2_v9.7/lib64/libdb2o.so
fileusage=1
dontdlclose=1

The driver line should point to the full path of libdb2o.so (any *nix but aix) or libdb2o.a (aix).

Add/update the following lines in the ${HOME}/.odbc.ini file:

[MYDB2DB]
Driver=DB2

You need to catalog the database (assuming you already cataloged the node [db2host in my case] – see https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001944.html):

 # catalog database database_name as database_alias at node node_name
catalog database MYDB2DB as MYDB2DB at node db2host

Test connectivity to the remote database:

db2 connect to MYDB2DB user testuser using test_password

Ensure we’re using the unixODBC isql:

$ /usr/bin/isql --version
unixODBC 2.2.14

Validate using unixodbc “isql”:

$ /usr/bin/isql -v MYDB2DB testuser test_password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

The DSN for the entry is simply “dbi:ODBC:MYDB2DB” if you’re using Perl. Later versions of the db2 client allow you to specify the connect options in the DSN like so:

my $odbc_connect_str = "dbi:ODBC:"
               . "DRIVER=/opt/ibm/db2_v10.5/lib64/libdb2o.so;
               . "Protocol=tcpip;"
               . "Hostname=mydbhost;"
               . "Port=67033;"
               . "DATABASE=MYDB2DB";
Share Button

How to install Sybase’s ODBC driver on Ubuntu Linux for ASE/IQ/Replication Server/SQL Anywhere/etc

ASE 15.0.3 full install on Linux 32bit.
First we need to install unixODBC:

% sudo aptitude install unixodbc unixodbc-bin unixodbc-dev libstdc++5

Because of a bug within the Sybase ODBC install, we need to change /opt/sybase/DataAccess/ODBC/samples/drivertemplate.txt.

/opt/sybase/DataAccess/ODBC/samples/drivertemplate.txt:

[Adaptive Server Enterprise]
Description = Sybase ODBC Driver
Driver = /opt/sybase/ODBC-12_5/lib/libsybdrvodb.so
FileUsage = -1

Change to:

[Adaptive Server Enterprise]
Description = Sybase ODBC Driver
Driver = /opt/sybase/DataAccess/ODBC/lib/libsybdrvodb.so
FileUsage = -1

This template file is used to install the ODBC driver with unixODBC:

odbcinst -i -d -f /opt/sybase/DataAccess/ODBC/samples/drivertemplate.txt

Currently Sybase’s ODBC driver does not have a GUI component for unixODBC. I’m waiting on the bug #. Once I have it, I’ll update this post.

Now we are ready to define an ODBC dsn. We will need to create a template file to do so:

dbadev1.dsn

[dbadev1]
Description=Sybase ODBC Data Source
UserID=sa
Password=password
Driver=Adaptive Server Enterprise
Server=dba-dev1
Port=5000
Database=sybase_dba
UseCursor=1

To add it to unixODBC we need to run:

odbcinst -i -s -f dbadev1.dsn

If you wanted a system wide dsn:

sudo odbcinst -i -s -l -f dbadev1.dsn
Updated to include libstdc++5 for the newer releases of Ubuntu
Share Button