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

SAP Sybase IQ and ODBC (Linux/UNIX)

Connecting to SAP SAP_IQSybase’s IQ server shouldn’t be scary but the documentation leaves a lot to be desired. Log on to your favorite Linux or Unix box and make sure you source the IQ.sh file wherever you installed the software.

Let’s use the following hypothetical server:

IQ Server:  MyIQboxOfDoom
Port:  5000
Hostname: myiqboxofdoom

If you want a single odbc.ini file for all users, you’ll need to create the file and store it somewhere. Make sure you set the ODBCINI environment variable somewhere (NOT the IQ.sh file as it will but overwritten anytime you patch IQ) to the file. For example: export ODBCINI=/opt/databases/odbc.ini

If you want each user to have their own odbc.ini file, they will need to create the file named as ${HOME}/.odbc.ini The format will be the same as odbc.ini
odbc.ini / .odbc.ini contents:

[MyIQboxOfDoom]
CommLinks=tcpip(ip=myiqboxofdoom;port=5000;DOBROADCAST=NONE;VERIFY=NO)
AutoStop=no

Connecting is a simple matter of:

dbisql -c "dsn=MyIQboxOfDoom;uid=myuser;pwd=mypass" -nogui

Of course you could embed the user in the odbc.ini file like so:

[MyIQboxOfDoom]
CommLinks=tcpip(ip=myiqboxofdoom;port=5000;DOBROADCAST=NONE;VERIFY=NO)
AutoStop=no
uid=myuser
Share Button