 | Level: Intermediate Daniel Duda, Software Engineer, IBM
Tawanna Harris, Software Engineer, IBM
Adekunle O Adio, Software Engineer, IBM
Shawn Moe, Software Architect, IBM
07 Feb 2008 Recent updates to the IBM® Migration Toolkit empower you with new migration capabilities. Learn how to take advantage of some of the newly introduced features, including enhancements to the command line interface functionality and new SQL translation capabilities.
Introduction
The IBM Migration Toolkit (MTK) provides capabilities to ease database migrations from Sybase, SQL Server, MySQL, and Oracle to DB2® and Informix® Dynamic Server (IDS). MTK is available for your use without charge. MTK versions 2.0.4.0 and 2.0.5.0 introduced several new features that make your migrations even easier. This article describes the enhancements to the command line interface (CLI) functionality along with some of the new SQL translation capabilities introduced in these versions.
The CLI was introduced in MTK 2.0.3.0 as a handy way for experienced MTK users to automate database migrations using a configuration file to describe the desired migration options and the command line arguments to drive the migration process.
At present (MTK 2.0.5.0), CLI can be used with migrations to DB2 data servers on Linux®, UNIX®, Windows® or zSeries® platforms, and IDS data servers.
Check out the introductory article listed in the Resources section for additional information about CLI arguments and the description of the configuration file format.
The release of IBM Migration Toolkit versions 2.0.4.0 and 2.0.5.0 bring about valuable feature enhancements. In case you're interested in DB2 Viper 2 compatibility features, you'll want to learn how these new versions take advantage of them. In addition, they extend the CLI to allow multiple source files and their relational dependencies as context files to be incorporated into a single conversion section. Please follow along with us as we provide explanations and examples, and find out how your capabilities are expanded with these new features.
Compatibility conversion
Database migration generally involves the transformation of SQL statements from source database syntax to compatible syntax on DB2 or IDS. The DB2 Viper 2 release (DB2 Version 9.5 for Linux, UNIX, and Windows) introduced many new compatibility features to greatly ease the migration effort of certain vendor-specific SQL statements when migrating to this version of DB2. MTK 2.0.4.0 has been updated to work in conjunction with the new DB2 release when processing these SQL statements.
Oracle ROWNUM, CONNECT BY, CURVAL and NEXTVAL are a few of the new DB2 compatibility features that are now supported by MTK. Please reference the MTK documentation for more details. Let's look at the new DB2 and MTK support for these constructs.
ROWNUM
A ROWNUM pseudo-column can be implemented to limit the result set returned by a query. When MTK encounters a statement containing the ROWNUM pseudo-column, the translation is now done without sub queries, FETCH FIRST or ROWS ONLY statements.
Listing 1a. ROWNUM pseudo-column
CREATE PROCEDURE P3 AS
BEGIN
DELETE FROM TAB1 WHERE C1 < C2 AND ROWNUM BETWEEN 1 AND 2;
UPDATE TAB1 SET C1 = 1 WHERE C2 < 2 AND ROWNUM <= 3;
UPDATE TAB1 SET C1 = ROWNUM;
END;
|
Listing 1b. MTK translation
CREATE PROCEDURE P3()
LANGUAGE SQL
BEGIN
DELETE FROM TAB1 WHERE C1 < C2 AND ROWNUM BETWEEN 1 AND 2;
UPDATE TAB1 SET C1 = 1 WHERE C2 < 2 AND ROWNUM <= 3;
UPDATE TAB1 SET C1 = ROWNUM;
END!
|
Hierarchical queries
MTK now provides support for translating the Oracle CONNECT BY ... START WITH ...clause. MTK translation of this syntax is currently only supported for the Oracle 9i syntax.
Listing 2a. CONNECT BY
SELECT SYS_CONNECT_BY_PATH (LAST_NAME, '>'),
LAST_NAME,
EMP_MGR_ID,
LEVEL
FROM EMPLOYEES
CONNECT BY PRIOR EMP_ID = EMP_MGR_ID
START WITH EMP_MGR_ID IS NULL;
|
Listing 2b. MTK translation
SELECT SYS_CONNECT_BY_PATH(LAST_NAME, '>'),
LAST_NAME,
EMP_MGR_ID,
LEVEL
FROM EMPLOYEES
START WITH EMP_MGR_ID IS NULL
CONNECT BY PRIOR(EMP_ID) = EMP_MGR_ID!
|
NEXTVAL/CURRVAL for sequences
Sequence look-ups are now more compatible than ever. MTK translates NEXTVAL and CURRVAL as is, keeping in mind the implementation differences in DB2 (as indicated in the listings below).
Listing 3a. NEXTVAL/CURRVAL
INSERT INTO TAB1(C1,C2) VALUES (1,ID_C.NEXTVAL);
INSERT INTO TAB1(C1,C2) VALUES (2,ID_C.CURRVAL);
INSERT INTO TAB1 VALUES (ID_C.CURRVAL, ID_C.NEXTVAL);
INSERT INTO TAB1 VALUES (ID_C.NEXTVAL, ID_C.NEXTVAL);
INSERT INTO TAB1 VALUES (ID_C.CURRVAL, ID_C.CURRVAL);
SELECT ID_C.NEXTVAL FROM DUAL;
SELECT ID_C.CURRVAL FROM DUAL;
|
Listing 3b. MTK translation
INSERT INTO TAB1 (C1,C2) VALUES (1,ID_C.NEXTVAL )!
INSERT INTO TAB1 (C1,C2) VALUES (2,ID_C.CURRVAL )!
INSERT INTO TAB1 VALUES (ID_C.NEXTVAL ,ID_C.NEXTVAL )!
INSERT INTO TAB1 VALUES (ID_C.NEXTVAL ,ID_C.NEXTVAL )!
INSERT INTO TAB1 VALUES (ID_C.CURRVAL ,ID_C.CURRVAL )!
SELECT ID_C.NEXTVAL FROM DUAL!
SELECT ID_C.CURRVAL FROM DUAL!
|
Creating a compatibility project
All work performed by MTK is done as part of a "migration project." Hence the first step of any migration requires the creation of a MTK project to store all of the related information about the particular migration at hand. This can be done by using the MTK GUI or through the MTK CLI.
Example A: Creating an Oracle compatible project through the MTK GUI
When creating a compatibility project from the MTK GUI, you choose the source database and target platform from the dropdown lists, as shown in Figure 1:
Figure 1. New project
Example B: Creating an Oracle compatible project through the MTK CLI
Using the CLI to create a compatibility project requires that the SRCDBTYPE and TRGTDBTYPE attribute values be specified as indicated in the configuration file snippet below:
MTK configuration file snippet: Project element
...
<PROJECT
NAME="migration1" DIRECTORY="C:\MTK\Projects"
SRCDBTYPE="oracle" TRGTDBTYPE=" DB2LUW_V9.5C">
...
|
Once you have created a project, you are free to include a wide variety of operations and functionality within. Take a look at what is new that you can do with CLI.
The CLI -- working with multiple source files, context files and DB2 table spaces
MTK continues to enrich its unattended execution capabilities by enhancing the CLI with powerful features. This section examines the newly added CLI features using examples.
Example 1: Converting multiple source files
The CLI now provides support for converting multiple SQL files together. Previously, each SQL file required its own conversion section in the configuration file. Let's assume you have two SQL files named "tableset1.sql" and "tableset2.sql." These files contain multiple Oracle table definitions. Suppose you want to use the CLI to import these files, translate them to DB2 syntax, and deploy to a local DB2 database named "myDB". You have set up your machine for the migration by installing MTK, the Oracle JDBC Driver, and the DB2 Viper 2 server on your Windows machine. The CLASSPATH and PATH variables are also correctly set.
Next, you need to create the MTK configuration file. Name the file "example1.xml" and save it in the MTK installation directory. The configuration file contains the following:
MTK Configuration File: example1.xml
<?xml version="1.0"?>
<!DOCTYPE MTK SYSTEM "mtk.dtd">
<MTK>
<PROJECT
NAME="migrate1" DIRECTORY="C:\MTK\projects"
SRCDBTYPE="oracle" TRGTDBTYPE="DB2LUW_V9.5C">
<SPECIFY_SOURCE>
<IMPORT>C:\sqlscripts\tableset1.sql</IMPORT>
<IMPORT>C:\sqlscripts\tableset2.sql</IMPORT>
</SPECIFY_SOURCE>
<CONVERSIONS>
<CONVERSION>
<CONVERT
SRCSQLFILES="tableset1.sql, tableset2.sql">
</CONVERT>
<DEPLOY_TO_TARGET
TRGTDBNAME="myDB"
TRGTUSR="db2user"
TRGTPWD="db2pwd"
LAUNCHSCRIPT="Y"
DEPLOYMTKUDF="N"
RECREATE="Y"
></DEPLOY_TO_TARGET>
</CONVERSION>
</CONVERSIONS>
</PROJECT>
</MTK>
|
You are now ready to run the CLI command. The goal is to import the SQL files, perform conversion and deploy to DB2. This scenario requires the import, convert, deploy, and config flags. Therefore, you will run the following command:
C:\MTK> MTKMain.bat -import -convert -deploy -config example1.xml |
This command performs three migration steps. Both SQL files are imported, translated into DB2 SQL syntax and deployed to the myDB database. The project files are stored in the folder C:\MTK\projects\migrate1.
Example 2: Context files
The CLI now supports the use of context files. Simply put, context files provide the appropriate definitions for a subsequent migration. Let's expand on the previous example. You have another SQL file named "views.sql" which contains view definitions. These views access tables which are defined in the files "tableset1.sql" and "tableset2.sql". Therefore, these two files must be set as context files for the conversion of "views.sql." Your configuration file should contain the following:
MTK Configuration File: example2.xml
<?xml version="1.0"?>
<!DOCTYPE MTK SYSTEM "mtk.dtd">
<MTK>
<PROJECT
NAME="migrate2" DIRECTORY="C:\MTK\projects"
SRCDBTYPE="oracle" TRGTDBTYPE="DB2LUW_V9.5C">
<SPECIFY_SOURCE>
<IMPORT>C:\sqlscripts\tableset1.sql</IMPORT>
<IMPORT>C:\sqlscripts\tableset2.sql</IMPORT>
<IMPORT>C:\sqlscripts\views.sql</IMPORT>
</SPECIFY_SOURCE>
<CONVERSIONS>
<CONVERSION>
<CONVERT
SRCSQLFILES="tableset1.sql, tableset2.sql">
</CONVERT>
<DEPLOY_TO_TARGET
TRGTDBNAME="myDB"
TRGTUSR="db2user"
TRGTPWD="db2pwd"
LAUNCHSCRIPT="Y"
DEPLOYMTKUDF="N"
RECREATE="Y"
></DEPLOY_TO_TARGET>
</CONVERSION>
<CONVERSION>
<CONVERT
SRCSQLFILES="views.sql"
CONTEXTFILES="tableset1.sql, tableset2.sql">
</CONVERT>
<DEPLOY_TO_TARGET
TRGTDBNAME="myDB"
TRGTUSR="db2user"
TRGTPWD="db2pwd"
LAUNCHSCRIPT="Y"
DEPLOYMTKUDF="N"
></DEPLOY_TO_TARGET>
</CONVERSION>
</CONVERSIONS>
</PROJECT>
</MTK>
|
This configuration file contains an additional conversion section for the "views.sql" file. The context files "tableset1.sql" and "tableset2.sql" are specified in this section. Let's run this new migration scenario with the following command:
C:\MTK> MTKMain.bat -import -convert -deploy -config example2.xml |
This command performs three migration steps. The migration project files are stored in the folder C:\MTK\projects\migrate2. All SQL files were imported, translated into DB2 SQL syntax and deployed to the myDB database.
Example 3: Working with DB2 table spaces
MTK now supports identification of the appropriate DB2 table space in which to insert the new database objects. This example brings together what you have learned in the previous examples while exploring the usage of the newly added table space feature. Imagine a migration effort that involves migrating tables and views from an Oracle database while adhering to the following requirements:
- "tables1.sql" contains table and sequence definitions which should not be deployed to the target database because the objects already exist in the target.
- "tables2.sql" contains table definitions which should be deployed to specific table spaces in the target data server.
- "views1.sql" and "procs1.sql" will be deployed to the target data server. "views1.sql" and "procs1.sql" contain definitions with references to object definitions in "tables1.sql" and "tables2.sql".
Specifying these migration requirements in the CLI configuration file is very straightforward. The following configuration is tailored to achieving the very objectives described above. Notice the new options for table spaces; "tables1.sql" is not converted or deployed; its definitions are used in the conversion of the other database objects.
MTK Configuration File: example3.xml
<?xml version="1.0"?>
<!DOCTYPE MTK SYSTEM "mtk.dtd">
<MTK>
<PROJECT
NAME="migrate3" DIRECTORY="C:\MTK\projects"
SRCDBTYPE="oracle" TRGTDBTYPE="DB2LUW_V9.5C">
<SPECIFY_SOURCE>
<IMPORT>C:\sqlscripts\tables1.sql</IMPORT>
<IMPORT>C:\sqlscripts\tables2.sql</IMPORT>
<IMPORT>C:\sqlscripts\views1.sql</IMPORT>
<IMPORT>C:\sqlscripts\procs1.sql</IMPORT>
</SPECIFY_SOURCE>
<CONVERSIONS>
<CONVERSION>
<CONVERT
SRCSQLFILES="tables2.sql, views1.sql, procs1.sql"
TABLESPACECONVERSION="UserSpecifiedSpaces"
DATATABLESPACE="dspace1"
INDEXTABLESPACE="ispace1"
LONGTABLESPACE="lspace1"
INSRTDROPPROCSTMT="y"
INSRTDROPDDLSTMT="y"
CONTEXTFILES="tables1.sql">
</CONVERT>
<DEPLOY_TO_TARGET TRGTDBNAME="myDB"></DEPLOY_TO_TARGET>
</CONVERSION>
</CONVERSIONS>
</PROJECT>
</MTK>
|
The command to invoke the migration process could be very similar to the following:
C:\MTK> MTKMain.bat -config example3.xml -import -deploy -trgtusr "admin" -trgtpwd "pwd"
|
Conclusion
MTK is continually being enhanced with additional functionality in order to aid your database migrations to DB2 and IDS. In addition, MTK functionality will be added to coincide with, and complement, new IBM data server functionality. The goal of MTK is to make it as easy as possible to enable your application with one of IBM's data servers.
In this article, you've seen how MTK has been extended to provide support for three new SQL constructs added in the DB2 V9.5 release. In addition, you've seen examples of how the CLI has been extended to enhance its capabilities to support more sophisticated migrations. Migrations may be of the "one-time" variety, or you may require regular migrations of data or database objects; depending on your business, data usage, or application environment. If your migration requirements fall into this second case, and you've worked through the MTK GUI a few times and understand the concepts, you might want to consider automating your migration activity using the CLI functionality of MTK. This new functionality is there to make things easier!
Trademark
IBM, DB2 and Informix are registered trademarks of International Business Machines Corporation in the United States, other countries or both. Windows is a trademark of Microsoft Corporation in the United States, other countries or both. UNIX is a registered trademark of The Open Group in the United States, other countries or both. Linux is a registered trademark of Linus Torvalds in the United States, other countries or both. Other company, product or services names may be trademarks or service marks of others.
Download | Description | Name | Size | Download method |
|---|
| MTK configuration files from the examples | examples.zip | 2KB | HTTP |
|---|
Resources Learn
Get products and technologies
-
MTK: Download the toolkit.
-
Download a free trial version of DB2 Enterprise 9.
-
Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
-
Download a free trial version of Informix Dynamic Server.
Discuss
About the authors | | | Daniel Duda is a Software Engineer at IBM's Lenexa lab. He has worked on IBM DB2 Call Level Interface (CLI) and IBM Migration Toolkit (MTK). |
| | | TaWanna Harris is a QA Software Engineer at IBM's Lenexa lab. She has worked on IBM Migration Toolkit, partner and customer application testing, and interoperability testing. |
| | | Adekunle Adio is a Software Engineer at IBM's Lenexa lab. He has worked on Data Facility Storage Management Subsystem (DFSMS) and IBM Migration Toolkit (MTK). |
| | | Shawn Moe is a Software Architect at IBM's Lenexa lab. |
Rate this page
| |
