IBM Support

Export and import Cognos Controller data - how to install/use the Controller Data Migration Tool

How To


Summary

Customer would like to migrate their Controller database from one platform (for example MS SQL) to a different platform (for example IBM Db2).

How to use the IBM Cognos Controller Data Migration Tool to achieve this?

Objective

How to migrate your IBM Cognos Controller (application repository) database from one database type to another.
  • For example, you can use it to migrate from an Oracle or SQL database to an IBM Db2 database (see further down for some restrictions)
This Technote will explain how to:
(A) Install the IBM Cognos Controller Data Migration Tool
(B) Use the tool to export IBM Cognos Controller data and structures from the 'source' database
(C) Use the tool to import the data and structures into the 'target' database
PLEASE NOTE:
  • To download the Data Migration Tool, you must request access by raising a ticket with IBM Controller Support
  • The tool does not transfer IBM Cognos Controller Lookup Tables
    • To transfer Lookup Tables, you must (afterwards) use the Export\Import Structures functionality in the IBM Cognos Controller rich client (menu option 'Transfer')
  • The tool does not transfer the configuration (enabled or disabled) of the audit logs
    • Therefore (after migrating to the new database), if you want to use the audit log functionality then you need to enabled it again (by launching the main/classic Controller client, and clicking "Maintain > System Audit Log > Configuration")
  • The tool is only designed to migrate Controller (application repository) databases (not other databases)
    • Specifically, it cannot be used to migrate a Cognos Analytics (CA) ContentStore database
    • TIP: For a method of migrating a CA contentstore database (to a different database type) see separate IBM Technotes #290381 & 291397.

Environment

Important:

The database type (that you export from and import into) must be based on the same version of IBM Cognos Controller
  • For example you can migrate from an IBM Cognos Controller 10.3.1 Oracle database to an IBM Cognos Controller 10.3.1 IBM Db2 database
  • However, you cannot migrate if the source version of IBM Cognos Controller is different from the target version (for example you cannot migrate an Oracle DB from IBM Cognos Controller 10.3.1 to SQL for IBM Cognos Controller 10.4.1)
Export is supported for the following platforms:
  • Oracle
  • MS SQL
  • IBM Db2
Import is supported for:
  • MS SQL
  • IBM Db2

Steps

(A) Installing the Cognos Controller Data Migration Tool

It is recommended to install the tool on the Cognos Controller application server where your universal data link (UDL) files are located. The tool uses the UDL files in the export and import process to extract the data and structure.
  • The server must be running a Microsoft Windows 64-bit operating system
  • It must also have Microsoft .NET Framework 4.7.2 installed
    • For example, if using Windows 2016 then you will need to install: NDP472-KB4054530-x86-x64-AllOS-ENU.exe
1. Download the 'Controller Data Migration Tool' install file
To obtain the download link, please raise a case (ticket) with IBM Support and provide your IBM WEB ID
  • Support will send you a direct download link (URL to IBM Fix Central) once your IBM WEB ID has been authorized
2. Decompress the install file
3. Double-click the ControllerDatabaseMigrationSetup.exe file
4. Follow the prompts in the installer to accept the license agreement and install the tool.
  • The default installation location is C:\Program Files\ibm\cognos\IBM Cognos Controller Database Migration Tool, which you can change during the installation

(B) Exporting data (from source database)

The export process extracts the database structure and data and creates a compressed (.zip) file. The compressed file is readable only by the IBM Cognos Controller Data Migration Tool.
Export is supported for Oracle, MS SQL and IBM Db2 databases.
Ensure that the server has enough disk space to store the exported data.
Important:
  • Ensure that you allow for an appropriate amount of down time for the database you are exporting. During the export, all users must be logged out of the database and no batch jobs can run against it. 
  • Back up the database before starting the export. 
1. From the Start menu, open ControllerDatabaseMigration :
image 4482
  • Alternatively, assuming you installed it into the default location, you can launch it directly from here: C:\Program Files\IBM\cognos\IBM Cognos Controller Database Migration Tool\bin
2. Click Export
3. Under Choose UDL, click Browse to navigate to a UDL file or type the path and name of the UDL file in the field.
  • Use the UDL file for the database that you are exporting from
4. Under Choose Export Folder, click Browse to navigate to a folder or to create a new folder. The exported data will be put in this folder
For example:
image 4489
5. Click Export Data.
6. A message displays asking you to confirm that you want to do the export. Click Yes.
A progress bar displays the progress of the export
image 4490
If a problem occurs, the export stops, and an error message is displayed in the progress window 
7. When the export is complete, the path to the export file is displayed in a message. Click OK
 
8. Optional: Click Save Log to save the messages in to a log file
image 4492
  
9. Click OK
10. Launch the IBM Cognos Controller rich client, connect to the source system, and export the Lookup Tables by doing the following:
  • Click "Transfer - Export Structures"
  • Tick the box 'Lookup Tables'
  • Choose a sensible folder (inside 'Directory') and press 'Run'
image 4488

  

(C) Importing data (into target database)

Import is supported for Microsoft SQL Server and IBM Db2 databases
The database that you are importing into must be in the same version of IBM Cognos Controller as the database that you exported from.
1. Create an empty database in the IBM Cognos Controller system you are importing data into.
  • Note: You can also use an existing Cognos Controller database that you want to overwrite
2. If you are importing into a MS SQL Server database, you must alter the database structure to accommodate the import. Run the following commands (modify the value for 'dbo' if necessary, to match your table owner's name):
ALTER TABLE dbo.satrglog ALTER COLUMN trigger_name varchar(40); ALTER TABLE dbo.xtaskset ADD [value] NVARCHAR(1000);
image 4494
3.  If you are importing in to an IBM Db2 database, you must alter the database structure to accommodate the import. Run the following commands:
ALTER TABLE wlookuptable ALTER COLUMN NAME SET DATA TYPE GRAPHIC(50);
Call Sysproc.admin_cmd ('REORG TABLE wlookuptable');

ALTER TABLE wimportspecification ALTER COLUMN NAME SET DATA TYPE GRAPHIC(50);
Call Sysproc.admin_cmd ('REORG TABLE wimportspecification');

4. From the Start menu, launch ControllerDatabaseMigration 
5. Click Import
6. Under Choose UDL, click Browse to navigate to a UDL file or type the path and name of the UDL file in the field. Use the UDL file for the database that you are importing into
7. Under Choose Archive, click Browse to navigate to the .zip file that contains the data to import
8. Click Import Data. A message displays with a warning that all data will be deleted from the database you are importing into.
  • Important: Verify that the UDL file you specified is the one for the database to import into
image 4520
9. Click Yes. The progress bar displays the progress of the import.
  • The progress window displays messages associated with the import.
  • If a problem with the import occurs, the import stops and an error message is displayed in the progress window 
10. If you want to cancel the import, click Cancel. The import stops and the progress of the cancellation process is displayed in the progress window
11. Optional: When the import finishes, click Save Log to save the messages in to a log file 
12. Open the Cognos Controller rich client and import the Lookup Tables by doing the following:
  • Click "Maintain - User - Single Mode", then click "Transfer - Import Structures"
  • Browse to the relevant folder, then tick the box 'Lookup Tables':
image 4645
  • Press 'Run'
 
13. If you are importing into a MS SQL Server database, you must run this command:
      ALTER TABLE dbo.xtaskset DROP COLUMN [value]; 
14. If you want to enable system audit logging (on the database you imported to) you must enable it manually
  • In the IBM Cognos Controller rich client, click "Maintain > System Audit Log > Configuration"

Additional Information

Below are some solutions to potential issues that might occur during the export or import process. 

Migrating a large database to an IBM Db2 server

The import might fail with an exception such as The transaction was rolled back because of error code: 964 or The transaction log for the database is full. SQLCODE=-964, SQLSTATE=57011.

This issue is caused by the transaction log configuration on the IBM Db2 database. To fix the problem, increase the size of the transaction log space.

Exporting from a Microsoft SQL Server database and importing in to an IBM Db2 database: Import fails when processing the xmapping table

You'll see a message related to this table in the log file.

This issue is caused by a difference in size for the ID column between IBM Db2 and the Microsoft SQL Server implementations. Run the following commands on the IBM Db2 database: 

ALTER TABLE xmapping ALTER COLUMN ID SET DATA TYPE DECIMAL(x,0);
Call Sysproc.admin_cmd ('REORG TABLE xmapping');

In the SQL Statement above, "x" represents the number of digits of the value returned by the following query run on the Microsoft SQL database:
select max(id) from fastnet.xmapping 

Exporting from a Microsoft SQL Server database and importing in to an IBM Db2 database: Import fails when processing the saxbostruc table

You'll see a message related to this table in the log file.

This issue is caused by a nullable column on Microsoft SQL Server that is set to not null in IBM Db2. Perform the following steps:

  1. Run the following commands on the IBM DB2 database: 
    alter table saxbostruc alter column unittype drop not null;
    Call Sysproc.admin_cmd ('reorg table saxbostruc');

     
  2. Perform the import as described in this document
     
  3. Run the following commands on the IBM Db2 database: 

    update saxbostruc set unittype=' ' ;

    (Ensure there is a space between the two single quotation marks.)

    alter table saxbostruc alter column unittype set not null;
    Call Sysproc.admin_cmd ('reorg table saxbostruc');

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"ARM Category":[{"code":"a8m0z0000004D3vAAE","label":"Database restore"}],"ARM Case Number":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.3.1;10.4.0;10.4.1;10.4.x","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
26 June 2020

UID

ibm10873220