IBM Support

How can I change the collation of a Controller database?

Troubleshooting


Problem

Customer is experiencing problems with their environment, which relate to the collation (also known as 'character set') of their database.
How can the customer modify the collation of their current (existing) Controller database?

Symptom

For examples of the sorts of problems that 'wrong' database collations can cause, see separate IBM Technotes #1345692 and #1388368.

Cause

The reason why database collations are important is because Controller regularly reads/writes information to/from the SQL "TEMPDB" database.
  • If the TEMPDB database has a different collation (from the Controller database) then errors can occur
  • It is therefore vital that the Controller database has *exactly* the same collation as the TEMPDB database.

Environment

Controller database hosted on Microsoft SQL server.

Diagnosing The Problem

How to check your SQL server's default collation (which is the same as the TEMPDB collation), plus also the Controller database's collation:

Logon to the SQL server, and perform the following steps:

1. Launch "SQL Server Management Studio"

2. Expand "Databases - System Databases" and locate the database "tempdb"

3. Right-click on 'tempdb' and choose 'Properties':


4. Make a note of the collation (for example "SQL_Latin1_General_CP1_CI_AS"):

5. Right-click on your Controller application repository database (for example "ControllerLive") and choose "properties"

6. Make a note of the collation (for example "Latin1_General_CI_AS").

Resolving The Problem

Use the Controller Data Migration Tool.
Steps:
1. Create a brand new 'blank' database, on a SQL server which has your preferred new collation
  • This will be your 'target' database
2. Use 'Controller Configuration' to connect to the new (blank) database, and populate it with the Controller databases
  • Specifically, use 'Database Conversion' to convert it to the same Controller version as the 'source' database currently is
3. Download and install the Controller Data Migration Tool
4. Connect the Data Migration Tool to the original (source) database, and export all the data (into a ZIP file)
5. Connect the Data Migration Tool to the new (target) database, and import all the data (from the ZIP file)
6. Afterwards, perform a few required extra tasks (for example transfer the data which the tool cannot copy over), as discussed in Technote #873220.
TIP: For more information on the above steps (how to use the Controller Data Migration Tool) see separate IBM Technote #873220.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"Not Applicable","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 January 2020

UID

swg21394936

Page Feedback