Automate the Migration to DB2 and IDS Using the IBM Migration Toolkit

The no-charge IBM® Migration Toolkit (MTK) can simplify and improve your migration to DB2® Database for Linux, UNIX, and Windows, DB2 on iSeries, DB2 on z/OS, and Informix® Dynamic Server (IDS). This migration utility provides a wizard, an easy-to-use graphical interface with only five steps, as well as a command line interface to help you quickly convert existing Oracle, Sybase Adaptive Server Enterprise (ASE), Sybase SQL Anywhere (ASA), and Microsoft® SQL Server database objects to DB2 Database for Linux, UNIX, and Windows, DB2 on iSeries, DB2 on z/OS, and IDS.

Alex Jarzebowicz (mailto:jarzebow@us.ibm.com), Software Engineer , IBM Silicon Valley Laboratory

Alex Jarzebowicz is a software engineer in the Business Intelligence - User Technology Group at the IBM Silicon Valley Lab in San Jose, CA. He specializes in information development and user-centered design. Your product experiences and feedback are greatly appreciated. Contact jarzebow@us.ibm.com to offer feedback on this article or the Migration Toolkit product.



23 August 2007 (First published 05 September 2002)

Also available in Russian

Introduction

With MTK, you can automatically convert data types, tables, columns, views, indexes, stored procedures, and triggers into equivalent target database objects. MTK provides database administrators (DBAs) and application programmers with the tools needed to automate previously inefficient and costly migration tasks. You can reduce downtime, eliminate human error, and cut back on person hours and other resources associated with traditional database migration by using the following features found in the MTK:

  • Extract database metadata from source DDL statements using direct source database access (JDBC™/ODBC) or imported SQL scripts
  • Automate the conversion of database object definitions, including stored procedures, triggers, packages, tables, views, indexes, and sequences
  • Access helpful SQL and JavaTM compatibility functions that make conversion functionally accurate and consistent
  • View and refine conversion errors
  • Efficiently implement converted objects using the deployment option
  • Generate and run data movement scripts
  • Track the status of object conversions and data movement, including error messages, error location, and DDL change reports, using the detailed migration log file and report

Consider MTK in your database solution decision

The decision to move to a new database requires careful consideration of the many factors that will affect your business. One of these factors is the allocation of resources required to get the database objects, stored procedures, and physical data from the original database to the target database. The person-hours, downtime, and possibility of human error associated with database migration can weigh heavily on the return on investment (ROI) for your overall data management upgrade. The availability of a comprehensive migration tool that helps you customize objects to your business migration specifications can significantly reduce the costs associated with database conversion. While several simple tools exist that offer resources for database migration, very few allow you to customize DDL objects within the tool interface while supporting procedural languages, such as T-SQL, PL/SQL, or SPL. Nor do they support the refinement and conversion of complex objects, such as triggers and stored procedures. MTK stands out among other migration products by helping you to view and manually customize database objects before you deploy the objects to the target database.

MTK supports the conversion of a significant portion of the features that exist in most relational database management systems (RDBMS). However, with some migrations, there can be complex RDBMS features (such as extensibility objects and collections) that cannot be handled by a migration utility and will require hands-on modification.

Figure 1
Conversion efficiency relative to RDBMS complexity

As the complexity of the RDBMS features being converted increases, the level of automation decreases and additional manual intervention is required. The value of MTK is realized in such a complex migration environment in that the tool can serve as a centralized interface for manual metadata manipulation. Additionally, MTK can automate the conversion of the remaining database objects and let you focus your valuable resources on the most complex RDBMS features.


MTK and the overall migration strategy

Database migration is a multiphase process that requires extensive planning and project management. It is important to understand the general migration process to know how and where MTK can fit into your strategy. Generally, the migration process consists of several phases:

  1. Pre-migration planning and assessment: In this phase, you define the scope of your migration and assess resource planning and scheduling tasks such as risk evaluation, proof of concept, data backup, staging, timeline planning, and migration skills training.
  2. Migration: During this phase, conversion specialists or DBAs convert to the target database and deploy the scripts and data directly to the target database.
  3. Post Migration testing and validation: This final migration phase entails tuning the target database, migrating applications, setting up the production environment, performing testing, and training users on the target database.

MTK is designed to be used in the second phase of migration; this phase involves the actual conversion of database objects and deployment of those objects and data to the target database. While MTK will help with the most critical phase of your migration, it is essential that the other phases are implemented correctly to ensure a successful and cost-effective migration. IBM provides a wealth of migration experience that can help you outline and implement all of the phases of your migration strategy (see the Additional migration resources section at the end of this article for contact information).


How MTK works

The MTK interface presents five iterative steps (see Figure 2); each page in the MTK interface represents a step in the conversion process.

Figure 2. MTK's five steps
MTK's five steps

The five-step migration process starts with the Specify Source step which is used to identify the source of the objects to be converted.As you move through the process, you convert objects from the source input into target database 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 that are 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 database.

The migration environment

When you set up an environment in which to migrate, you will specify the operating platforms on which the database server is running. The source database manager exists on a particular platform, the target database could exist on another, and MTK could be on a Microsoft Windows NT or Windows 2000 platform.

You can also install MTK on the same system as your target database. The source database is accessed with either ODBC or a JDBC native driver. The toolkit can also import from a file, if the file contains SQL that has been exported in the correct format (standard SQL - non-proprietary format) See Figure 3.

Figure 3. Using ODBC or JDBC to access the database
Using ODBC or JDBC to access the database

It is possible to deploy the database to a system platform other than those specifically supported by MTK. During deployment, MTK executes a set of batch files that contain the necessary commands for deployment. If you know the shell command syntax of the target system, you can copy the files to the system, modify them as necessary, and execute them yourself. For example, you can manually modify and locally run the UNIX® (*.sh) scripts generated during the migration so that the deployment and data movement is localized on a target UNIX machine.

Figure 4. Using batch files for deployment
Using batch files for deployment

The five iterative steps of the MTK interface are described below:

Step 1: Specify source
After a migration project is created or opened, you can start the migration process. Your first step is to obtain the DDL source files that will be converted to the target database. This step is done on the Specify Source page.

Figure 5. Specify source
Specify source

Using an ODBC or JavaTM connection, you can extract the source directly from the source database or you can import scripts. For example, when importing scripts, you can import source scripts from previous extractions or from a source library. You can also specify and convert multiple source files at the same time.

Step 2: Convert
In the Convert step, you can choose from a list of formatting options that affect the conversion output. The Convert step can also be used to change the default mapping between a source data type and its target database data type.

After selecting from the conversion options, the source DDL statement is converted into target database DDL. The result of each conversion is a DB2 file (.db2) or an IDS file (.ids) and a report file (.rpt), each containing the prefix that you specified in the Prefix for generated files field. The target database SQL file contains the target database DDL statements that were created during the conversion, normally preceded by the source DDL statements as comments. The report file contains a list of errors that were identified during the conversion. You can view and edit these files on the Refine page, which opens automatically after the objects are converted.

Figure 6. Step 2, Converting
Step 2, Converting

Step 3: Refine
Use the Refine step to view the results of the conversion, locate error messages, and make changes to the converted DDL.

Figure 7. Step 3, Refining
Step 3, Refining

After refining the converted data, you must return to the Convert step to apply the changes. When you perform the conversion step again, the converter merges the refined changes with the source DDL statements that were originally extracted to produce updated target database and XML metadata (a representation of source objects as specified by the DDL). The original DDL statements are not changed. Repeat the Convert-Refine process until you are satisfied with the results.

You can use other tools such as the Log or Reports to help you refine the conversion. After you have refined the target database DDL statements to your satisfaction, you can move on to the Generate data transfer scripts step to prepare the data transfer scripts, or the Deploy to Target step to execute the target database DDL statements.

Step 4: Generate data transfer scripts
In this step, Windows NT (*.bat) and UNIX (*.sh) data transfer scripts are generated that will be used to create data files in the target database format. Several options allow you to specify load and import, file format, file type modifier, parameter, and indexing options to meet the needs of your particular migration.

Figure 8. Step 4, Generating scripts
Step 4, Generating scripts

Step 5: Deploy to target
The Deploy step is used to select the method for deploying data to the target database. In this step, you can execute the DDL to create the metadata objects, extract data from the source database, load data into the target database tables, or choose any combination of the three options.

Figure 9. Step 5, Deploying
Step 5, Deploying

MTK provides these different options to accommodate many possible system environments. One common use of these features is to use the Deploy options so that you can execute the DDL statements during the day and deploy the actual data at night when network usage is low.

If you need to convert and refine on a system other than the one where the target database resides, you can copy MTK and its project directory to the target database system and deploy it locally. MTK generates scripts for both UNIX and Windows platforms.


Sample database migration using MTK

We have a related tutorial that demonstrates the use of the MTK to convert a database model from a Microsoft SQL Server DDL script to a DB2 for Linux, UNIX, and Windows database. The converted objects will include a stored procedure. You can find the tutorial at the following URL: http://www.ibm.com/developerworks/db2/library/tutorials/0209jarzebowicz/index.html.


How do I get MTK?

The latest versions of MTK can be downloaded free of charge at: http://www.ibm.com/db2/migration/mtk/

The product is under continuous development and new features and extended functionality are added on a regular basis.


Technical support

MTK support requests, product design issues, and user feedback are welcomed at mtk@us.ibm.com.


Additional migration resources

DB2 migration sales experts

Contacts for the Americas, AP, and EMEA:
See http://ibm.com/db2/migration/

DB2 migration pre-sales support for partners reselling DB2

Steve Tessler
Manager, IBM Data Management ISV Software Sales
sltessl@us.ibm.com
516-349-3879

DB2 migration pre-sales support for partners not reselling DB2

Zamil Janmohamed
Manager, DB2 Application Development Services
zamil@ca.ibm.com
416-313-1130

Acknowledgments

The author would like to thank Patrick Dantressangle, Gary Faircloth, Amyris Rada, and Kathleen Nojima for their technical expertise and helpful contributions to the article and tutorial.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, IBM i
ArticleID=14203
ArticleTitle=Automate the Migration to DB2 and IDS Using the IBM Migration Toolkit
publish-date=08232007