We use the Linux version of Cognos 8 and need to connect to a MySQL DB for reporting. Cognos doesn't have a native connector to MySQL, so I've been told by Cognos support that I need to establish a 'Generic ODBC' connection.
The MySQL website has a Linux ODBC driver available for download - which I'm hoping to install on our Cognos machine.
Obviously this is different to doing the same thing on a Windows machine.
Has anyone got any experience doing this sort of thing? Does it work?
This topic has been locked.
7 replies Latest Post - 2010-04-03T23:03:35Z by SystemAdmin
Pinned topic Generic ODBC / MySQL...
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2010-04-03T23:03:35Z at 2010-04-03T23:03:35Z by SystemAdmin
Re: Generic ODBC / MySQL...2009-10-16T12:20:21Z in response to mgibsonHi,
We have successfully connected to MySQL using the generic ODBC driver option on CentOS 5.3 64bit.
You need unixODBC and the 3.51.19 version of the MySQL driver (we have had the best results with this version). The 3.51 driver might not work with some of the more recent MySQL versions.
Driver and manager installation on CentOS:
yum install unixODBC.x86_64
rpm -i mysql-connector-odbc-3.51.19-0.x86_64.rpm
Note: Check that the unixODBC library is properly symlinked. You should have /usr/lib/libodbcinst.so link to the latest libodbcinst.so version. This was not the case after our installation.
DRIVER = /usr/lib/libmyodbc3.so
UsageCount = 1
Driver = MySQL
SERVER = server IP
PORT = 3306
DATABASE = dbname
USER = username
PASSWORD = password
Driver manager setup commands:
odbcinst -i -d -f /etc/odbcinst.ini
odbcinst -i -s -l -f /etc/odbc.ini
odbcinst -s -q
# Outputs: [datasourcename]
isql -v datasourcename username password
# Starts a console
Please note that unlike these instructions, we have installed the 32bit versions, but you probably should try the 64bit versions first.
I would be happy to hear about success stories and performance comparisons between this solution and for instance MySQL with Virtual View Manager.
Re: Generic ODBC / MySQL...2009-11-03T22:54:37Z in response to irfuserHi irfuser,
We've been trying on and off for the last few weeks to get this to work, but have had no luck.
After getting some help from another forum, I was finally able to get Framework Manager to 'see' the DB objects by changing some of the options on the client odbc config for this data source.
But we still can't get the server install on linux to return data. I get various errors when trying.
I was curious to know whether you had to set the 'OPTION' parameter in the odbc.ini file to get yours to work?
Re: Generic ODBC / MySQL...2009-11-25T10:54:17Z in response to mgibsonHi Mick,
We have currently no options set in the odbc.ini file. I am however experiencing problems with native query subject on the server side, so some might be required.
If you can, please post what you learned from the other forum, since it might help us fix our problem.
I'm sorry I can't be of more help to you.
PS. Looks like I wrote "lib/" on my first post when I should have wrote "lib64"...
Re: Generic ODBC / MySQL...2009-11-25T13:52:27Z in response to irfuserI fixed the native query problem. My Framework Manager workstation's ODBC datasource has the Include table name in SQLDescribeCol set, so I had to do the same on the server. I just added OPTION = 1024 to odbc.ini under my datasource.
Some additional reference:
Post suggesting flags for Cognos MySQL ODBC: https://www.ibm.com/developerworks/forums/message.jspa?messageID=14309209#14309209
MySQL flags: http://dev.mysql.com/doc/refman/4.1/en/connector-odbc-configuration-connection-parameters.html
If you want more than one flag, just add the values together. So if we want option 16 and option 32: OPTION = 48
Re: Generic ODBC / MySQL...2009-11-25T22:42:35Z in response to irfuserThanks for the post. I have already tried several combinations of flags, which has enabled the data source to be viewable within Framework Manager, but I'm still getting errors when using it in any of the C8 studios.
I might try the same settings as yourself and see if they work, but it may not seeing we have the 5.1 drivers.
Re: Generic ODBC / MySQL...2010-01-05T18:52:19Z in response to mgibsonMinor update, which I hope will help people having problems with special characters:
I added CHARSET=utf8 to the data source configuration, which solved the problem of displaying special characters (umlauts) from a latin1 encoded table on a UTF-8 locale server.
SystemAdmin 110000D4XK15496 Posts