Database Connection - Step By Step

What is a database connection anyway?

A database connection is a facility that allows the client system to interact with the database server. A connection is required to allow exchange of data information between two servers.

A data source is the crux of database connectivity—a data source is the initial location where data is obtained; it defines the physical connection to a database. The data source connection specifies the parameters to connect to the database, such as the location of the database and the timeout duration These parameters form a connection string for the data source. IBM Cognos Analytics supports multiple OLAP, DMR and relational data sources. This includes a wide range of database servers—both legacy and modern sources.

Below are data source types supported in Cognos 11.2.3 (may vary slightly by version):

---

Sybase Adaptive Server Enterprise Data Sources

IBM Db2 Data Sources

Informix Data Sources

Microsoft Analysis Services Data Sources

IBM InfoSphere Warehouse Cubing Services

Microsoft SQL Server Data Sources

Oracle Data Sources

External Repository data source connections

ODBC Data Source Connections

TM1 Data Sources

XML Data Sources

SAP Business Information Warehouse (SAP BW) Data Sources

IBM Cognos Cubes

IBM Db2 Data Sources

---

Here are specific database servers supported in Cognos 11.2.3:

The Data Server Connection Details (configuration details) for each different data source will differ, mainly because of the specifications of the parameters to connect to a data source vary by vendor and sometimes even by database version.

First things first!

Below I am going to show you how to successfully connect to a database server in Cognos Analytics, but before that: the most vital and first step is product Configuration outside of the Cognos portal.

1. Make sure that the installed Java Database Connectivity (JDBC) drivers are in the correct Directory. The drivers must be copied to the Cognos Directory\drivers folder.

2. The JSQL driver for Microsoft SQL server must be replaced with the Microsoft JDBC driver. The driver JAR file sqljdbc42.jar is the file you need to support the Java version that is shipped with IBM® Cognos® Analytics.

JDBC connections for data servers/sources

If you’ve ever hung out with software engineers, you may have heard the acronym “JDBC”, but what is it and how does it relate to database connectivity? JDBC is an application programming interface (API) that provides a standardized manner for another system to access a database. Database vendors provide JDBC drivers that can be utilized by third party applications to access their database.

Cognos uses the JDBC drivers for 2 reasons:

1. To connect to data for authoring or viewing analytics content (reports, dashboards, stories, etc.) – when using “Dynamic Query Mode”. Dynamic Query Mode is the modern way of accessing data in Cognos, and unless you have a good reason to do otherwise, it should always be used.

2. Cognos Analytics uses JBDC to connect to it’s own Content Store database. This is this is the back end database that the Cognos Analytics software uses to run.

Important Note: Incorrect use of JDBC drivers may cause a many issues, ranging from Cognos not starting up to connection issues and even data-related issues. So make sure your drivers are in the correct directory and updated.

JDBC Data sources connections are a pre-requisite when your packages are deployed from Framework Manager with the use of Dynamic Query Mode option enabled.

String syntax for JDBC connection:

^User ID:^?Password:;LOCAL;JD;URL=<urlspec>;

DRIVER_NAME=<driver class name spec>[;CognosProperty=value[;...]] 

How JDBC Works

The JDBC driver Connects Cognos (a Java Application) to the Data source (database) using a specified database URL to establish a connection between the two making it possible to send queries and update statements and process the results.

Why does Cognos make use of JDBC drivers?

JBDC does not only provide methods to query and update data in a database, but it is also capable of reading any database, and it does not require content to be converted to utilize it. JBDC also provides support to both synchronous and asynchronous processing. Long story short, it provides a more standardized mechanism for getting at various types of databases than completely custom drivers for each data source.

What if my Data source is a spreadsheet file?

Cognos supports various formats of spreadsheets files, and it is quite simple to get it into Cognos (no database drivers required!):

1.     When you select Uploaded files in the Sources slide-out panel, you are presented with a list of uploaded files to use as input. Check one or more uploaded files to use as sources.

2.     Click Start or Done in Selected sources to expand the data module into its component tables.

3.     Drag the source uploaded file into your data module to start modelling.

How to Establish a data source connection  

For Security purposes you can include credential information for the database in the data source connection by creating a sign on. The user does not need to enter database credentials every time when the connection is used because the security information is encrypted and stored on the server.

These are the steps you would follow to connect Cognos to a database server:

1. Click Manage > Data server connections.

2. In the Data server connections pane, click the Add data server icon.

3. Select the data server type from the list of supported types.

For example, select IBM Db2 to create a connection to an IBM Db2 database or IBM Planning Analytics to create a connection to an IBM Cognos TM1 database.

4. In the field “New data server connection”, type a unique name for the connection.

5. Beside “Connections details”, click Edit and enter the connection details for the type of connection that you are creating.

For most connections, you must specify the JDBC URL. You can view the syntax and example URL under connection details. You might need to ask the database administrator for more details or check the database vendor documentation.

In the “Connection properties” box, type the supported property name. For information about the supported JDBC properties, see Cognos-specific connection parameters.

For IBM Planning Analytics connections, specify the TM1 database host and HTTP port number. To use an SSL connection, select the Use SSL check box.

6. Under “Authentication method”, specify how to access the data server.

You can select one of the following options:

Connect anonymously or Integrated security.

Choose the Connect anonymously option when anonymous access to the data server is allowed.

Choose the Integrated security option when the TM1 database is configured for Integrated Security mode 4 or 5. This option is applicable for IBM Planning Analytics connections only.

Prompt for the user ID and password.

Choose this option when the user must be prompted for database credentials with each use.

Use an external namespace.

Choose this option to secure the connection against a namespace that is configured for Cognos Analytics. Use the drop-down menu to select one of the available namespaces.

Cognos Analytics logs on to the data server with the credentials that are used to authenticate to the selected namespace. The namespace must be active, users must be logged on prior to accessing the data server connection, and the authentication credentials that are used for the namespace must be relevant for the data server authentication.

Typically, this authentication method is used in the following situations:

• You want Cognos Analytics to pass through to the database the user ID and password that is presented to the portal during authentication.

• You want Cognos Analytics to use Kerberos authentication.

• You want Cognos Analytics to use JSON Web Token (JWT) authentication.

The query server determines the credential information that is provided by the external namespace and chooses which connection method to attempt.

Use the following signon

Choose this option to assign a signon for the connection.

Select the signon from the drop-down list, or create a new signon by clicking the add icon. In the New data server connection window on the Credentials tab, type a user ID and password.

To restrict the signon to particular users, roles, or groups, on the Permissions tab, click the add icon, and specify the access permissions for the signon.

If you are creating a data source connection for The Weather Company, then you must configure at least one signon. The password must be your The Weather Company API key.

Click Test to verify that the data server connection works, and then click Save to save the new data server connection.

Connecting multiple databases in a single data source:

What should happen when your data is stored up in different databases? Cognos has the ability to connect more than two databases in a single Data source. You can use multiple data source connection in a single data source to facilitate the migration from one environment to another and maintain the integrity of a project. You can read further and more on it here.

Common errors/troubleshoot you might encounter and how to solve them:

In this section I want to discuss common problems you may encounter when setting up a database connection.

1.     Unable to select ODBC as the Type of Data source connection.

Data source connection such as NCR Teradata, IBM Red Brick, and Microsoft server do not support a Direct OBDC connection in Cognos. To create a OBDC connection for these Data servers, select Other Type

Solution: Type the following Database source connection string:

[^UserID:[^?Password:]];LOCAL;{RBSSTD};DSN= Data_Source [; UID=%s[;PWD=%s]][@ASYNC={01}][@ Connection_Timeout​/ Reply_Timeout ][@COLSEQ=[ Collation_Sequence ]]

To create data source connections to Microsoft SQL Server from UNIX, you must use the DataDirect ODBC driver for SQL Server.

2.     Content manager connection Problem in Oracle -Error CM-FG-5036.

The Cognos service does not create the tables in an Oracle Content manager, hence one of these error messages is generated:

CM-CFG-5063 A Content Manager configuration error was detected while connecting to the content store.

CM-CFG-5036 Content Manager failed to connect to the content store.

CM-SYS-5007 Content Manager failed to start. Review the Content Manager log files and then contact your system administrator or customer support

Solution:

a) Make sure that you can contact the Oracle instance through tnsping <SID> where the <SID> does not contain an underscore.

b) Configure the Oracle Content Manager connection in IBM Cognos Configuration so that the Service Name is the same as the <SID> in step 1.

3.     PowerCube Data source on Unix or Linux Computers.

When you create a data source connection to a PowerCube where both the PowerCube and all report servers are on UNIX or Linux computers, the following error may appear:

The field "Windows location:" is mandatory

Solution: type any characters in the Windows location field. The UNIX or Linux location must be correct.

4.     Error Connecting SQL Server Database.

If your SQL Server connection is Intermittent- sometimes it connects successfully and sometimes it does not and displays one of the following error messages:

     SQL Server cannot be found..

     Access denied.

In addition, when you run reports, sometimes they run but other times you see the following or a similar error message:

Connection not found - Check DNS entry or select different connection.

You may also have problems creating the database that is used for the content store.

These errors can occur if you configure named pipes instead of TCP/IP protocol as the default network library in the SQL Server Client Network Utility for a SQL Server reporting or content store database.

Conclusion

By providing a Database source/server connection the recurring client side configuration effort is shifted to a one time central configuration of different JDBC database driver. In this way, proximity to the data is created and user-friendly access is made possible.

We hope you found this article to be insightful and informative. If you have any questions about database connections or would like PMsquare to provide guidance and support for your analytics solution, contact us today. Be sure to subscribe to our newsletter for more PMsquare updates, articles, and insights delivered directly to your inbox.