IBM Support

How to check the Oracle database character set and patch level for Controller

Troubleshooting


Problem

Customer is unsure whether their Oracle server meets the requirements for use as a Controller database repository.

Symptom

.

Cause

Controller requires two separate databases in order to work (see below). TIP: For some specialist tasks/need, Controller can be configured to use more than two separate databases if required.

  • When using Oracle to store these 2 Controller databases, the required character settings for each database are different!
=> You cannot use the same (one single) database for both purposes!

(1) CONTENTSTORE database
  • Contains all the information necessary for the IBM Cognos BI reporting component to work, for example the I.T. / server configuration etc.
  • This (BI) component's main job is to run the 200+ 'standard reports' (which come as standard with Controller), but it also handles some security tasks too.
  • It is the database used inside 'Cognos Configuration', for example this screen:

  • This database must be Unicode
  • A recommended Oracle character set is:
    • 'Database character set': 'AL32UTF8'
    • 'National Character Set': 'AL16UTF16'

(2) APPLICATION REPOSITORY database
  • This is the most important database
  • It stores the financial structures and information which relates to the functional configuration of the financial consolidation software. For example, it stores the accounting/company structure, currency values etc.
  • It is the database used inside 'Controller Configuration', for example this screen:

  • This database must be NON-Unicode
  • The supported Oracle character set is:
    • 'Database character set' is 'WE8MSWIN1252'
    • 'National Character Set' is 'AL16UTF16'

Resolving The Problem

Use the following script to find the values of the relevant Oracle database:

    Connect system/<password>@<database_name> AS SYSDBA

    select * from nls_database_parameters;

Steps:
  1. Launch SQL*Plus Worksheet
  2. Logon to the correct database as SYSTEM
  3. Type in the following: select * from nls_database_parameters;
  4. Press 'execute' button.

This will return values such as:
    PARAMETER VALUE
    ------------------------------ ----------------------------------------
    NLS_LANGUAGE AMERICAN
    NLS_TERRITORY AMERICA
    NLS_CURRENCY $
    NLS_ISO_CURRENCY AMERICA
    NLS_NUMERIC_CHARACTERS .,
    NLS_CHARACTERSET WE8MSWIN1252
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_DATE_LANGUAGE AMERICAN
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY $
    NLS_COMP BINARY
    NLS_LENGTH_SEMANTICS BYTE
    NLS_NCHAR_CONV_EXCP FALSE
    NLS_NCHAR_CHARACTERSET AL16UTF16
    NLS_RDBMS_VERSION 10.2.0.3.0

    20 rows selected.

The most important settings have been highlighted, which are:
  • NLS_CHARACTERSET = WE8MSWIN1252
  • NLS_NCHAR_CHARACTERSET = AL16UTF16
  • NLS_RDBMS_VERSION = 10.2.0.3.0

In this example, this shows that:
  • The 'Database character set' is 'WE8MSWIN1252'
  • The 'National Character Set' is 'AL16UTF16'
  • The Oracle server is running version 10g Release 2, with patchset 3 installed

This example (above) would be perfect as a location for the 'application repository' but must NOT be used for the 'ContentStore'.
=================================

TIP: A suitable location for a 'ContentStore' would have the following values:
    PARAMETER VALUE
    --------------------------------------------
    NLS_LANGUAGE AMERICAN
    NLS_TERRITORY AMERICA
    NLS_CURRENCY $
    NLS_ISO_CURRENCY AMERICA
    NLS_NUMERIC_CHARACTERS .,
    NLS_CHARACTERSET AL32UTF8
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_DATE_LANGUAGE AMERICAN
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY $
    NLS_COMP BINARY
    NLS_LENGTH_SEMANTICS BYTE
    NLS_NCHAR_CONV_EXCP FALSE
    NLS_NCHAR_CHARACTERSET AL16UTF16
    NLS_RDBMS_VERSION 10.2.0.3.0

[{"Product":{"code":"SS9S6B","label":"Cognos Controller"},"Business Unit":{"code":"BU002","label":"Business Analytics"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.1;10.2.0;10.1.1;10.1;8.5.1;8.5;8.4;8.3","Edition":"All Editions"}]

Historical Number

1038813

Document Information

Modified date:
15 June 2018

UID

swg21347750