MySQL is quite possibly the most commonly used database today due to its free license tier making it a very low risk investment. While it is not well geared towards the large queries of reporting and analysis, there may be a need to report directly from an existing MySQL database for any number of reasons.
The issue with MySQL in Cognos, is that it is only supported via general ODBC drivers. The trick to remember with ODBC drivers is that even if the OS and Cognos installation are 64-bit, ODBC drivers must be 32-bit to work with Cognos. Interestingly enough, Cognos Insight can use 64-bit drivers (probably due to it inheriting more from TM1 than Cognos proper). Also note that ODBC drivers cannot run in Dynamic Query Mode. This guide will step you through the process of setting up a MySQL data source specifically, but can be easily adapted to any ODBC driver.
Download and install 32-bit ODBC drivers
- Go to the MySQL ODBC download page and download the 32-bit version of the drivers. The msi version is probably the more convenient of the two, but the zip version should work as well.
- Once downloaded, a simple double click of the msi should start the installation wizard (the zip version is probably different, it should have instructions included).
Set up a 32-bit ODBC connection
- In 64-bit versions of windows, the default ODBC connection program that can be accessed from the start menu is the 64-bit version. To open the 32-bit one, go to Start->Run and enter the following: C:\Windows\SysWOW64\odbcad32.exe (this may vary slightly based on the system setup).
- Select the System DSN tab and click the Add button. This allows any user access to the connection information.
- From the list, select the MySQL 3.51 Driver from the list (version number may vary).
- Set up the connection info to reflect the MySQL database you are trying to connect.
- Press the OK button and it should now show up in the list.
Create a new Cognos Datasource
- Open a web browser and log in as an admininstrator to Cognos.
- If not already open, go to IBM Cognos Administration and select the Configuration tab. The Data Source Connections should be automatically selected.
- Select the New Data Source button on the upper right part of the screen.
- Give it a name and press the Next button.
- Select ODBC from the Type list. Leave everything else as it is and hit Next.
- Enter the ODBC data source, User ID and Password as they appear on the ODBC database. Use the Test Connection link on the bottom to confirm the setup.
- Press Finish and the connection should now be visible in the list:
Change the Project Query Mode
- Open up Framework Manager and project the data source is for.
- Select the Project at the top of the tree view on the left side of the window.
- Under the properties tab at the bottom of the screen, change the Query Mode option from the default Dynamic to Compatible.
It should now be possible to use this MySQL database in any packages in this project.
12 thoughts on “Using MySQL (or other ODBC connections) with Cognos”
can I use mySQL for the content store?
I thought I replied to this previously. Sorry about that.
The short answer is no, you need to use one of the databases officially supported for the content database. It is much more particular than a source database for performing analytics.
The long answer is that it might be possible, but would require doing something that would basically be a hack without any manuals, tutorials, or guides to help. This will likely be a time consuming endeavor that leaves your environment with some odd behavior. If you have an IBM support package, they will not be able to help you with this, and will probably cite this as the source of any other issues you may have on your system (with good reason). So in other words, if it’s possible, it’s probably far more costly than just using a supported database.
How to configure the above for linux environment of Cognos ?
Haven’t had to set up a mysql ODBC on linux, personally, but this set of directions looks like a pretty good start: http://www.savelono.com/linux/how-to-use-odbc-driver-to-connect-to-mysql-database-in-fedora-10.html
Good luck!
http://www.cognosasksumeet.blogspot.in/search/label/Accessing%20MySQL%20Database%20in%20Cognos%20using%20ODBC
But Test connection is failled it shows an error Composite (ODBC) / Compatible Failed DPR-ERR-2002 Unable to execute the request because there were no connections to the process available within the configured time limit.
DPR-ERR-2002 Unable to execute the request because there were no connections to the process available within the configured time limit.
Handler trace back:
[the_dispatcher] com.cognos.pogo.handlers.performance.PerformanceIndicationHandler
[the_dispatcher] com.cognos.pogo.handlers.logic.ChainHandler
[service_lookup] com.cognos.pogo.handlers.engine.ServiceLookupHandler
[load_balancer] com.cognos.pogo.handlers.logic.ChainHandler
[lb_forwarder] com.cognos.p2plb.clerver.LoadBalanceHandler
[mdaChainHandler] com.cognos.pogo.handlers.logic.ChainHandler
[asyncMetadataServiceHandler] com.cognos.pogo.async.impl.AsyncHandler
[metadataServiceHandler] com.cognos.metadataService.bibusHandler.MDSRVHandler
Generally that sort of error occurs when all of the connections are being utilized by something like many different reports running simultaneously. Coming up specifically when you are testing a new connection seems to be a symptom of an issue related to the encryption system. Try testing another database connection (non-mySQL at least, preferably something that doesn’t use the generic ODBC connection in Cognos) and see if it still has the same issue. In that case, you might need to refresh your cryptography keys. Otherwise, I would just make sure that you started up each machine in a multi machine instance in the right order, and that your usernames and passwords are correct. Hope that steers you in the right direction.
Hi Gerrit,
I am getting the same error when I run the report in Cognos 10.2.1. I have migrated the database from Netezza to Cognos. If I create a new report It works fine but if I use the old report it is throwing the error:
[the_dispatcher] com.cognos.pogo.handlers.performance.PerformanceIndicationHandler
[the_dispatcher] com.cognos.pogo.handlers.logic.ChainHandler
[service_lookup] com.cognos.pogo.handlers.engine.ServiceLookupHandler
[load_balancer] com.cognos.pogo.handlers.logic.ChainHandler
[lb_forwarder] com.cognos.p2plb.clerver.LoadBalanceHandler
[reportservicechain] com.cognos.pogo.handlers.logic.ChainHandler
[warp_with_authenticate] com.cognos.pogo.handlers.logic.ChainHandler
[do_warp] com.cognos.pogo.handlers.logic.ChainHandler
[warpmta] com.cognos.pogo.reportservice.ReportServerHandler
Do you have any idea about it.
Thanks
Don’t know about migrating from Netezza, but I know that there is typically some validation that needs to happen on the cognos end of things before you can run it. Try republishing the package from framework manager. Beyond that, the easiest option might be to just recreate the reports in the new system. That depends on the number of reports and complexity and all that.
Dear Gerrit Goewey,
I have a Doubt in Cognos Express:
i have installed Cognos express in Windows server and i have installed framework in local desktop. In Cognos server machine have a access to connect Database server (Mysql) so i have created ODBC and test connection also is working fine.
While i am using framework manger it shows all datasource list but i am unable to connect datasource
it shows below error
QE-DEF-0285 The logon failed.
QE-DEF-0323 The DSN(ODBC)/ServiceName is invalid. Either the DSN is missing or the host is inaccessible.
RQP-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database(s) in:
prcaseinterface
UDA-SQL-0031 Unable to access the “testdatasource” database. Check that the connection parameters to the database are configured correctly. For example, ensure that the data source connection contains the signon information, such as a password, to connect to the database.
UDA-SQL-0532 Data Source is not accessible: “testdatasource”.
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
In my local desktop don’t have access to Database server.
Please help me to resolve this issue.
Regards,
A.Anand
I have noticed that Cognos Express seems to have the additional requirement of having the ODBC on the System DSN tab (need to run as administrator to add to that tab). Try recreating the connection on that tab, but make sure there are no other connections with the same name, otherwise it will cause them to fail.