Question & Answer
Question
This article we describes how to create a Cognos dynamic query mode (DQM) data source connection to BigSQL on the IBM Cloud. This article was originally created using Cognos 10.2.2 FP5 but has since been verified against newer Cognos 11.x versions as well as newer BigInsights on Cloud versions. Consult the Cognos supported environments pages to ensure the versions of the the products you're working with are supported.
Answer
Environment:
1. Cognos will use the DB2 db2jcc4.jar JDBC driver to establish connectivity to BigSQL. Download highest 10.5.x JDBC 4.0 Driver (db2jcc4.jar) available, install, then copy the db2jcc4.jar and db2jcc_license_cu.jar into your <Cognos install>\webapps\p2pd\WEB-INF\lib (for all report servers in your environment). A Cognos/websphere restart is needed.
DB2 JDBC Driver Versions and Downloads
http://www-01.ibm.com/support/docview.wss?uid=swg21363866
2. BigSQL uses an SSL key size greater than 128-bit therefore an update your Websphere OR the default/provided Websphere Liberty Profile is needed before you can connect.
To correct the above error, acquire the unrestricted JCE policy files (local_policy.jar, US_export_policy.jar) from the link below and replace the policy files you currently have in place for the JRE which is in use by Websphere or Websphere Liberty profile. . A Cognos/websphere restart is needed.
Websphere:
<Websphere install>\AppServer\java_1.XXXXX\jre\lib\security
Websphere Liberty Profile:
<Cognos 11 install>\jre\lib\security
Unrestricted JCE policy File.
https://www14.software.ibm.com/webapp/iwm/web/reg/pick.do?source=jcesdk&lang=en_US
3. Bluemix Cloud's BigSQL utilizes SSL therefore you need to create a keystore to be used by the report server to establish a connection. Using your browser, browse to your Ambari web interface. Click the lock icon in the address bar and choose 'More Information' to view the certificate.
4. Export the certificate using your browser to x.509 pem/crt.
5. Use Keytool to create a keystore containing the certificate which you exported from Ambari. Keytool is a tool which is provided with the IBM jre and is generally located in the bin directory of the jre. Use the keytool.exe/sh which is in the path of the java you're using; in this case Websphere is hosting Cognos using a JRE in the path below, therefore this is the keytool I will use.
<Websphere install>\AppServer\java_1.7_64\jre\bin\keytool.exe/sh
In the below syntax bic4103-105-mastermanager.bicloud.com.crt is the exported certificate from step4, bic4103-105-keystore.jks is an arbitrary name of the keystore to be created.
keytool -import -trustcacerts -alias biginsights -file bic4103-105-mastermanager.bicloud.com.crt -keystore bic4103-105-keystore.jks -storepass mypassword -noprompt
6. Login to Cognos Connection > Cognos Administration and click the Configuration tab then Data Source Connections. Select the ‘New Data Source’ button in the top right. Select :
Type: JDBC
Type: IBM BigInsights(Big SQL)
JDBC URL: jdbc:db2://<BigSQL head node IP>:51000/bigsql
*Connection Properties: sslConnection=true;sslTrustStoreLocation=C:\Program Files\c1022\bin64\bic4103-105-keystore.jks;sslTrustStorePassword=password;
User ID: biadmin
Password: <biadmin password>
*path and password from step5
Test the connection.
Was this topic helpful?
Document Information
Modified date:
18 July 2020
UID
swg21990775