Migrating InfoSphere Warehouse, V9.5.x to DB2 Warehouse, V10.5.x

Content

Same Computer vs Different Computer
The Warehouse program can be migrated to a new computer or you can migrate within the same computer. It is recommended, however, that you do not install in same directory as you did when V9.5.x of Warehouse was installed.

WebSphere Application Server
If you have WebSphere Application Server installed, you can reuse it with your installation of DB2 Warehouse, V10.5.x, but only if it is a supported version. For more information about the level of support for WebSphere Application Server, see the latest system requirements.

If you do choose to reuse WebSphere Application Server, perform the migration before starting the Warehouse migration.

Follow the below migration instructions for your version of WebSphere Application Server:

• WebSphere Application Server V7: http://pic.dhe.ibm.com/infocenter/wasinfo/v7r0/topic/com.ibm.websphere.base.doc/info/aes/ae/welc6topmigrating.html
• WebSphere Application Server V8: http://pic.dhe.ibm.com/infocenter/wasinfo/v8r0/topic/com.ibm.websphere.nd.multiplatform.doc/info/ae/ae/welc6topmigrating.html

Reuse of existing DB2
Do not reuse DB2 as your metadata repository. Version 9.5.x will soon be out of service and is not supported by DB2 Warehouse, V10.5.x. Follow the steps in this document to migrate to DB2 for LUW, Version 9.7.4 or 10.5.x.

Plan

1. Installing DB2 for LUW, V9.7.4.
2. Migrating the DB2 instance and databases on the data Installing DB2 for LUW, V9.7.4.server
3. (optional) Installing and Upgrading to DB2 for LUW, V10.5.x.
4. Installing DB2 Warehouse, V10.5.x.
5. Migrating the warehouse server metadata, V9.5-10.5.x.
6. Migrating the DB2 client instance and warehouse projects.

1. Installing DB2 for LUW, V9.7.4

If you are migrating from DB2 for LUW, Version 9.5.x, then you need to migrate to DB2 for LUW, V9.7.4 first.

To migrate to DB2 for LUW, V9.7.4, complete the following steps:

1. Check the installation requirements for DB2 for LUW, V9.7.4.
2. Install DB2 For LUW, V9.7.4 by one of the methods found in the IBM Knowledge Center.

2. Migrating the DB2 instance and databases on the data server, V9.5.x-V9.7.4

The steps that are provided in this procedure are general and might not be sufficient for your particular environment. For detailed instruction on migrating your DB2 instance and databases on the data server, go to Upgrade to DB2 For LUW Version 9.7 in the IBM Knowledge Center.

To migrate on the same computer you can use the migration method by issuing the db2iupgrade command, or the back up and restore method by running the backup, restore, and migrate commands.

If you are migrating to a different computer, you must use the backup and restore option.

Before you begin:

• To run the commands for backing up, restoring, and migrating DB2, you must be the instance owner.
• To run the db2iupgrade command, you must have root or administrator authority.

To migrate the DB2 instance and databases on the data server, choose one of the following options:

Option	Description
Backup and restore (different or same computer)	Back up the databases by issuing the following command in the command prompt of the instance of DB2, V9.5: db2 backup db database_name Restore the databases by running the following command in the command prompt of the instance of DB2, V9.7: db2 restore db database_name Migrate the existing databases by running the following command in the command prompt of the instance of DB2, V9.7: migrate database database_name
Migrate (same computer only)	Upgrade the existing database instance by going to the directory where DB2, V9.7, is installed and issuing the the following command: db2iupgrade instance_name Migrate the existing databases by running the following command in the command prompt of the instance of DB2, V9.7: migrate database database_name

3. Installing and Upgrading to DB2 for LUW, V10.5.x

Now that you have migrated to DB2 for LUW, V9.7.4, you can migrate directly to version 10.5.x.

Despite the fact that DB2 Warehouse, V10.5.x supports DB2 for LUW, V9.7.4, it is recommended that you upgrade to the latest version, V10.5.x.

To upgrade to DB2 for LUW, V10.5.x, complete the following steps:

1. Check the installation requirements for DB2 for LUW, V10.5.x.
2. Install DB2 for LUW, V10.5.x by one of the methods detailed here in the IBM Knowledge Center.
3. Upgrade to DB2 for LUW, V10.5.x by completing the procedure found in Upgrade DB2 Servers in the IBM Knowledge Center.

4. Installing DB2 Warehouse, V10.5.x

It is recommended that you do not accept the default name (DSSDB)for the metadata database, or any database names in the new DB2 Warehouse installation. This makes it easier to distinguish between the two installations.

1. Check the installation requirements for DB2 Warehouse, V10.5.x.
2. Download and prepare the installation image.
3. Install DB2 Warehouse V10.5.x by one of the methods detailed here.

5. Migrating the warehouse server metadata, 9.5.x-10.5.x

To migrate the server metadata, you will complete each of the following steps:

1. Migrate the Administration Console metadata
1. Migrating the data warehousing resources
2. Migrating the SQW metadata
3. Migrating the SQW schedules
1. Deploy the schedule export application
2. Remap the shared library references
3. Export and import the SQW schedules
2. Migrate the Cubing Services metadata
3. Migrate the Intelligent Miner metadata

5.1. Migrating the data warehousing resources

The command line tool is used to migrate all the definitions for database connections and system resources.

Before you begin:

• Catalog the 9.5.x version of the metadata database in the Administration Console.
• With administrator authority, start the Warehouse Administration Console, V10.5.x and create a connection to version 9.5.x of the SQW metadata database on the Manage Connections page. Make sure the user ID that creates the connection to version 9.5.x has READ or SELECT privileges on the metadata database. Otherwise the migration may fail, and in some case may result in inconsistent data.
• For connections that are managed by the WebSphere Application Server, the following information is migrated: connection name, database name, user ID, password, host name, port number, and associated driver information.
• If a connection that is managed by WebSphere Application Server refers to a non-DB2 for LUW driver, a template driver is added to the list of drivers in the Warehouse Administration Console, V10.5.x. You can review the drivers from the Manage Data Server Drivers page under the Manage Connections tab in the Warehouse Administration Console, V10.5.x. You need to specify the details of the template driver on the Manage Data Server Drivers page, including the driver class name, driver JAR file location, descriptions, and other properties. When template drivers are defined during migration, the template driver information is added to the log file.
• For connections that are NOT managed by the WebSphere Application Server, all the information that is related to connections is migrated.
• Ensure that there are no control flow processes running in the Warehouse Administration Console, V9.5.x.
• Suspend all SQW schedules.
• Ensure that WebSphere Application Server and the DB2 server are running.

To migrate data warehousing resources, complete the following steps:

1. From the ProductInstallHome/Migration/sqw directory, run one of the following command:
• On Linux or UNIX: /bin/sh ./metadbmigrate.sh -port PortNo -user User -password Password -migrate -data resources -fromdb FromDatabaseID -fromversion versionnumber
• On Windows: \metadbmigrate.bat -port PortNo -user User -password Password -migrate -data resources -fromdb FromDatabaseID -fromversion versionnumber
• Where
• PortNois the port number where the Warehouse Administration Console, V10.5.x is configured.
• Userand Password are the user name and password for the Warehouse Administration Console, V10.5.x.
• FromDatabaseID is the name of the connection to version 9.5.x of the SQW metadata database that you created in the Warehouse Administration Console, V10.5.x. For Linux and Unix operating systems, FromDatabaseID is case sensitive.versionnumber is the version of DB2 Warehouse from which you are migrating. In this case, type 9.5.
2. Check the log file in the ProductInstallHome/Migration/logs directory for detailed information about the success of the migration process. The name of the log file is migration_resources_YYYY_MM_DD_HHMMSS_log.txt, where:
• YYYY is the year
• MM is the month
• DD is the day
• HHMMSS is the time in hours, minutes, seconds

5.2. Migrating the SQW metadata

The command line tool is used to migrate all the metadata that is related to deployed applications, profiles, and statistics.

If you are migrating to a different computer, complete the following steps before you begin:

1. Go to the Warehouse Administration Console, Version 9.5.x and look at the SQW application properties.
2. Find the application home directory and the logs directory for the version 9.5.x applications and manually copy them to the other computer in the same absolute path.

To migrate the SQW metadata:

1. From the ProductInstallHome/Migration/sqw directory, run one of the following commands:
• On Linux and UNIX: /bin/sh ./metadbmigrate.sh -port PortNo -user User -password Password -migrate -data sqw -fromdb FromDatabaseID -fromversion versionnumber
• On Windows: \metadbmigrate.bat -port portNo -user User -password Password -migrate -data sqw -fromdb FromDatabaseID -fromversion versionnumber
• Where:
• PortNo is the port number where the Warehouse Administration Console, V10.5.x is configured
• User and Password are the user name and password for the Warehouse Administration Console, V10.5.x.
• FromDatabaseID is the name of the V9.5.x metadata database cataloged in the Warehouse Administration Console, V10.5.x. For Linux and Unix operating systems, FromDatabaseID is case sensitive.
• versionnumber is the version of DB2 Warehouse from which you are migrating. In this case, type 9.5.
2. Check the log file in the ProductInstallHome/Migration/logs directory for detailed information about the success of the migration process. The name of the log file is migration_sqw_YYYY_MM_DD_HHMMSS_log.txt, where:
• YYYY is the year
• MM is the month
• DD is the day
• HHMMSS is the time in hours, minutes, seconds

Note: The log files that are generated in DB2 Warehouse, V9.5.x from the application execution run time are not migrated. New log files are generated when you run applications in DB2 Warehouse, V10.5.x. To refer to the DB2 Warehouse, V9.5.x application execution log files, you must go to the logs directory that you specified during the SQW application deployment and view the log files with a web browser (Internet Explorer or Firefox).

5.3. Migrating the SQW schedules

5.3.1. Deploying the schedule export application

Deploying the schedule export application exports the active schedules from version 9.5.x of the Warehouse Administration Console

1. Log in with administrator authority to the WebSphere Administrative Console that is associated with version 9.5.x of the Warehouse Administration Console.
2. Click Applications > Enterprise Applications.
3. Click DWEAdminConsole, and then click Update.
4. Complete the steps of the wizard:
1. Click Replace or add a single module.
2. Specify the path beginning with the installed application archive file to the module to be replaced or added by typing SchExp.war.
3. Specify the path to your local file system by browsing to ProductInstallHome/Migration/export/SchExp.war file on your local file system.
4. Type /SchExp for Context root.
5. Review the deployment results information. If there are no errors, click Save. If an error occurs, consult the WebSphere Application Server documentation.

5.3.2. Remapping the shared library references

When you deploy the schedule export application in WebSphere Application Server, the shared library references are removed. You need to remap the shared library references so that version 10.5.x of the Warehouse Administration Console functions correctly.

1. Log in with administrator authority to the WebSphere Administrative Console that is associated with the Warehouse Administration Console, Version 9.5.x.
2. Click Applications > Enterprise Applications.
3. Click DWEAdminConsole.
4. On the Configuration page, in the References section, click Shared library references.
5. On the Shared library references page, select the DWEAdminConsole check box, and then click Reference shared libraries.
6. On the Shared library mappings page, add dslib, dweualib, cslib, dwecustlib, dwecorelib, and db2lib libraries to the selected libraries.
7. On the DWEAdminConsole page, click Save to save the shared library mappings.

5.3.3. Exporting and importing the SQW schedules

To export and then import the SQW schedules from Warehouse Administration Console, Version 9.5.x to the Warehouse Administration Console, V10.5.x, run the schedule migration tool from the application server installation of DB2 Warehouse, V10.5.x.

1. With administrator authority, go to the DB2 Warehouse, V10.5.x scheduler migration directory: ProductHome/migration/export.
2. Run the schedule migration tool.
• On Linux and UNIX: ./schedulermigrat.sh -hostV95 host_name_V95 -portV95 port_number_V95 -port port_number -user user_name -password password
• On Windows: schedulermigrat.bat -hostV95 host_name_V95 -portV95 port_number_V95 -port port_number -user user_name -password password
• Where:
• host_name_V95 is the host name or IP address of the Warehouse Administration Console, V9.5.x. If V9.5.x and V10.5.x of the Warehouse Administration Console are on the same computer, use localhost.
• port_number_V95 is the port number that is used by the default_host under the 'dwe' profile that is created in V9.5.x of the Warehouse Administration Console. The default_host port is usually 9080. If you are not sure what the port number is, you can find the port number for the default_host in the portdef.props file, which is in the ${WAS_INSTALL_ROOT}/profiles/dwe/properties directory. The property keyword is WC_defaulthost.
• port_number is the port number for the Warehouse Administration Console, V10.5.x.
• user_name and password are the administrator user name and password for the Warehouse Administration Console, V10.5.x.
3. Check the log files in the ProductInstallHome/Migration/logs directory for detailed information about the success of the migration process.
• scheduleFileName.txt: Records the file name of the latest migrated scheduler data file, the number of schedules, and the time stamp of the schedule migration.
• scheduleMigration_YYYMMDDHHMM.txt: Records all the messages (information, warning, and error) that occurred during the schedule migration.
• scheduleData_YYYMMDDHHMM.txt: Records the schedule data that was migrated.
• where:
• YYYY is the year
• MM is the month
• DD is the day
• HHMM is the time in hours, minutes, seconds
4. After the schedules are migrated, manually enable the schedules in version 10.5.x of the Warehouse Administration Console.

5.4. Migrating Cubing Services metadata

To migrate the Cubing Services metadata to version V10.5.x of DB2 Warehouse, run the Cubing Services migration tool.

Before you begin:

• The cube server data source file, csdatasource.xml, is not supported and will not be migrated. This has no effect on DB2 Warehouse, V10.5.x.
• Ensure that all DB2 Warehouse, V9.5.x cube servers are stopped
• If you are migrating to a different computer, copy the entire V9.5.x Cubing Services directory, ProductInstallHome/CubingServices, and paste to the new computer.

To migrate the Cubing Services:

1. With administrator authority, go to the DB2 Warehouse, V10.5.x Cubing Services bin directory. For example, the default path is:
• On Windows: C:\Program Files\IBM\ISWarehouse\CubingServices\bin\
• On Linux and UNIX: /opt/IBM/ISWarehouse10/CubingServices/bin/
2. Run the Cubing Services migration script:
• On Windows: migrate.bat V9.5_ProductInstallHome/Cubing Services V10.5_ProductInstallHome/Cubing Services
• On Linux and UNIX: ./migrate.sh V9.5_ProductInstallHome/Cubing Services V10.5_ProductInstallHome/Cubing Services
• Where:
• V9.5_ProductInstallHome/Cubing Services is the InfoSphere Warehouse V9.5.x Cubing Services home directory.
• V10.5_ProductInstallHome/Cubing Services is the DB2 Warehouse V10.5.x Cubing Services home directory.

Note: Do not include a slash (/) at the end of either of the Cubing Services home directories in the command. If either Cubing Services home directory ends with a slash, the command fails with errors.

The Cubing Services metadata in DB2 Warehouse, V10.5.x now contains the following items:

• Data sources with encrypted passwords
• Cubing Services release number
• Cubes, including metadata and settings
• Cube server host names

In DB2 Warehouse, V9.5.x, you defined cubes and cube servers with the localhost host name. However, V10.5.x of DB2 Warehouse this function is not available. Your existing cubes and cube servers that were defined with the localhost host name are now defined as the machine name (for example, myMachine.mycompany.com).

During the migration process, some of the parameters within the Administration Console are set to NULL. You must set the values for these parameters within the Administration Console, Version 10.5.x.

1. Open the Cubing Services page.
2. Click Manage Cube Servers.
3. Click the cube server that you want to edit.
4. Click the Edit icon and edit the parameters.
5. In the directory Install_home/Cubing Services of the previous release, copy the folder of the cube server you are editing and paste it into the directory Install_home/Cubing Services of the current release.

5.5. Migrating the Intelligent Miner metadata

To migrate the Intelligent Miner metadata, you must first migrate to V9.7.4 and then you can migrate to V10.5.x. Any attempt to migrate directly from V9.5.x to V10.5.x fails.

• Ensure that the databases are enabled. Do not disable the databases before you migrate them.
• Do not change the database manager configuration parameters (DBM CFG) or DB2 registry settings.

To migrate the Intelligent Miner metadata, complete the following steps:

1. Migrate the DB2 instances to DB2 V9.7.x. For more information on migrating instances, see Migrating Intelligent Miner in the DB2 V9.7 Information Center.
2. Re-enable your databases to automatically move the metadata. For more information on enabling databases, see the Enabling a database in the DB2 V9.7 Information Center.
   
   Note: When you enable the database, you must enable it in the same mode that you used when you previously enabled the database. For example, if you originally enabled the database in fenced mode, you must enable it again in fenced mode. If you do not know the mode, use the idmcheckdb command.
3. Migrate the V9.7.4 DB2 instances to V10.5.x. For more information on migrating instances, see Migrating Intelligent Miner in the DB2 V10.5 Information Center.
4. Re-enable your databases to automatically move the metadata.. For more information on enabling databases, see Enabling a database in the DB2 V10.5 Information Center.

6. Migrating the DB2 client instance and warehouse projects

If you are migrating the warehousing projects on the same computer, you need to first migrate the DB2 client instance. If you are migrating the warehousing projects to another computer, you need to first copy your workspace to the other computer.

After you Migrate the DB2 client instance or Copy your workspace, you then migrate the warehousing projects from Design Studio.

Migrate the DB2 client instance (SAME COMPUTER)

If your client is on Windows, you also need to set the DB2 instance and DB2, Version 10.5.x as the default client.

To migrate the DB2 client instances:

1. Go to the directory where DB2, 10.5.x is installed. For example, the default directory is:
• On Windows: C:\Program Files\IBM\SQLLIB
• On Linux and UNIX: /opt/IBM/db2/V10.5
2. Run the following command: db2iupgrade instance_name
3. On Windows: Set the DB2 instance and DB2, Version 10.5.x client as the default client. From the Start menu, click IBM DB2 <DB2COPY> Default DB2 and Database Client Interface Selection Wizard.

Copy your workspace (DIFFERENT COMPUTER)

If you are migrating the data warehousing projects to another computer, first copy the Design Studio workspace to the other computer.

1. Find the directory of the Design Studio, Version 9.5.x workspace that contains the projects that you want to migrate, and then copy the entire workspace directory. To find the directory of a workspace in version 9.5.x of the Design Studio, click File > Switch Workspace.
2. Paste the workspace directory into the directory on the other computer where the client of DB2 Warehouse, 10.5.x is installed.

Migrating the data warehousing projects from Design Studio

You do not need to migrate all of the projects at the same time. You can run the wizard multiple times on different data warehousing projects. For example, if you have 100 data warehousing projects to migrate, you can choose to migrate 50 projects in the first run, and then run the migration wizard again to migrate the remaining 50 projects.

Restrictions for Version 10.5.x validation and SQL and DDL files

• The validation rules in Design Studio, V10.5.x are stricter than in Design Studio, V9.7. For example, an operator such as the Roll-out operator that had missing properties would validate successfully in Design Studio, V9.7. In Design Studio, 10.5.x, the missing property values must be fixed to validate successfully.
• In version 10.5.x of the Design Studio, the build profile no longer packages SQL and DDL files for deployment. If you want to run any SQL and DDL files, you must run them manually or create a control flow to run them.
• If the data warehousing projects are stored in a version control system, such as CVS, ClearCase, and so on, check out the files and copy them to the local file system. After you run the Migration wizard, you can check in the migrated files again, if needed.

Metadata and view files that are consolidated in V10 and after

• Previous to Version 10, each warehouse flow in the Design Studio is associated with a metadata file and a view file for internal XML serialization. In Version 10, these 2 files are combined into one metadata file. You can either run the migration wizard to update your version 9.5.x flows or they are automatically combined when you open them. In each case, the obsolete view file is deleted. However, if the file is locked by your version control system, you might see error messages that can be ignored.

Restrictions for migrating secure operators

Support for password-less FTP processing in Secure FTP operators and Secure command operators causes some restrictions for migration. Therefore, when you migrate your machine resources to version 10.5.x there are certain changes in the properties.

• In the previous release, the Secure FTP operators and Secure Command operators accept variables for private key file, passphrase, and known hosts. In V10.5.x, these properties are stored in the machine resource profile, which does not accept variables. Therefore, the current fixed value of private key file, passphrase, and known hosts, at the time of migration, are set in the machine resource profile of V10.5.x.
• If the machine resource is named secureFTP in the previous release, then the machine resource is renamed secureFTP_ssh after migration to version 10.5.x.
• In the previous release, there is no machine type that is associated with a machine resource. Therefore, in version 10.5.x all migrated machine resources are designated machine type SSH Server.
• All of the other machine resources properties from the previous version are migrated to version 10.5.x.
• In the previous release, if you have two or more machine resources with the same host name but with different properties, then only one is migrated to the new SSH server machine resource in version 10.5.x.

To migrate data warehousing projects either on a new computer or within the same computer:

1. Start version 10.5.x of the DB2 Warehouse Design Studio by using a new workspace. This workspace is used for migration purposes only.
2. From the main menu, click Data Warehousing > Migration. The Migration wizard opens.
3. Complete the wizard to migrate the data warehousing projects. The data warehousing projects are migrated to the destination directory. A Migration Log window opens and displays the migration summary.
4. Prepare the Design Studio, 10.5.x to use the migrated projects:
1. Specify a new workspace when you open the Design Studio 10.5.x.
2. Click File > Import, and then click Existing Projects to Workspace.
3. Complete the Import wizard to import the migrated projects.
4. In the Data Source Explorer, right-click Database Connections, and then click New.
5. Complete the New Connection wizard to create a database connection.