Topic
  • 7 replies
  • Latest Post - ‏2010-04-03T23:03:35Z by SystemAdmin
mgibson
mgibson
592 Posts

Pinned topic Generic ODBC / MySQL...

‏2009-09-07T01:17:34Z |
Hi,

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?

Regards
Mick
Updated on 2010-04-03T23:03:35Z at 2010-04-03T23:03:35Z by SystemAdmin
  • irfuser
    irfuser
    6 Posts

    Re: Generic ODBC / MySQL...

    ‏2009-10-16T12:20:21Z  
    Hi,

    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.

    Configuration:

    # /etc/odbcinit.ini
    [MySQL]
    DRIVER = /usr/lib/libmyodbc3.so
    UsageCount = 1

    # /etc/odbc.ini
    [datasourcename]
    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

    Testing:

    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.

    -Kurt
  • mgibson
    mgibson
    592 Posts

    Re: Generic ODBC / MySQL...

    ‏2009-11-03T22:54:37Z  
    • irfuser
    • ‏2009-10-16T12:20:21Z
    Hi,

    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.

    Configuration:

    # /etc/odbcinit.ini
    [MySQL]
    DRIVER = /usr/lib/libmyodbc3.so
    UsageCount = 1

    # /etc/odbc.ini
    [datasourcename]
    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

    Testing:

    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.

    -Kurt
    Hi 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?

    Regards
    Mick
  • irfuser
    irfuser
    6 Posts

    Re: Generic ODBC / MySQL...

    ‏2009-11-25T10:54:17Z  
    • mgibson
    • ‏2009-11-03T22:54:37Z
    Hi 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?

    Regards
    Mick
    Hi 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.

    -Kurt

    PS. Looks like I wrote "lib/" on my first post when I should have wrote "lib64"...
  • irfuser
    irfuser
    6 Posts

    Re: Generic ODBC / MySQL...

    ‏2009-11-25T13:52:27Z  
    • irfuser
    • ‏2009-11-25T10:54:17Z
    Hi 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.

    -Kurt

    PS. Looks like I wrote "lib/" on my first post when I should have wrote "lib64"...
    I 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

    -Kurt
  • mgibson
    mgibson
    592 Posts

    Re: Generic ODBC / MySQL...

    ‏2009-11-25T22:42:35Z  
    • irfuser
    • ‏2009-11-25T13:52:27Z
    I 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

    -Kurt
    Thanks 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.

    Regards
    Mick
  • irfuser
    irfuser
    6 Posts

    Re: Generic ODBC / MySQL...

    ‏2010-01-05T18:52:19Z  
    • mgibson
    • ‏2009-11-25T22:42:35Z
    Thanks 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.

    Regards
    Mick
    Minor 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.

    -Kurt
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Generic ODBC / MySQL...

    ‏2010-04-03T23:03:35Z  
    move to 8.4.1 which supports MySQL from Windows and Linux.
    ensure to have the current 5.x ODBC connector and do no use the older 3.x drivers which come with the server install.