Migrate to DB2 in Five Easy Steps

 

IBM's free Migration Toolkit makes short work of typically lengthy tasks.

Businesses today upgrade legacy systems to newer technologies for many business-driven reasons including getting more out of corporate data and better performance and reliability. In addition, as IT budgets are tightened, system consolidation is often seen as a way to improve operational efficiency and significantly reduce the total cost of ownership of the relational databases used to manage the data.

In spite of the business benefits, migration isn't an easy task because of the proprietary SQL features many RDBMSs use to achieve performance or other objectives. Applications that implement business logic in the database (stored procedures, functions, triggers, and so on) are difficult to migrate if they make use of proprietary extensions. 
 
An automated and comprehensive migration tool that customizes objects to your specifications can significantly reduce the costs associated with database migration. IBM’s Migration Toolkit (MTK) helps you view and manually customize database objects before deploying the objects to DB2. MTK supports the conversion of a significant portion of the features that exist in most RDBMSs. For complex features that require manual intervention, the tool can serve as a centralized interface for manual metadata manipulation.

Table 1 shows the different source and target DB2 combinations which MTK supports.

Target DBMS	Source DBMS
		Oracle 8i, 9i, & 10g	Sybase Adaptive Server Enterprise (ASE) 11, 12, & 12.5	Microsoft SQL Server 7, 2000 & 2005	Sybase SQL Anywhere 9	Informix Dynamic Server 7.3 & 9 and XPS versions 8.3, 8.4 & 8.5	MySQL 3.23.48 and higher
	DB2 UDB on Linux, Unix, & Windows –8.1, 8.2 and Viper	Supported	Supported	Supported	Supported	Supported	Supported
	DB2 UDB for iSeries V5R3 and V5R4	Supported	Supported	Supported	--	Supported	--
	DB2 UDB for z/OS 7 and 8	Supported	--	--	--	--	--
	Informix Dynamic Server 10	Supported	--	--	Supported	N/A	--

Table 1. MTK-supported source and target combinations.

The toolkit provides a five-step GUI interface that can help you convert source database objects to DB2. Each GUI panel in the main MTK interface represents a step in the conversion process. Figure 1 shows the five steps.

Figure 1:

Figure 1. The five-step MTK GUI.
The five-step migration process starts with the Specify Source step, which identifies the database being migrated. The source can be an Oracle, Sybase, SQLServer, MySQL, or IDS database. As you move through the process, you convert objects from the source into DB2 objects in the Convert step, then view and customize the conversion results in the Refine step. You repeat the Convert and Refine steps until the conversion results displayed in the Refine tab are accurate and complete. In the final two migration steps, you Generate Data Transfer Scripts and Deploy the database objects and data to the target DB2 database

I'll describe how each of the steps in detail. Before we start, you download and install the product (http://ibm.com/db2/migration/mtk). To start MTK, run MTKMain.bat (Windows) or MTKMain.sh (Unix). You'll see the product launch screen shown in Figure 2.

Figure 2: The MTK product launch screen.

Click on the Launch the Migration Toolkit button to start the five-step process.

Step 1: Specify Source

Before you start this step, you need to create a migration project that identifies the database being migrated and the DB2 you want to migrate to. Figure 3 shows the GUI panel for creating a migration project.

Figure 3. The Create new project panel.

After you create a migration project, you need to obtain the DDL statements that define the database objects that you want to migrate. These DDL statements will be converted to DB2 UDB syntax in the Convert step. The DDL statements are obtained on the Specify Source panel on the MTK GUI (see Figure 4).

Figure 4. The Specify Source panel.

Using an ODBC or JDBC connection, you can extract the DDL statements directly from the source database or you can import files already containing SQL statements. Figure 5 shows the screen you see after you press the Extract button.

Figure 5. The Extract screen.

The Extract panel shows all database objects in a hierarchical tree format. You can migrate all or a subset of database objects present in the source database.

Step 2: Convert

 

The Convert panel provides various options that are tailored based on the database being migrated (see Figure 6). For example, you can change the default mapping between a source data type and its target DB2 data type (Global Type Mapping button). In this stage, the source DDL statements are converted into DB2 DDL. The result of each conversion is a DB2 SQL file (.db2 extension) that contains DB2 DDL statements optionally preceded by the source DDL statements as comments. It also contains a list of errors that were identified during the conversion. You can view and edit this file on the Refine panel, which opens automatically after you press the Convert button.

Figure 6: The Convert panel.

Migration Step 3: Refine

You can use the Refine step to view the results of the conversion, locate warning/error messages, and make changes to the converted DDL

Figure 7: The Refine screen.

After refining the converted DDL, you repeat the Convert-Refine steps until you are satisfied with the results. You can then move on to the Generate data transfer scripts step or to the Deploy to DB2 step to execute the DB2 DDL statements.

Migration Step 4: Generate data transfer scripts

This step is used to prepare the necessary scripts required for moving the data present in the tables you are migrating to DB2. MTK generates Windows (*.bat) and Unix (*.sh) data transfer scripts that are used to create data load files in DB2 format. As in the Convert panel, various options are available to meet the needs of your particular migration.

Figure 8: Generating data transfer scripts.

Migration Step 5: Deploy to Target

The final step in the database migration process allows you to provide information about the DB2 server instance. Figure 9 shows the Deploy to Target panel. In this step, you can execute the DDL we obtained from the Convert step, extract data from the source database, load data into the target DB2 tables, or choose any combination of the three options.

Figure 9. Deploying to the target.

After you press the Deploy button, MTK generates a HTML report that gives extensive information about the success of your migration. You can browse through various logs that show which database objects and data have been successfully migrated and which have encountered warnings or errors. Figure 10 shows a sample HTML report.

Figure 10: A sample MTK-generated report.

And that's it. The MTK documentation (Help menu) provides an exhaustive description of the five-step migration process and any additional features you want to use.

MTK is under continuous development; new features and extended functions are added regularly. The latest version, 1.4.5.0, was released in March, 2006. For technical support, send an e-mail to mtk@us.ibm.com or post a message in the news group at news://news.software.ibm.com/ibm.software.db2.udb (prefix messages with "Migrate-team").