Connecting to the IBM Social Media Analytics Database (DB2)
Can I access the underlying DB2 database behind Social Media Analytics so I can run additional analytics in tools such as SPSS Modeler or Cognos Business Intelligence?
Social Media Analytics provides DB2 views for integrating analytics into other applications. Instructions for how to connect to the DB2 instance on the Social Media Analytics server when DB2 is configured to use SSL encryption are not included in the product documentation and may require assistance from system administrators or IBM.
The DB2 instance used to store IBM Social Media Analytics (SMA) results may be used by other products. While connecting to DB2 over an unencrypted connection is relatively straightforward and well-documented elsewhere, when DB2 connections are encrypted using SSL, connectivity is more difficult. These instructions will describe the steps necessary to connect to an IBM Social Media Analytics server DB2 instance if it is encrypted using SSL.
First, collect from the system administrator or IBM the following information about the connection to the DB2 database. Most of these values will be referred to later in the instructions:
1) DB2 Server Name/IP (<SMA_DB2SERVER>):
2) DB2 Server Port (<SMA_DB2PORT>):
3) Username (<DB2_USER>):
4) Password (<DB2_PASSWORD>):
5) Project Name:
6) Project Database Name (<PROJECT_DB>):
7) The ARM file (the Signer Certificate which is an ASCII text file containing the public key needed to connect to the DB2 server)
- Verify that the local network does not block the access to DB2 Server by issuing the command tracert <DB2 Server Name/IP. At least one valid hop should be received. Alternatively, run telnet <DB2 Server Name/IP> <DB2 Server Port> and if DB2 server host is reachable, a blank screen should appear.
- If using a 3rd party application, for example SPSS modeler used as a client to access the SMA database, make sure 32/64bit DB2 client and GSK versions are properly lined up with the 3rd party application.
- If the db2 cli tool can connect to db2 but IBM SPSS Modeler can't, try reinstalling Modeler.
Follow the steps below.
1. Download and install the IBM Data Server Runtime Client (DB2 CLI and JDBC Drivers)
The IBM Data Server Runtime Client provides a way to run applications on remote databases. GUI tools are not shipped with the IBM Data Server Runtime Client. Capabilities include the following:
· DB2 Command Line Processor Plus for dynamically creating, editing, and running SQL statements and scripts
· Support for applications that use ODBC, CLI, PHP, or RUBY to access databases
· On Windows operating systems, support for applications that use .NET or OLE DB to access databases
· Support for client applications and applets that are written in the Java language using JDBC and for embedded SQL for Java (SQLJ)
Download and install the DB2 Version 10.5 Fix Pack 5 Client for Linux, UNIX, and Windows. An IBM ID is needed to download software from IBM FixCentral:
Be sure to choose the IBM Data Server Runtime Client.
Additional instructions on the IBM Knowledge Center:
2. Download and install the 64-bit (if appropriate) or 32-bit (if appropriate) IBM Global Security Kit v8 (GSKit, or GSK8) which is needed for SSL encryption of data
The IBM Global Security Kit provides libraries and utilities for SSL communication. GSKit 8 includes the GSKCapiCmd command-line interface for managing keys, certificates, and certificate requests. In a Windows 64-bit environment, to work with Cognos Business Intelligence, you will want both the 32-bit and the 64-bit libraries.
Download and install the IBM GSKit from IBM FixCentral. In order to download the installers, you will need an IBM ID and a valid IBM Customer Number (ICN). The following link will take you to the latest GSK8 downloads.
3. Configure the environment for GSK8
In order for the GSKit to work, its binaries and libraries must be added to the PATH environment variable. This is slightly different for different versions of Windows and Linux. In Windows 7, you should
1) Go to the “Control Panel”,
2) Choose “System”
3) Choose “Advanced system settings”
4) Click “Environment Variables.”
5) Highlight “Path”
6) Click “Edit…”
7) Add “C:\Program Files\IBM\GSK8\lib64” (or the installation path where you installed the 64-bit GSKit)
8) Add “C:\Program Files\IBM\GSK8\bin” (or the installation path where you installed the 64-bit GSKit)
9) Add “C:\Program Files (x86)\IBM\GSK8\lib” if you installed the 32-bit libraries on 64-bit Windows
10) Add “C:\Program Files (x86)\IBM\GSK8\bin” if you installed the 32-bit libraries on 64-bit Windows
NOTE: A semicolon before or after the new entries is required.
11) Continue pressing “OK” until all dialog boxes have closed.
12) If you have opened any command prompts, you will need to close and reopen them for them to get the new Path setting.
Testing this change:
1) Open a command prompt
2) Type “gsk8capicmd_64 -version”
3) Type “gsk8capicmd -version”
If you receive output after each command resembling the following, the path has been properly set up:
@(#)CompanyName: IBM Corporation
@(#)FileDescription: IBM Global Security Toolkit
@(#)LegalCopyright: Licensed Materials - Property of IBM GSKit
(C) Copyright IBM Corp.1995, 2011
All Rights Reserved. US Government Users
Restricted Rights - Use, duplication or disclosure
restricted by GSA ADP Schedule Contract with IBM Corp.
@(#)ProductName: gsk8b (GoldCoast Build) 121010
@(#)CMVCInfo: gsk8b_111013/gsk8b_pkg gsk8b_120615/gsk8b_ikm gsk8b_121010/gsk8b_ssl gsk8b_101019/gsk8b_support gsk8b_120710/gsk8b_acme gsk8b_120816/gsk8b_cms gsk8b_120926/gsk8b_doc
4. Obtain the Signer Certificate from the SMA Server
Signer Certificates establish the trust relationship in SSL communication. Signer certificates are also known as public keys. The Signer Certificate we are using is extracted into a text format which can be used by GSKit, and is a text file with a “.arm” extension such as “usrd11q78100XX.adm.rtp.ssm.sdc.gts.ibm.com.arm” and will need to be provided to you by the system administrator or IBM as the file is created and stored on the DB2 server.
5. Use the GSKit commands to store the signer certificate in a new client key database
The signer certificate must be stored in a key database (kdb), which holds the encrypted key information and is locked with a password. There are several types of key databases, and we will require one for Certificate Management System (CMS). The password to the key database is stored encrypted in a file known as a stash file. GSKit uses the stash file to obtain the password to the key database.
Create a new folder and copy or move the .arm file to it.
Open a DOS command prompt and change directory to the new folder.
Run the following commands, changing only the italicized text to represent the actual file that was given to you. Long lines have been reverse-indented for clarity.
Create a key database (this only happens once, as the key database is used for all connections from this client):
gsk8capicmd_64 -keydb -create -db client.kdb -pw ccipass –stash
For each SMA server you have received a key for, add that key into the Key Database by using the following command. Replace <UNIQUELABEL> with a unique identifier for this key such as the server name (usrd11q78100XX).
gsk8capicmd_64 -cert -add -db client.kdb -pw ccipass -label <UNIQUELABEL> -format ascii -file usrd11q78100XX.adm.rtp.ssm.sdc.gts.ibm.com.arm
As a result of running the above commands, the directory will contain several new files with different extensions.
Additional reading: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.sec.doc/doc/t0053518.html
6. Configure the DB2 Runtime Client to support SSL and catalog the remote SMA database server
In order to access the remote database using the DB2 command line tools and ODBC driver, it is necessary to configure the local DB2 client by telling it where the key database and stash files are.
Open DB2 Command Prompt by finding the Command Line Tools under IBM DB2 in the Windows Start Menu. Change to the same directory used in the previous step and, in the command prompt, type the following commands, replacing c:\path\to with the actual path to those files, putting single-quotes around the file paths if there are special characters such as spaces in the path.
db2 update dbm cfg using SSL_CLNT_KEYDB ' c:\path\to\client.kdb'
db2 update dbm cfg using SSL_CLNT_STASH ' c:\path\to\client.sth'
db2 catalog tcpip node <MY_NODE_ALIAS> remote <SMA_DB2SERVER> server <SMA_DB2PORT> security ssl
· <MY_NODE_ALIAS> is a name that you choose to access the SMA node
· <SMA_DB2SERVER> is the host name or IP address given to you by IBM SMA
· <SMA_DB2PORT> is the port number at which the server is configured for an SSL connection, given to you by IBM SMA
db2 catalog tcpip node CustomerDB remote 188.8.131.52 server 4444 security ssl
7. Catalog each database on the remote server and connect
After cataloging the server itself, it is necessary to catalog each database you wish to connect to. SMA databases are named sequentially on each server in the format PROXXXX, where XXXX is a number. You can determine the database name by connecting to your project’s document limiter Admin.jsp page as documented in the SMA documentation at http://pic.dhe.ibm.com/infocenter/sma/v1r2m0/topic/com.ibm.swg.ba.cognos.ag_ci.1.2.0.doc/t_ag_ci_settingdocumentlimits.html
1. Log in to Social Media Analytics as a user with administrator or system administrator role.
2. Substituting in the proper IP address or hostname for "<ui_node_host_name>", enter the following web address in to the address of the web browser:
With the database name now known, enter the following command at the command prompt to make an alias to the database locally.
db2 catalog database <PROJECT_DB> as <PROJECT_DB_ALIAS> at node <MY_NODE_ALIAS>
· <PROJECT_DB> is the database name on the server corresponding to the required project
· <PROJECT_DB_ALIAS> is how the DB name will later appear in your environment
· <MY_NODE_ALIAS> is the name you chose in step 7
After these connections are all set up, we must reset the DB2 client in order for the settings to take affect. Run the command
With the database server and the database both cataloged, we can connect to the database using the DB2 Command Line Interface (CLI).
db2 connect to <PROJECT_DB_ALIAS> user <DB2_USER> using "<DB2_PASSWORD>"
<PROJECT_DB_ALIAS> is the name you chose previously,
<DB2_USER> is the DB2 user assigned to you by the SMA team
<DB2_PASSWORD> is the password for the above user, provided by the SMA team. The password should be in quotes as it likely contains non alpha-numeric characters.
8. Access for Cognos BI: Create a Cognos Business Intelligence Data Source
The easiest connection in Cognos Business Intelligence to your SMA database is a CLI connection. Cognos always uses a 32-bit connection (even on a 64-bit server), so it is necessary to have the 32-bit version of GSK8 installed and in the PATH environment variable.
To create the connection
1. Log in to Cognos as an administrator
2. Open IBM Cognos Administration
3. Navigate to the Configuration tab
4. Ensure you are on the Data Source Connections screen
5. Click the “New Connection” icon
6. Give the connection a name and any other optional information you desire, and click next
9. Access for SPSS Modeler: Create an ODBC data source to the SMA DB2 database
Many applications use ODBC to connect to databases. To create an ODBC data source to use by your application:
1) Create an ODBC Data Source
a. On Windows XP: Open Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC)
b. On Windows 7: Search for ‘ ODBC’ from the Start Menu and select ‘Set up data sources (ODBC)’.
2) Select the “System DSN” tab
3) Click “Add”
4) Select “IBM DB2 ODBC Driver”
5) Click “Finish”
6) Enter a datasource name of your choice
7) Choose the database alias <PROJECT_DB_ALIAS> you created earlier
8) Click “OK”
10. Create a JDBC Type-2 DB2 data source that points to the SMA DB2 database
You can use a Type-2 or Type-4 JDBC driver to connect to the database. The benefit of using the Type-2 driver is that we can use the previous steps to perform the SSL encryption and it is very easy to set up once the previous steps are done. The Type-2 driver works by tunneling the database traffic through the DB2 CLI driver. A type-4-driver is written completely in Java and connects directly to the DB2 database. To do that, changes need to be made to the Java security settings, a new Java keystore needs to be created.
To use the Type-2 driver, set up a JDBC connection using the JDBC URL, substituting in the same Project DB Alias used from the db2 catalog database command
The classname to use is com.ibm.db2.jcc.DB2Driver
The JDBC driver files are db2jcc.jar and db2jcc_license_cu.jar.
Alternatively, to configure the pure Java Type-4 driver, rather than use the CLI to connect to DB2, JDBC can connect to DB2 over SSL directly but needs configuration. Changes must be made to the Java security providers, as well as the JDBC connection string. The changes necessary on the server have already been done, so it is only the client-side settings that need to be made. These instructions are based on the page: http://www.ibm.com/developerworks/industry/library/ind-configure-fips-db2-ws/index.html?ca=drs-#ConfiguretheJREtouseSSL
Create a Java keystore to hold the certificate file. To do so, open a command prompt and change to the directory holding the signer certificate with the .arm extension. Run the command to create a java keystore named client.jks. You will need to enter a password, which you will need later, and verify the import.
keytool -import -trustcacerts -alias SMA -file usrd11q78100XX.adm.rtp.ssm.sdc.gts.ibm.com.arm -keystore client.jks
Follow these steps to configure Java Runtime Environment to use Secure Sockets Layer (SSL):
10.1 Configure the Java security provider in java.security file
Edit the file java.security in [JAVA_HOME]\lib\security to include IBMJSSEProvider2 as the security provider. The configuration in the specified file depends on the Java development kit you have. In this example, we are considering the use of IBM Software Development Kit (SDK).
Include the security provider in the list of providers and rearrange the rest of the providers.
After the change, the list of providers in the java.security file is displayed as:
# List of providers and their preference orders (see above):
10.2 Set the Java system property
Because the IBM Data Server Driver for JDBC must be in FIPS-compliant mode, you must set the com.ibm.jsse2.JSSEFIPS Java system property in the application or application server using the driver:
10.3 Configure SSL socket factory providers in java.security file
Edit the file java.security in [WAS_HOME]\ java\jre\lib\security to include the SSL socket factory providers. Uncomment the following socket factory providers:
After the change, the list of SSL socket factory providers in the java.security file is displayed as:
# Determines the default SSLSocketFactory and SSLServerSocketFactory
# provider implementations for the javax.net.ssl package. If due to
# export and/or import regulations, the providers are not allowed to be
# replaced, changing these values will produce non-functional
# SocketFactory or ServerSocketFactory implementations.
# Default JSSE socket factories
# WebSphere socket factories (in cryptosf.jar)
Set parameters in the JVM arguments or in the JDBC connection string. To set the parameter in the JVM arguments, use the following parameters when you run your application. This configuration must be done in the JVM arguments. The truststore parameters refer to the java keystore created earlier.
Example of the JVM arguments:
Alternatively, use the JDBC Connection string to connect to the database
The JDBC Connection string is in the format:
jdbc:db2://< db2-server>: <db2_port>/ <SMA_PROJECT_NAME>:sslConnection=true;sslTrustStoreLocation= <KEYSTORE_LOCATION>;sslTrustStorePassword= <KEYSTORE_PASSWORD>;