Migrate table data from SQLServer/Oracle to DB2 using IBMDataMovementTool

Based on our successful practices, This article summarizes how to use IBMDataMovementTool to migrate table data from SQLServerOracle to DB2, and provides corresponding solutions to common problems encountered during table data migration.

Based on our successful practices, This article summarizes how to use the IBM Data Movement Tool to migrate table Data from SQL Server/Oracle to DB2, and addresses common problems encountered during table Data migration, provide the corresponding solution.

SQL Server, Oracle, and DB2 are relational applications currently on the market. As application functions become increasingly powerful, both version upgrade and re-installation of the application require that the original data be retained. In this way, the migration between different types has become the focus of data migration in application functions. Some database migration tools (such as IBM Data Movement Tool) are available on the market ). The introduction to these tools on the internet is mostly based on the overall migration of data objects, that is, data migration based on data structure migration. However, some applications do not require data structure migration, but only table data migration. For example, Collaboration and Deployment Services (CADS), a product of ibm spss. CADS is an enterprise-level platform that can be widely used and deployed. It can be integrated with other predictive analysis products of ibm spss, such as ibm spss Modeler, ibm spss Statistics, or ibm spss Data Collection, to provide users with complete commercial solutions .), It supports SQL Server, Oracle, DB2, and other databases. When CADS is installed, the installer automatically creates database objects, including table structures, views, functions, and stored procedures. When CADS is installed and used, it adds, deletes, modifies, and queries table data. When considering data migration across database types for this product, the initial migration steps are as follows: create a source database-> install the source CADS-> source data-> Create a target database-> use a third-party data migration tool to migrate data from the source database to the target database-> install the target CADS. When the target CADS server is installed, views, functions, stored procedures, and other objects except table data are automatically rebuilt. Therefore, other objects do not need to be migrated using third-party data migration tools. For the table structure, the structure of the target table generated by a third-party migration tool and the structure of the table created by installing the target CADS server are very different (mainly the difference in data types). Therefore, if the target server is installed after the table structure and table data are migrated using a third-party migration tool, it is difficult to ensure that the target server correctly processes the data type. The potential problems are immeasurable. Therefore, to reduce the risks of data migration, CADS decided to use a third-party migration tool to only migrate table data. In this way, the cross-database migration steps of CADS become: create a source database-> install the source CADS database-> source data-> Create a target database-> install the target CADS server-> use a third-party migration tool to only migrate table data-> after migration to redeploy the CADS server to the target database. For CADS, redeployment is very simple. Therefore, using this solution for data migration can avoid potential problems caused by data types.

Currently, the architecture of many applications is similar to CADS, which requires you to consider how to use a third-party migration tool to only migrate table data. Based on our successful practices, This article summarizes how to use the IBM Data Movement Tool to migrate table Data from SQL Server/Oracle to DB2, and provide corresponding solutions to the common problems encountered during table data migration.

A Brief Introduction to the migration tool and detailed descriptions of table data migration steps

Introduction to IBM Data Movement Tool

This tool is a simple but powerful tool that supports migrating data from databases such as MySQL, PostgreSQL, Teradatra, SQL Server, Sybase, Oracle, DB2, and Access to DB2 databases. The graphic interface and command line are provided to facilitate operations.

This document describes how to use the IBM Data Movement Tool to migrate only table Data.

Use IBM Data Movement Tool to migrate table Data from SQL Server/Oracle to DB2

There are two ways to migrate Data using the IBM Data Movement Tool (IDMT): graphical interface and command line. This section describes the steps for migrating table data from SQL Server/Oralce to DB2 9.7 LUW based on these two methods.

Before using IDMT for data migration, you need to make some preparations so that IDMT can connect to the source and target databases.

Graphical interface-based data migration

Step 1-connect to the source database and target database

Open the IDMT graphic interface. On the "Extract/Deploy" Page, enter the corresponding information in the Source Database and Target Database. Note: In the Migration row, you only need to select the "DDL" and "Data" check boxes. Then, click Connect to ORACLE and Connect to DB2 to ensure the connection between the source database and the target database is successful. See figure 1.

Figure 1 data extraction and loading dialog box

Step 2-set parameters

If the migrated data contains Graphic and DBCLOB data, you must Set "Graphic" and "DBCLOB" to true on the "Set Params" page. Click "Save Params. See figure 2.

Figure 2 parameter Settings dialog box