Getting both Teradata ODBC and Aster ODBC working together with the pyodbc Python adapter

Connectivity

Getting both Teradata ODBC and Aster ODBC working together with the pyodbc Python adapter

Greetings,

I'm generally more of a forum lurker, but every once in a while I get something working that doesn't readily work out of the box the way you'd expect and feel that its worth posting about to ease the path of others (and maybe leave a trail for Google to scan so I don't have to remember where I left my notes!)

My little victory dance today was getting the ODBC adapters for Aster and Teradata to both co-exist in the same process with pyodbc, the Python ODBC adapter.  It took more doing than it should have, so thats why I'm writing it up.

My test system was Ubuntu Linux 13.04 -- a very recent Ubuntu build -- running a 64 bit Linux.

I had to install the "rpm" module for Ubuntu to cope with the tdodbc RPMs, but that's not a big deal.  The teradata ODBC adapters are in their typical home of

/opt/teradata/client/14.10/odbc_64/lib

and

/opt/teradata/client/14.10/tdicu/lib64

and to make things similar, I dropped the Aster ODBC drivers into /opt/aster, i.e. the two drivers are

/opt/aster/stage/clients-odbc-linux64/Libs

and

/opt/aster/stage/clients-odbc-linux64/DataDirect/lib

Python is the stock python and I used the Ubuntu package manager to install pyodbc.

Now -- heres the interesting stuff.  The Teradata installer will make symlinks to libodbc.so and libodbcinst.so in /usr/lib64.  Fortunately for me, /usr/lib64 is not a standard library path on Ubuntu so I could easily see what was going on.  The Teradata version of the DataDirect odbc driver manager will not properly load the Aster version of the adapter, so you have to switch out the symlinks in /usr/lib64.  First, you have to make libodbc.so and libodbcinst.so use the Aster versions, and you also need to make a convenience link of libodbc.so.1 for pydobc itself.   If there are existing libodbc* in /usr/lib64 (or, for me, /lib/x86_64-linux-gnu) you need to get those out of the way -- I suggest renaming them with a capital X in front so they aren't found but you can easily put them back in case you oops something.

You also have to symlink the lidddicu27.so from Aster into your lib directory.  Here's the symlinks I ended up with when I was done (note that the teradata odbc installer did most of these links).

lrwxrwxrwx root/root         0 2013-11-06 14:17 usr/lib64/libodbc.so.1 -> libodbc.so
lrwxrwxrwx root/root 0 2013-11-06 14:02 usr/lib64/libodbc.so -> /opt/aster/stage/clients-odbc-linux64/DataDirect/lib/libodbc.so
lrwxrwxrwx root/root 0 2013-11-06 14:02 usr/lib64/libodbcinst.so -> /opt/aster/stage/clients-odbc-linux64/DataDirect/lib/libodbcinst.so
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libiculxtd.so -> /opt/teradata/client/14.10/tdicu/lib64/libiculxtd.so.46.0
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libicui18ntd.so -> /opt/teradata/client/14.10/tdicu/lib64/libicui18ntd.so.46.0
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libicudatatd.so.46 -> /opt/teradata/client/14.10/tdicu/lib64/libicudatatd.so.46.0
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libiculetd.so -> /opt/teradata/client/14.10/tdicu/lib64/libiculetd.so.46.0
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libtdsso.so -> /opt/teradata/client/ODBC_64/lib/libtdsso.so
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libicudatatd.so -> /opt/teradata/client/14.10/tdicu/lib64/libicudatatd.so.46.0
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libtdparse.so -> /opt/teradata/client/ODBC_64/lib/libtdparse.so
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libicuuctd.so -> /opt/teradata/client/14.10/tdicu/lib64/libicuuctd.so.46.0
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libicuuctd.so.46 -> /opt/teradata/client/14.10/tdicu/lib64/libicuuctd.so.46.0
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libicui18ntd.so.46 -> /opt/teradata/client/14.10/tdicu/lib64/libicui18ntd.so.46.0
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libicuiotd.so -> /opt/teradata/client/14.10/tdicu/lib64/libicuiotd.so.46.0
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libicuiotd.so.46 -> /opt/teradata/client/14.10/tdicu/lib64/libicuiotd.so.46.0
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libddicu26.so -> /opt/teradata/client/ODBC_64/lib/libddicu26.so
lrwxrwxrwx root/root 0 2013-11-06 14:07 usr/lib64/libddicu27.so -> /opt/aster/stage/clients-odbc-linux64/DataDirect/lib/libddicu27.so
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libiculetd.so.46 -> /opt/teradata/client/14.10/tdicu/lib64/libiculetd.so.46.0
lrwxrwxrwx root/root 0 2013-11-06 10:29 usr/lib64/libiculxtd.so.46 -> /opt/teradata/client/14.10/tdicu/lib64/libiculxtd.so.46.0

Now, as it happens you need to make sure your system is reading the related libraries provided... and most systems nowadays will use /etc/ld.so.conf.d to have simple files listing your system libraries.  We add the Aster and Teradata libraries by creating /etc/ld.so.conf.d/Zteradata.conf (named with a capital Z so it comes at the end of the default search path) with the following entries:

/opt/aster/stage/clients-odbc-linux64/Libs
/opt/aster/stage/clients-odbc-linux64/DataDirect/lib
/opt/teradata/client/14.10/odbc_64/lib
/opt/teradata/client/14.10/tdicu/lib64

The aster ODBC driver installation instructions tell you to set the DriverManagerEncoding to UTF-32, but I found if I did that it would cause a segfault, and UTF-8 managed to turn everything from Aster into Chinese on me, but fortunately UTF-16 did the trick.

Here is my /etc/aster.ini file:

[driver]
DriverManagerEncoding=UTF-16
LogLevel=0
DriverLocale=en-US
ErrorMessagesPath=/opt/aster/stage/clients-odbc-linux64/ErrorMessages
ODBCInstLib=/opt/aster/stage/clients-odbc-linux64/DataDirect/lib/libodbcinst.so

For completeness, here are my odbc.ini and odbcinst.ini files, respectively:

[ODBC]
InstallDir=/opt/teradata/client/14.10/odbc_64
Trace=0
;TraceDll=/opt/teradata/client/14.10/odbc_64/lib/odbctrac.so
;TraceFile=/usr/joe/odbcusr/trace.log
TraceAutoStop=0

[ODBC Data Sources]
TDATA=tdata.so
Aster=AsterDriver

[ASTER]
Driver=/opt/aster/stage/clients-odbc-linux64/DataDirect/lib/libAsterDriver.so
SERVER=aster.mydomain
DATABASE=beehive

[TDATA]
Driver=/opt/teradata/client/14.10/odbc_64/lib/tdata.so
Description=Teradata database
DBCName=teradata.mydomain
LastUser=
Username=
Password=
Database=
DefaultDatabase=

[ODBC DRIVERS]
Teradata=Installed
AsterDriver=Installed

[ODBC Translators]
OEM to ANSI=Installed

[AsterDriver]
Driver=/opt/aster/stage/clients-odbc-linux64/DataDirect/lib/libAsterDriver.so
IconvEncoding=UCS-4LE

[Teradata]
Driver=/opt/teradata/client/14.10/odbc_64/lib/tdata.so
APILevel=CORE
ConnectFunctions=YYY
DriverODBCVer=3.51
SQLLevel=1

Now -- after making the entries in /etc/ld.so.conf.d, and getting all the symlinks made, you have to remember to run

ldconfig -v

(well, OK, maybe you don't need -v but I always do that) to refresh ld.so's cache.

After that, voila!  Python is able to talk to both Teradata and Aster simultaneously!

Here's my test program:

#!/usr/bin/env python

import pyodbc
pyodbc.pooling = False

def Tables(conn):
c = conn.cursor()
tables = c.tables()
rs = tables.fetchall()

for r in rs:
print "%s.%s" % (r[1], r[2])

tdata = pyodbc.connect('DSN=TDATA;UID=matt;PWD=********')
aster = pyodbc.connect('DSN=Aster;UID=matt;PWD=********')

print "Teradata tables"
Tables(tdata)

print "Aster tables"
Tables(aster)

Except of course I blanked out my password :)

Hopefully this helps both of you similarly situated users whom I've never met use both libraries simultanously! (OK, maybe there are three of you, but it isn't a big number!)

Also -- one last thing -- if you were previously using LD_LIBRARY_PATH to set the path to your libraries -- you should get rid of that, since the /etc/ld.so.conf.d/Zteradata.conf file now sets that for all users of your system.  You have to make sure the aster Libs load after the normal libc libraries because they include a rude version of the c++ runtime which will make ld cry out a warning which will annoy the heck out of you.

4 REPLIES

Re: Getting both Teradata ODBC and Aster ODBC working together with the pyodbc Python adapter

One thing I forgot to mention above; on Ubuntu, I also added /usr/lib64 to a /etc/ld.so.conf.d file (I probably called it lib64.conf or something obvious like that) so that the symlinked libraries were picked up.  The only real reason for this is to make sure that the right version of libodbc.so and libodbcinst.so (and their dependent libraries) are picked up first.  In a more ideal world the libraries would be versioned so that multiple versions could coexist side by side but that isn't the case here.

<p>

Also note, the forums are mangling some of the examples a bit, there are not supposed to be spaces in most of those config files, and I've evidently not got the hang of how to do paragraph breaks here yet.

<p>

Prior to getting this working I was left with using LD_PRELOAD to load the todbc version of libodbc.so rather than the default system version and so I couldn't get the Aster and Teradata adapters concurrently connected with pyodbc.  Trying to figure out what was happening by setting LD_DEBUG=all and watching the loader look for symbols that weren't in the libraries as drving me nuts, so I ended up doing a clean install in a new VM and now I'm much happier.

<p>

If, by chance, someone actually has native python adapters rather than pyodbc wrapped ODBC adapters I'd be delighted to hear about them.

Enthusiast

Re: Getting both Teradata ODBC and Aster ODBC working together with the pyodbc Python adapter

Great,  thanks very much for sharing.  I don't have a linux system to test this on, but it will be a great reference for when I do !

Enthusiast

Re: Getting both Teradata ODBC and Aster ODBC working together with the pyodbc Python adapter

I am still getting the below error : 

pyodbc.Error: ('HY000', '[HY000] [Teradata][ODBC Teradata Driver] Major Status=0x04bd Minor Status=0x20800002-[terasso]Cannot load TDGSS library. (0) (SQLDriverConnect)')

Could somebody point where i must be going wrong?

Re: Getting both Teradata ODBC and Aster ODBC working together with the pyodbc Python adapter

I'm also getting 

teradata.api.DatabaseError: (0, u'[HY000] [Teradata][ODBC Teradata Driver] Major Status=0x04bd Minor Status=0x20800002-[terasso]Cannot load TDGSS library.')

Any help?