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!
(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:
- Launch SQL*Plus Worksheet
- Logon to the correct database as SYSTEM
- Type in the following: select * from nls_database_parameters;
- 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