IBM Support

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

Technote (troubleshooting)


Problem(Abstract)

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

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

Related information

1347754 - How to check user / schema has correct privil
1361692 - How to install Oracle patchset 5473334 on Con
PM80819 - Controller Install Doc error: Oracle DB non-U

Historical Number

1038813

Document information

More support for: Cognos Controller
Controller

Software version: 8.3, 8.4, 8.5, 8.5.1, 10.1, 10.1.1, 10.2.0, 10.2.1

Operating system(s): Windows

Software edition: All Editions

Reference #: 1347750

Modified date: 25 August 2010


Translate this page: