Question & Answer
Question
Customer would like to host their Controller application repository database(s) on Microsoft SQL Server.
During the SQL server installation wizard, SQL asks what its default collation should be. What SQL collations does Controller support?
Cause
If the collation of the Controller database is different from the collation of the TEMPDB database (which is a Microsoft database that always exists on the same SQL server) then this will cause errors/problems.
- For example, see separate IBM Technotes #216475 , 388945.
Upper-case preference collations (for example SQL_Latin1_General_Pref_CP1_CI_AS) are not supported because they can cause errors with alphanumeric keys (which are used extensively in Controller).
- In addition, case-sensitive (including binary) collations will also cause problems.
Answer
Controller supports many/most SQL collations, so long as all the following rules are obeyed:
(1) Collation of the Controller database must be identical to the collation of the TEMPDB database
- As a consequence, if you move your Controller database (in the future) to a new
- Therefore think carefully about the collation of the first SQL server that you use with Controller. In general, it should have the same collation as your corporate standard (which means that if you move your Controller database to a new SQL server in the future, the new SQL server should have the same collation for its TEMPDB too)
(2) Collation must NOT be case-sensitive (for example SQL_Latin1_General_CP1_CS_AS)
(3) Collation must NOT be a binary collation (for example Latin1_General_BIN) because they are case-sensitive too
(4) Collation must NOT be upper-case preference (for example SQL_Latin1_General_Pref_CP1_CI_AS)
(5) A small number of collations have been found to cause issues
- For example 'Sami_Sweden_Finland_100_CI_AS'.
=======================================
Frequently asked questions:
Q1 - If I do not have any preference for a collation, what does IBM recommend?
The most standard (commonly used) collation is: SQL_Latin1_General_CP1_CI_AS
- The second-most common is: Latin1_General_CI_AS
If you have no preference about which collation you would most like to use, then IBM recommend one of the above. This is because it makes it easier for IBM to support you.
=======================================
Related Information
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.4.2","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
27 January 2020
UID
ibm11170148