MySQL ODBC Connection Issue - "SQLSTATE = IM003"

sohasaid · Post by **sohasaid** » Mon Jan 05, 2015 9:11 am

Dears,

I'm trying to configure MySql community edition on DataStage server. I'm using this version 'mysql-connector-odbc-5.3.4-linux-el6-x86-64bit.tar.gz '.

The configuration went smooth at the production server but It's not working at Dev server. I've followed the same steps mentioned in the below URL at both servers:

http://www-01.ibm.com/support/docview.w ... wg21458603

When trying to view data from DataStage Designer, I receive this error:

Code: Select all

ODBC function "SQLConnect" reported: SQLSTATE = IM003: Native Error Code = 0; Msg = [DataDirect][ODBC lib] Specified driver could not be loaded

.odbc.ini file data source entry is:

Code: Select all

[MySQL]
Driver=/opt/mysql/lib/libmyodbc5a.so
Description=Connector/ODBC 3.51 Driver DSN
SERVER=9.133.16.210
PORT=3306
USER=8
Password=8
Database=NEWSMS
OPTION=3
SOCKET=
DriverUnicodeType=1

dsenv file:

Code: Select all

LD_LIBRARY_PATH=`dirname $DSHOME`/branded_odbc/lib:`dirname $DSHOME`/DSComponents/lib:`dirname $DSHOME`/DSComponents/bin:$DSHOME/lib:$DSHOME/uvdlls:`dirname $DSHOME`/PXEngine/lib:$ASBHOME/apps/jre/bin:$ASBHOME/apps/jre/bin/classic:$ASBHOME/lib/cpp:$ASBHOME/apps/proxy/cpp/linux-all-x86_64:$LD_LIBRARY_PATH
	export LD_LIBRARY_PATH

	## Netezza Home and ODBC Configuration ##
	Netezza_Home=/opt/odbc64v51/lib
	NZ_ODBC_INI_PATH=/opt/IBM/InformationServer/Server/DSEngine
	LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$Netezza_Home
	export LD_LIBRARY_PATH
	export NZ_ODBC_INI_PATH
	
	LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/mysql/lib;	export LD_LIBRARY_PATH
fi

uvodbc.config file at the project level:

Code: Select all

[ODBC DATA SOURCES]
<localuv>
DBMSTYPE = UNIVERSE
network = TCP/IP
service = uvserver
host = localhost

<MySQL>
DBMSTYPE = ODBC

Thanks in advance.

ray.wurlod · Post by **ray.wurlod** » Mon Jan 05, 2015 3:28 pm

Are these entries identical to those on the production server?

sohasaid · Post by **sohasaid** » Tue Jan 06, 2015 7:49 pm

Thanks Ray, here's the values at production server for the same files:

.odbc.ini file data source entry is:

Code: Select all

[MySQL]
Driver=/opt/mysql/lib/libmyodbc5a.so
Description=Connector/ODBC 3.51 Driver DSN
SERVER=10.10.4.107
PORT=3306
USER=<>
Password=<>
Database=NEWSMS
OPTION=3
SOCKET=
DriverUnicodeType=1

dsenv file:

Code: Select all

if [ -n "$DSHOME" ] && [ -d "$DSHOME" ]
then
	ODBCINI=$DSHOME/.odbc.ini; export ODBCINI
	HOME=${HOME:-/}; export HOME
	
	#LANG="<langdef>";export LANG
	#LC_ALL="<langdef>";export LC_ALL
	#LC_CTYPE="<langdef>";export LC_CTYPE
	#LC_COLLATE="<langdef>";export LC_COLLATE
	#LC_MONETARY="<langdef>";export LC_MONETARY
	#LC_NUMERIC="<langdef>";export LC_NUMERIC
	#LC_TIME="<langdef>";export LC_TIME
	#LC_MESSAGES="<langdef>"; export LC_MESSAGES
	
	LD_LIBRARY_PATH=`dirname $DSHOME`/branded_odbc/lib:`dirname $DSHOME`/DSComponents/lib:`dirname $DSHOME`/DSComponents/bin:$DSHOME/lib:$DSHOME/uvdlls:`dirname $DSHOME`/PXEngine/lib:$ASBHOME/apps/jre/bin:$ASBHOME/apps/jre/bin/classic:$ASBHOME/lib/cpp:$ASBHOME/apps/proxy/cpp/linux-all-x86_64:$LD_LIBRARY_PATH
	export LD_LIBRARY_PATH

	# Add the netezza binaries and libraries. Must use the lib64 because DataStage Netezza Connector is 64bit.
	PATH=$PATH:/usr/local/nz/bin; export PATH
	LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/nz/lib64; export LD_LIBRARY_PATH
	LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/mysql/lib; export LD_LIBRARY_PATH
	NZ_ODBC_INI_PATH=$DSHOME; export NZ_ODBC_INI_PATH

fi

uvodbc.config file at the project level:

Code: Select all

[ODBC DATA SOURCES]
<localuv>
DBMSTYPE = UNIVERSE
network = TCP/IP
service = uvserver
host = localhost

<MySQL>
DBMSTYPE = ODBC

One information not sure it's useful or not, the project directory at prod server is not located under the default directory at '/opt/IBM/InformationServer/Server/Projects', but it's location at '/data/Projects/'

Is there any difference?

ray.wurlod · Post by **ray.wurlod** » Wed Jan 07, 2015 2:15 am

No, projects can be located anywhere except the root directory.

Have you compared the files? I don't really have time at the moment.

sohasaid · Post by **sohasaid** » Wed Jan 07, 2015 7:41 am

ray.wurlod wrote:Have you compared the files?

Yes I did but I didn't capture any difference.

qt_ky · Post by **qt_ky** » Wed Jan 07, 2015 8:39 am

Double check the driver file name; it doesn't seem to match the instructions.

sohasaid · Post by **sohasaid** » Wed Jan 07, 2015 10:17 am

The instructions say:

Code: Select all

[DataSourceName]
Driver=/opt/mysql_odbc/lib/libmyodbc5.so (put your own path and driver name in here)
Description=Connector/ODBC 3.51 Driver DSN

I believe I'm using the same driver '3.51'

qt_ky · Post by **qt_ky** » Wed Jan 07, 2015 8:23 pm

1. Does the downloaded driver file name actually contain an extra "a" that is not in the example from the instructions?

2. Are there any differences in file sizes, owners, groups, or permissions, as far as the mysql driver file(s) go?

3. Does your .odbc.ini header section have a line for the MySQL entry?

4. Do you get the same error when you test the connection from the command line? Look under the ....../branded_odbc/samples/example path for a program named "example" (exact location may vary). Source the dsenv file before you run the example program.

5. Have you tried running the ISA Lite General Health Checker? It can detect problems in the .odbc.ini. file.

sohasaid · Post by **sohasaid** » Sun Jan 11, 2015 6:28 am

Problem is solved. Thanks Eric & Ray for your support.

The main clue is testing the connection from the command line, it gave a different error which is the correct one.

Code: Select all

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

As per the instructions you will have to create a link to the shared library but first you need to run 'ldd' utility to ensure that the library is not working.
Command is:

Code: Select all

ldd /opt/mysql/lib/libmyodbc5a.so

Output is:

Code: Select all

linux-vdso.so.1 =>  (0x00007fffa7d56000)
        libodbcinst.so.2 => not found
       libpthread.so.0 => /lib64/libpthread.so.0 (0x00007feb134e3000)
        librt.so.1 => /lib64/librt.so.1 (0x00007feb132db000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007feb130d6000)
        libm.so.6 => /lib64/libm.so.6 (0x00007feb12e52000)
        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007feb12b4c000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007feb12935000)
        libc.so.6 => /lib64/libc.so.6 (0x00007feb125a1000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003e9d600000)

The output means that libodbcinst.so.2 is not defined successfully.

Run this command to create the link:
ln -s libodbcinst.so libodbcinst.so.2

After this step the issue should be resolved.

Steps to test your ODBC from command line:
1- Go to DSHOME directory: opt/IBM/InformationServer/Server/DSEngine/ (it might vary)
2- Source dsenv file using (. ./dsenv) command
3- Start the DataStage Server Shell using (./bin/dssh) command
4- Login to your Project (LOGTO <project_name>)
5- Test the connection using DS_CONNECT command.
Example, DS_CONNECT <DSN Name>,
where <DSN Name> is the same entry name at unvconfig file at the project level and odbc.ini at the DataStage level.
6- Login using your username and password.

In case no issues, you will receive this message:

Code: Select all

SQLConnect info-only:   Status = 1   SQLState = 00000   Natcode = 0

Test connection steps are quoted from this guide:
http://www-01.ibm.com/support/knowledge ... reODBC.pdf

Thanks

chulett · Post by **chulett** » Sun Jan 11, 2015 10:06 am

Awesome... thanks for posting that.