Connecting to IBM DB2 Database Source from Cognos 8

Cognos 8 is primarily a web based reporting tool that is widely used by businesses to give its users access to required and authorized information. In most cases this information is stored in large data warehouses that are housed on common database platforms like DB2, Oracle or SQL server.

This article focuses on setting up of connection between one such database platform - IBM DB2 and the Cognos 8 reporting environment. After completing the steps outlined below, you should be able to successfully access the data stored in the DB2 database through any of the reporting tools provided as part of the Cognos 8 suite.

The connection process can be broadly broken down into two types of set ups. These two set ups can be viewed as independent from each other in operation but not in functionality. The steps below will provide more detailed information on each of the two set ups.

The first connection set up is between the Cognos 8 server and the IBM DB2 database. The second connection set up is between the client machine that has the Framework Manger software installed on it and the IBM DB2 database. In most cases this client machine will be operated by the modeler of the Cognos 8 package.

All the steps below can be classified into one of the two set ups. The connection information is accessed independently through the database client that is set up on each of the machines, therefore they also operation independently. They are dependant in functionality because the connection that is created in the Cognos Connection portal is used by the Cognos server and the client “modeler” machine.

Step 1: Creating user in the DB2 database:

Like when dealing with most applications that connect to the database, you are required to create an authorized user that the Cognos 8 server can use to run the Cognos SQL generated by the reports. The steps that need to be followed on the DB2 server are:

• Create the required user needs in the local users groups on the operating system. This user becomes part of the public group in the DB2 database.
• Open the Computer Management window and create the new user
• Do not include special characters in the password because this user will be used specifically to connect via Cognos. For example:
  
  

  Username- COGNOS_USERS      Pass- Password1 

Step 2: Installing the DB2 client on the Cognos server

The database information stored in the DB2 client is used by the Cognos server to connect to the source database. The following steps need to be completed on the Cognos server:

• Install the DB2 administrative client (control center) on the Cognos server machine
• Open the Configuration Assistant from setup tools menu
• Right click in the window and select “Add database using wizard”. Follow the steps needed in the wizard to add the DB2 database that Cognos server needs to use. Test the connection using the username and password created in step 1. In this case it would be - COGNOS_USER/Password1

Step 3: Installing the DB2 client on the client machine (desktop)

The database information stored in the DB2 client is used by the client machine to connect to the source database. The configuration information used is similar to the one used to set up the client on the server. The following steps need to be followed on the user “modeler” machine:

• Install the DB2 client on every machine that uses the Cognos Framework Manager to connect to the DB2 database. You can either install the DB2 runtime client or the DB2 administrative client.
• After installing the client, open the Configuration Assistant from set-up tools menu
• Right click in the window and select “Add database using wizard”. Follow the steps needed in the wizard to add the DB2 database that Cognos client needs to use.
• Test the connection using the username and password created earlier in step 1. In this case it would be - COGNOS_USER/Password1

Note: Although the metadata wizard in Cognos Frame-Work Manager shows all the sources that have been created in cognos connection, the configuration settings used to connect to the data source from frame work manager are the local settings stored on the client. The data source connections created in the cognos connection page is used by the server tools only and is in no way related to the frame work manager installed on the client machine.

Step 4: Adding the ODBC data source on the server and client machine

To add the ODBC data source on the server and client machine do the following:

• Open ODBC data source administrator through Administrative tools
• Click on the System DSN tab and click Add
• Choose IBM DB2 ODBC driver, click OK
• Select the appropriate database alias and test the source connection.

Step 5: Creating the source database connection in Cognos Connection

This final step relates to the creating the data source connection through the Cognos Connection web portal page. The user creating the connection needs to have administrative access. The connection created in this section will be available to both the web tools used by the report authors to build the reports and the data modeler designing the package. The following are the steps you need to do in Cognos Connection portal:

• Open up Cognos Connection and navigate to the Configuration tab on the Cognos Administration Page
• Select New Data Source from the data source connection
• Type in the “Name” that you want for the data source
• Choose Type of database as “DB2”
• Leave Isolation level as default Object gateway.
• Type in the DB2 database name (must be all caps)
• Leave connect string blank —
  This is where the user would enter in the name/name value pairs that DB2 or ODBC vendors can accept. Depending on your environment in rare cases it would be needed.
• Leave collation sequence blank —
  Collation sequence is required only in rare cases where there may be sorting discrepancies between IBM Cognos 8 and a database. Collation sequence is needed in situations for example if you use sorting functions such as running-totals in reports, you may need to add a collation sequence to the database connection string to maintain data integrity. Otherwise, when sorting a report that is grouped by one or more columns containing foreign characters, you may get inconsistent results. This is because without the collation sequence information, the default locale is used to sort data, whereas databases may use different collations in their sorting. To prevent sorting inconsistencies, you need to add the correct collation sequence to your database connection string when you create a data source or add a connection. If you enter a collation sequence, the syntax would be included in the connection string.
• Leave open asynchronously option unchecked —
  Open asynchronously check box can be selected if you want the connection to process requests independent of each other. Do not check this box if you want the connection to complete the current request before starting another one.
• Increase connect time if required —
  This is the time that the Cognos server will wait for a response from the data source)
• In the sign on section fill in the username and password created for the user on DB2 server (Note the password cannot have special characters)
• Test connection. Finish and save.
• Test the new connection in Framework Manager to import query subjects from the database.

In Conclusion...

Although, this article primarily focuses on setting up connection between IBM DB2 and Cognos 8, it is important to note that the same principle of setting up the database client on the server, the Framework Manager machine and then using the connection created in Cognos Connection portal could be applied while connecting to any other relational database like Oracle or SQL server.

About the Author

Mahesh Kalyanshetty is a BI Analyst for Crowe Horwath LLP. Located at Oak Brook, IL