Best practices for configuring the database connection

The Cognos® Business Intelligence server supports four database types, including IBM DB2 (LUW), IBM DB2 (z/OS), Oracle, and the Microsoft SQL server. You can configure these database connections with your Cognos Business Intelligence server.

The Cognos Business Intelligence server is embedded as part of the IBM® Control Center product installation. See the Detailed System Requirements to view which version of the Cognos Business Intelligence server is supported in IBM Control Center. The Cognos Business Intelligence server offers reporting capabilities as one of the features in IBM Control Center. The Cognos Business Intelligence server requires its own database, with preferences on supporting the collation settings, character set, and code page. The following list contains the different database types the Cognos Business Intelligence server supports:

  • IBM DB2 (LUW)
  • IBM DB2 (z/OS)
  • Oracle
  • Microsoft SQL server

The following tables detail the best practices for configuring the database connection to the Cognos Business Intelligence server.

Table 1. Server requirements for all database types
Parameter Description
dbname Name of the database. Use SID for a non-RAC database, and service name for a RAC database.
username User that has access to the database.
password Password to access the database.
hostname Host where the database server is located.
port Port where the database server is listening for connections.
Attention: For Microsoft SQL server databases, IBM Control Center and Cognos Business Intelligence server only support SQL authentication.
Table 2. Requirements for all database types and components
Database type Component Requirement
All databases SQL statement privileges The database user must be able to execute the following types of SQL statements:
  • CREATE TABLE
  • DROP TABLE
  • CREATE INDEX
  • DROP INDEX
  • VIEW
  • SELECT FROM <table>
  • INSERT INTO <table>
  • UPDATE <table>
  • DELETE <table>
Oracle Database user privileges CREATE SESSION UNLIMITED_TABLESPACE
Oracle Database settings IBM Control Center requires the following parameter settings in your Oracle database:
  • Number of open cursors is greater than 2000
  • Database block buffers is greater than 19200
  • Shared pool size is greater than 90000000
  • Large pool size is greater than 614400
  • Number of processes is greater than 500
  • Log buffer is greater than 163840
  • Database block size is greater than 8192
  • Sort area size is greater than 65536
  • Sort area retained size is greater than 65536
  • Max extents equals Unlimited
  • Character set equals AL32UTF8
    Important: If you are using a multi-byte character set, set the following parameter and restart Oracle: nls_length_semantics=CHAR inINIT<INSTANCE_NAME>.ORA file.
Microsoft SQL server Database user privileges Grant the database owner database role to the database user, and grant DB_DDLADMIN database role to the user.
Microsfot SQL server Database settings IBM Control Center requires the following parameter settings in your MS SQL database:
  • Collation setting is SQL_Latin1_General CP1_CI_AS.
  • Sort order is binary.
  • Security authentication is SQL.
  • Server and windows
  • Torn page is Detection Off.
DB2 Database user privileges Grant database administrator authority (DBADM) to the database user on this database.
DB2 Database settings IBM Control Center requires the following parameter settings in your DB2 database:
  • APPLHEAPSZ is greater than 10000.
  • APP_CTL_HEAP_SZ is greater than 512.
  • MAXAPPLS is greater than 150.
  • LOCKLIST is greater than 30000.
  • MAXLOCKS equals 100.
  • Database code page equals UTF-8.
Table 3. Requirements by database type and operating system
Database type Operating system Information
DB2 Windows or UNIX Each of the DB2 databases must have the following properties:
  • One buffer pool with a page size of 32K and another with a page size of 4K.
  • One system temporary table space with a page size of 32K and another system temporary table space with a page size of 4K.

    The temporary table spaces must be defined so that temporary tables can be defined in them.

  • One regular table space with a page size of 32K and another table space with a page size of 4K.

    Determine the location of db2jcc.jar IBM DB2 database driver file.
DB2 z/OS or OS/390 Each of the DB2 databases must have the following properties:
  • One buffer pool with a page size of 32K and another with a page size of 4K.
  • One system temporary table space with a page size of 32K and another system temporary table space with a page size of 4K. The temporary table spaces must be defined so that temporary tables can be defined in them.
  • One regular table space with a page size of 32K and another table space with a page size of 4K For each database, determine the DB2 database location, which is the path that specifies the DB2 location name and was defined during the DB2 installation.

    Determine the location of the db2jcc.jar database driver.
Microsoft SQL Windows Download and install the Microsoft SQL Server JDBC drivers setup.exe program.
Oracle UNIX or Windows Determine the location of the ojdbc7.jar Oracle database driver file.