IBM Support

DataStage ODBC connectivity to MySQL Community Edition databases

Technote (FAQ)


Question

How can DataStage ODBC be configured to connect to the Community edition (open source) MySQL databases which are not commercially licensed?

Answer

Information Server / DataStage distributions include an OEM installation of the DataDirect ODBC driver software. The bundled ODBC drivers include a MySQL Wire Protocol driver that can be used with commercially licensed versions of MySQL only (i.e. Enterprise edition). Due to commercial license restrictions, this DataDirect driver will not connect to the MySQL Community (open source, non-commercially licensed) version.

If you have a requirement to connect DataStage to the MySQL Community edition, it is possible for the end user to use the open-source ODBC driver with the DataDirect driver manager.

Important: This open-source driver is not supplied by IBM. The end user must obtain and install the driver. Though IBM permits this configuration, direct support of the open-source driver will not be provided by IBM.

The following instructions are provided as reference material only, for the Linux platform. End users may need to adapt these instructions according to their specific software version and/or platform. These instructions are known to work at the time of writing and are provided on an as-is basis, with no implied commitment from IBM that these instructions will work in all end user environments.

  1. 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

  2. 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.

  3. 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

  4. You must also add the Data Source Name to the uvodbc.config file in your Project directory. Example:

    <DataSourceName>
    DBMSTYPE = ODBC


  5. 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)


  6. 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

Document information

More support for: InfoSphere DataStage

Software version: 8.5, 8.7, 9.1, 9.1.2.0, 11.3, 11.5, 11.7

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Software edition: All Editions

Reference #: 1458603

Modified date: 19 January 2011