Cognos database considerations for Oracle

IBM® Cognos® Business Intelligence uses the COGNOSCS (IBM Cognos BI content store) database for configuration and report specification information, and uses the MONITOR database for actual reporting data.

COGNOSCS database considerations for IBM Cognos BI

The IBM Cognos BI service creates tables in the IBM Cognos BI content store database the first time it is started. The database user provided for accessing the IBM Cognos BI content store database must have full access to Oracle to create tables, views, sequences, triggers, and so on. In IBM Cognos BI, you cannot specify a separate schema name; the IBM Cognos BI objects are created in the default schema and default table space of the database user. It is recommended that you create a new database user for the content store database only.

Important: Do not use the SYSTEM user for this purpose, because you do not want the IBM Cognos BI database objects to be created in the system area.

The COGNOSCS database must be used only for IBM Business Monitor data. You must not add data directly to the COGNOSCS database, or use the database with other databases to create reports against such data (combined or not with data created in IBM Business Monitor).

MONITOR database considerations for IBM Cognos BI

If your MONITOR database is remote from the server or cluster that the IBM Cognos Business Intelligence service is deployed on, you must install a full database client or the Oracle Instant Client on the IBM Cognos BI server to deploy cubes.

The Oracle instance for IBM Cognos BI must be addressable by a TNSNAMES entry in the Oracle client on the IBM Cognos BI server. The entry in TNSNAMES must use the same name as the database instance name that you entered for the MONITOR database during profile creation (for example, ORCL). Otherwise, you must to change the WBMONITOR_DB data source in IBM Cognos BI to point to the correct TNSNAMES entry.

If you are using Oracle Instant Client, the path to the client must be in the system path. A TNSNAMES.ORA file must also be included with an entry for the Oracle database server, and the TNS_ADMIN environment variable must be set to point to the directory containing the TNSNAMES.ORA file.

Important: Install the SQLPlus command utility with Oracle Instant Client for troubleshooting purposes.

The following example shows the contents of a valid TNSNAMES.ORA file. (The uppercase ORCL is the alias for the database connection.)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

IBM Cognos BI needs access to Oracle client commands when publishing cube packages during model deployment.

The Oracle client must be in the server PATH.
The user starting the IBM Business Monitor server must be profiled as an Oracle user profile.

Oracle Instant Client

To use Oracle Instant Client, you must download and install the following libraries:

Instant Client Package - Basic Instant Client Package
SQL*Plus (useful for connection troubleshooting)

Add the installation directory to the server path and create a TNSNAMES.ORA file as described in the previous section. Add a TNS_ADMIN environment variable and specify the path to the directory containing the TNSNAMES.ORA file.

32-bit client requirement

An Oracle 32-bit client must be installed for IBM Cognos BI cube deployment. If Oracle is installed on a separate server, the 32-bit Oracle Instant Client is recommended. If Oracle is installed on the same server as IBM Cognos BI and 64-bit Oracle is installed, the 32-bit Oracle Instant Client must be installed as well.

If you are using 64-bit Oracle, complete the following steps to configure a path to the Oracle 32-bit libraries:

In the administrative console, click Servers > Server types > WebSphere application servers > server_name. The Configuration panel is displayed.
Under Server Infrastructure, expand Java and Process Management and click Process Definition.
Under Additional Properties, click Environment Entries. Add the path to the Oracle Instant Client as described below:
- Add the path to the 32-bit Oracle Instant Client to the PATH environment variable using a ";" as a delimiter:
- Add the path to the 32-bit Oracle Instant Client to the following environment variable using a ":" as a delimiter.
  - For Linux and Solaris: LD_LIBRARY_PATH
  - For AIX: LIBPATH