Question & Answer
Question
Customer is upgrading from an older version of Controller (for example 10.1) to a newer version (for example 10.3). What steps do they need to perform to migrate the database to be usable with the new Controller versions?
Cause
Customers often move their Controller databases to a new/different database server, when upgrading to a new version of Controller.
- Care needs to be taken to make sure that the new (target) database server's settings match the old (source) database server
In addition, newer/later Controller versions have some new/different database tables (compared to the old version.
- Therefore, the customer must make slight modifications to the old database, before it can be used by the newer/later version of Controller.
More Information:
For best practice documentation on how to install Controller server on a new application server (plus how to upgrade the version of Controller on an existing application server) see separate IBM Technote #1608353.
Answer
(a) Make sure that the new (target) database server's settings match the old (source) database server
- Most importantly, make sure that the new collation (or character set) exactly matches the old server
(b) Use the Database Conversion utility to update/upgrade the database, so that it can be used by the newer/later version of Controller.
- This needs to be repeated for all 'application repository' databases, plus (if used) the 'FAP' and/or 'Data Mart' databases.
Steps:
(a) Make sure that the new (target) database server's settings match the old (source) database server
- TIP: For more information on how to do this, see separate IBM Technote #1594174 (also see 1388368 for why).
- TIP: For more information, see separate IBM Technote #1594174 (also see Scenario #4 inside separate IBM Technote #1366714)
- TIP: For more information, see separate IBM Technote #1594174 (or Scenario #2 inside separate IBM Technote #1366714)
- If the database was created in a modern version of SQL, then the tables are (normally) already owned by the user 'dbo' and therefore step 3 is unnecessary.
- For more details on this alternative method, see separate IBM Technote #1364881.
- TIP: For more information, see separate IBM Technote #1347750
- TIP: For more information, see separate IBM Technote #1570572
Microsoft SQL:
1. Ensure that the new SQL server's default collation exactly matches the old SQL server's default collation.
2. Create a SQL login (on the new SQL server) with the same name as the one currently used by the old system (for example, called 'fastnet')
3. After restoring the database (on the new SQL server), synchronise the SQL login names (remove orphaned users) by running a script similar to: EXECUTE sp_change_users_login 'Update_One', 'fastnet', 'fastnet'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TIP: Step 3 is only necessary if the database tables are owned by a user (for example 'fastnet') instead of 'dbo'. Typically this will only be true if the database was originally created in an old version of SQL (for example SQL 2000).
Also, be aware that there is an alternative method to step 3 (which also means that in step 2 you can change your SQL login name) which involves modifying all the table owners to be 'dbo'.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oracle:
Ensure that the new Oracle server's character set exactly matches the old Oracle server's character set
DB2:
Ensure that the new DB2 server's settings are compatible with Controller
(b) Use the Database Conversion utility
Launch Controller Configuration on the Application Server, by opening “Start –Programs – ‘IBM Cognos Controller – 64’ - IBM Cognos Controller - Controller Configuration”
- Click “database connections” and then highlight your database
- Click on the green “tick” and ensure that it says “connection succeeded”
- Click on the green “play” icon (see below - next to the tick) labelled “Run”
![](/support/pages/system/files/support/swg/cogtech.nsf/0/58e4375415e6aae88525809f0039cb5b/Content/0.1046.gif)
Click “Connect” at the following screen:
![](/support/pages/system/files/support/swg/cogtech.nsf/0/58e4375415e6aae88525809f0039cb5b/Content/2.4600.gif)
- IMPORTANT: MAKE SURE THAT YOUR SCREEN ABOVE IS FILLED IN SIMILARLY TO THE ABOVE BEFORE PROCEEDING
- Most importantly, ensure that 'Current Version' has a value larger than zero (for example 883)
- If the value of 'current version' is zero (0) then read advice on separate IBM Technote #1366714.
You will see that the “Current Version” is lower than the new “Upgrade to” DB version (927). Because of this, click “Upgrade” and wait for the process to finish:
![](/support/pages/system/files/support/swg/cogtech.nsf/0/58e4375415e6aae88525809f0039cb5b/Content/5.3D34.gif)
Click “Close”.
Repeat the above process to upgrade all other databases
=========================================
** Optional ** – This section is only necessary if you are publishing Data Marts
Repeat the instructions in the previous section, but this time:
- Click on the ‘”Datamart DB’ radio button (see picture below)
![](/support/pages/system/files/support/swg/cogtech.nsf/0/58e4375415e6aae88525809f0039cb5b/Content/9.3C4C.gif)
- Click on the ‘browse’ button (“…”) and MAKE SURE THAT YOU OPEN THE “DMData” folder [NOT the “Data” folder]
![](/support/pages/system/files/support/swg/cogtech.nsf/0/58e4375415e6aae88525809f0039cb5b/Content/13.2C90.gif)
- Choose your UDL file and upgrade it.
Repeat this section to upgrade the Data Mart version for *all* other database connections (contained inside the “DMData” folder).
=========================================
** Optional ** – This section is only necessary if you are using FAP
Launch “Controller Configuration” and then:
- Open “Database Connections” and create a new entry
- Choose your settings for the FAP database, for example:
- database Type (for example “SQL Server”)
- Name: controller_fap_data_mart
- Provider: SQLNCLI11.1
- User id: fastnet
- Password: <password>
- Initial Catalog: controller_fap_data_mart
- Data source: <SQL_server>
- Click ‘Save’
- Select this database connection (for example “controller_fap_data_mart”) and click the “run” button (green triangle).
This will launch the "Database Conversion Utility"
- Check that the "UDL File" is correctly pointing to your FAP database
- Click the "FAP DB" radio button
- Click "Connect"
- Click “Upgrade”
IMPORTANT: After doing the above steps, you should ‘tidy up’ the new Database Connection by moving the UDL file (for example C:\Program Files\ibm\cognos\ccr_64\data\ controller_fap_data_mart.UDL) to a new folder (for example C:\Program Files\ibm\cognos\FAP_UDL). If you do not do this, then users will see this in their list of databases to choose from when they launch Controller.
=========================================
Related Information
1608353 - How to install/upgrade Controller server/clie
1366714 - ** Troubleshooting ** Unable to determine dat
1388368 - Error 'Cannot resolve the collation conflict
1594174 - How to create new Controller database or cre
1347750 - How to check the Oracle database character se
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21996831