Page 1 of 1
MySQL ODBC Connection Issue - "SQLSTATE = IM003"
Posted: Mon Jan 05, 2015 9:11 am
by sohasaid
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.
Posted: Mon Jan 05, 2015 3:28 pm
by ray.wurlod
Are these entries identical to those on the production server?
Posted: Tue Jan 06, 2015 7:49 pm
by sohasaid
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?
Posted: Wed Jan 07, 2015 2:15 am
by ray.wurlod
No, projects can be located anywhere except the root directory.
Have you compared the files? I don't really have time at the moment.
Posted: Wed Jan 07, 2015 7:41 am
by sohasaid
ray.wurlod wrote:Have you compared the files?
Yes I did but I didn't capture any difference.
Posted: Wed Jan 07, 2015 8:39 am
by qt_ky
Double check the driver file name; it doesn't seem to match the instructions.
Posted: Wed Jan 07, 2015 10:17 am
by sohasaid
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'
Posted: Wed Jan 07, 2015 8:23 pm
by qt_ky
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.
Posted: Sun Jan 11, 2015 6:28 am
by sohasaid
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:
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
Posted: Sun Jan 11, 2015 10:06 am
by chulett
Awesome... thanks for posting that.