DataStage ODBC connectivity to MySQL Community Edition databases
Download
the open source MySQL ODBC Connector from MySQL.com. Example file
name for 32-bit linux platforms:
mysql-connector-odbc-5.1.6-linux-glibc2.3-x86-32bit.tar.gz
Extract
the file contents to a directory of your choice and make sure the
directory is added to the end
of
the load library path environment variable in your dsenv file.
Example for Linux:
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/mysql_odbc/lib/;
export LD_LIBRARY_PATH
After
modifying the dsenv file, you must stop and restart the DataStage
Engine.
Add
a DSN entry to your .odbc.ini file, located in $DSHOME. Note that
this example contains annotations for the various properties. You
must remove these annotations when updating your actual .odbc.ini
file.
[DataSourceName]
Driver=/opt/mysql_odbc/lib/libmyodbc5.so
(put your own path and driver name in here)
Description=Connector/ODBC
3.51 Driver DSN
SERVER=<<ServerName>>
PORT=3306
USER=
Password=
Database=<<DatabaseName>>
OPTION=3
SOCKET=
DriverUnicodeType=1
You
must also add the Data Source Name to the uvodbc.config file in your
Project directory. Example:
<DataSourceName>
DBMSTYPE
= ODBC
Test
the connection. If you see an error like this:
SQLConnect
error: Status = -1 SQLState = IM003 Natcode = 0 [ODBC]
[DataDirect][ODBC lib] Specified driver could not be loaded Invalid
parameter(s) found in configuration file
then
you may have to create a link to a shared library. Use the "ldd"
utility to check why the driver will not load:
$
ldd libmyodbc5.so
linux-gate.so.1
=> (0xffffe000)
libcrypt.so.1
=> /lib/libcrypt.so.1 (0xf7d15000)
libnsl.so.1
=> /lib/libnsl.so.1 (0xf7cfe000)
libm.so.6
=> /lib/libm.so.6 (0xf7cd6000)
libodbcinst.so.1
=> not found
libdl.so.2
=> /lib/libdl.so.2 (0xf7cd2000)
libpthread.so.0
=> /lib/libpthread.so.0 (0xf7cbb000)
libc.so.6
=> /lib/libc.so.6 (0xf7b78000)
/lib/ld-linux.so.2
(0x00981000)
In
this case, it could not find a library named libodbcinst.so.1 .
However, in branded_odbc/lib , we do have libodbcinst.so . Create a
link in branded_odbc/lib :
$
ln -s libodbcinst.so libodbcinst.so.1
$
ls -al libodbc*
-rwxr-xr-x
1 dsadm dstage 1101596 Nov 4 06:00 libodbcinst.so
lrwxrwxrwx
1 dsadm dstage 14 Dec 16 17:08 libodbcinst.so.1 -> libodbcinst.so