IBM Support

How to migrate / upgrade / upsize an existing database to a new (later) version of Controller

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


    Microsoft SQL:

    1. Ensure that the new SQL server's default collation exactly matches the old SQL server's default collation.

    • TIP: For more information on how to do this, see separate IBM Technote #1594174 (also see 1388368 for why).

    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')
    • TIP: For more information, see separate IBM Technote #1594174 (also see Scenario #4 inside separate IBM Technote #1366714)

    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: For more information, see separate IBM Technote #1594174 (or Scenario #2 inside separate IBM Technote #1366714)

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    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).

    • 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.

    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'.

    • For more details on this alternative method, see separate IBM Technote #1364881.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Oracle:

    Ensure that the new Oracle server's character set exactly matches the old Oracle server's character set

    • TIP: For more information, see separate IBM Technote #1347750

    DB2:

    Ensure that the new DB2 server's settings are compatible with Controller

    • TIP: For more information, see separate IBM Technote #1570572

(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”

 
Click “Connect” at the following screen:

    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:

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)
  • Click on the ‘browse’ button (“…”) and MAKE SURE THAT YOU OPEN THE “DMData” folder [NOT the “Data” folder]

  • 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.
=========================================

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

Document Information

Modified date:
15 June 2018

UID

swg21996831

Page Feedback