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
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.
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.
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:
- 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.
- Migration: During this phase, conversion specialists or DBAs convert to the target database and deploy the scripts and data directly to the target database.
- 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).
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
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.
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
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
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
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 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
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 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
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.
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.
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.
MTK support requests, product design issues, and user feedback are welcomed at email@example.com.
- Get the facts from our customers, partners, and independent consultants on DB2 technology leadership and innovation at:
- Visit the migration Web site for up-to-date information on the latest migration issues and product support at:
- Read about other customers who have successfully migrated to DB2 from competitive RDBMS engines at:
- Obtain migration information from IBM at:
Contacts for the Americas, AP, and EMEA:
Manager, IBM Data Management ISV Software Sales
Manager, DB2 Application Development Services
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.
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 firstname.lastname@example.org to offer feedback on this article or the Migration Toolkit product.