How-to setup MSSQL JDBC on Linux for Dynamic Query Mode
Problem(Abstract)
This document provides detailed steps how-to setup MSSQL JDBC on Linux
Resolving the problem
Requirement - procure sqljdbc_<currentversion>_[language] tar.gz from Microsoft:
http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774
In this description Version 4.0.2206.100 (English) is used - this Version is applicable for MSSQL Server 2005, 2008 and 2012.
The following steps need to be done on every Cognos Instance that has an query service installed and active and is supposed to use the SQL Server as a DQM Datasource.
The steps are derived from the Proven Practices DQM Cookbook:
http://www.ibm.com/developerworks/data/library/cognos/infrastructure/cognos_specific/page529.html
Be aware that neither Authentication via "IBM Cognos software service credentials" nor Authentication via "An external namespace" will work on Linux - as Microsoft provides sqljdbc_auth only as a .dll File which is not useable on Linux.
Steps:
1. Create /tmp/mssqljdbc
2. Copy sqljdbc_4.0.2206.100_enu.tar.gz to /tmp/mssqljdbc/
3. Navigate to /tmp/mssqljdbc/ unless you are already there
4. Unpack sqljdbc_4.0.2206.100_enu.tar.gz by running "tar xzf sqljdbc_4.0.2206.100_enu.tar.gz"
5. Navigate to/tmp/mssqljdbc/sqljdbc_4.0/enu
6. Copy sqljdbc4.jar to …/C10/v5dataserver/lib and …C10/webapps/p2pd/WEB-INF/lib by running
"cp sqljdbc4.jar <path to your cognosinstall> /v5dataserver/lib"
and
"cp sqljdbc4.jar <path to your cognosinstall> /webapps/p2pd/WEB-INF/lib"
7. Stop Cognos Services
8. Start Cognos Services, steps 7 and 8 are needed for the service to pick up the jdbc driver
9. Open a Browser and access IBM Cognos Connection
10. Click Launch, Click IBM Cognos Administration
11. Click Configuration Tab
12. In the left-hand pane you should be on "Data Source Connections"
13. Click "New Data Source"
14. Enter a useful Name, Description and Screen Tip if necessary, then click Next
15. Select SQLServer (ODBC), ensure that the "Configure JDBC connection" checkbox stays ticked, click next
16. If you have an ODBC connection established via DataDirect 6.0/7.0* ODBC, enter all the necessary values, otherwise if you do not have DataDirect ODBC setup, tick the checkbox next to "Password", then enter the user and password required to access the SQLServer, Click next
17. Enter server name, port number and database name – also if applicable instance name
18. Click "Test the connection… ", then click "Test" – Type/Query Mode should say "Microsoft SQL Server (JDBC) / Dynamic) and the test should of course succeed.**
19. Click Close
20. Click Close
21. Click Finish, you now have a JDBC MSSQL Server Connection on you Linux Server
Be aware that only reports based on Dynamic Query Mode enabled Packages can be run against this Data Source.
*The DataDirect Version required needs to be retrieved from the Supported Environment for your Cognos BI 10 Version.
http://www-01.ibm.com/support/docview.wss?rs=3528&uid=swg27014782
Go to your BI Version, click Software, the Scroll to the Section Data Sources, search for Microsoft SQL Server, click the Information Icon behind the SQL Server Release you are using, then click the Notes Tab. The required DataDirect Version will be listed.
** if the test fails with XQE-DS-0014 Unable to logon to the data source. An unexpected error from the JDBC driver "com.microsoft.sqlserver.jdbc.SQLServerDriver" : "java.lang.UnsupportedOperationException: Java Runtime Environment (JRE) version 1.6 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0."
it means somebody already copied sqljdbc.jar to <path to your cognosinstall> /v5dataserver/lib and/or <path to your cognosinstall> /webapps/p2pd/WEB-INF/lib - remove the file from both locations. Then proceed with the testing after restarting the environment (to unload the driver).