IBM's Migration Toolkit (or MTK) is a free-to-use, free-support tool that helps you move off competitor databases and on to IBM data servers, both DB2® and Informix® Dynamic Server. If you're new to MTK, check out these earlier articles.
Until now, MTK only offered a graphical way of interacting with the tool. This interface makes MTK very easy to work with, especially for first-timers. Many experienced users however found that carefully-planned, successful migrations involved the repeated use of MTK and expressed a desire to use MTK without having to point and click. And so the latest release of MTK, version 126.96.36.199, includes a command-line interface among other new features.
Working with MTK from the command-line requires you to be familiar with two new things:
- Command-line arguments
- An MTK configuration file
These two items allow you to control MTK just like you do with the GUI. If you're unfamiliar with the overall migration process or the settings of certain steps, we recommend using the GUI until you're more comfortable. There are many details to these arguments and the configuration file -- as many as in the GUI -- because replacing the GUI is the whole point. In this article, we cover them in general and provide helpful examples. For all the details please see MTK's documentation. OK, let's dive in!
A first example
Let's say you want to migrate a certain Oracle database to DB2 9, and you've decided to use MTK from the command-line. Your Oracle database is named "myOra", is hosted on the remote machine "oradb.domain.com", and is accessible via the Oracle service "oradb_svc". You want to migrate this to a remote DB2 database named "myDB2mig" which you've created and cataloged. You have installed MTK, the Oracle JDBC driver, and the DB2 client on your Windows desktop, set your CLASSPATH and PATH appropriately, and verified you can connect to both databases. You want to do a straightforward SQL migration of everything in the database -- no table renaming, data type mapping changes, date reformatting, and so on. And you want the data from Oracle to be moved into DB2 too.
Given these requirements, you first create an MTK configuration file. Let's name it "migration1.xml" and save it in the installation directory of MTK, which on Windows defaults to "C:\MTK". This is an XML file that follows certain MTK-defined rules and tells MTK on what data to perform any migration steps. Since you want to perform all the migration steps, from extraction to deployment, you must include info about each one. Your file looks like this:
MTK configuration file: migration1.xml
<?xml version="1.0"?> <!DOCTYPE MTK SYSTEM "mtk.dtd"> <MTK> <PROJECT NAME="migration1" DIRECTORY="C:\MTK\Projects" SRCDBTYPE="oracle" TRGTDBTYPE="viper_2"> <SPECIFY_SOURCE> <EXTRACT EXTRACTTOFILE="source" SRCUSR="orauser" SRCPWD="orapassword"> <JDBC_CONNECTION IP="oradb.domain.com" PORT="1521" SERVICE="oradb_svc" ></JDBC_CONNECTION> <DATABASE NAME="myOra" ENTIREDATABASE="Y"></DATABASE> </EXTRACT> </SPECIFY_SOURCE> <CONVERSIONS> <CONVERSION> <CONVERT SRCSQLFILE="source.src"></CONVERT> <GENERATE_DATA_TRANSFER_SCRIPTS></GENERATE_DATA_TRANSFER_SCRIPTS> <DEPLOY_TO_TARGET TRGTDBNAME="myDB2mig" TRGTUSR="db2user" TRGTPWD="db2password" DBLOCATION="remote" ></DEPLOY_TO_TARGET> </CONVERSION> </CONVERSIONS> </PROJECT> </MTK>
With the configuration file complete, you're ready to run MTK. Calling MTK is done by typing the name of the main MTK class at a command prompt, but that's not enough. You need to tell MTK about:
- What migration steps you want to perform and
- What data to perform those steps on
This is done by adding arguments. Since you want to perform a full end-to-end migration, use the
-all flag, which means perform all the migration steps. Recall that your configuration file covers #2, so you just tell MTK about that file using the
-config flag. Putting it all together, run the following:
C:\ MTK> MTKMain.java -all -config migration1.xml
Executing this single command instructs MTK to perform all five migration steps, placing output files in the C:\MTK\Projects\migration1 directory. The entire schema of the myOra database is extracted, translated into DB2 SQL and deployed to myDB2mig. Then the data is extracted from Oracle and inserted into myDB2mig. Not bad for one simple command!
As stated above, using MTK from the command line requires you to know about two new things: command line arguments and the MTK configuration file. Think of the command line arguments as the buttons that make MTK perform the import, extract, translate, generate data transfer scripts, and deploy actions. Think of the configuration file as the settings for all those actions including where to get the input data. These actions and settings directly correspond to items on MTK's GUI. However since we're no longer using the GUI, they have to be controlled from elsewhere.
MTK now accepts the following new arguments. Some of these arguments (or flags or switches, depending on your preference) require values to follow them while others do not.
|-config||Specifies the configuration file to use. This flag must be followed by the configuration file's name, including full path, enclosed in double quotes. No part of the file name or path can include the double quote character.||[configuration file]|
|-import||Instructs MTK to import file(s)||NA|
|-extract||Instructs MTK to extract object(s) from source database(s)||NA|
|-convert||Instructs MTK to perform the conversion step||NA|
|-genscript||Instructs MTK to perform the generate data transfer scripts step||NA|
|-deploy||Instructs MTK to perform the deployment step||NA|
|-all||Instructs MTK to perform all specify source operations, the conversion step, the generate data transfer scripts step and the deployment step.||NA|
|-srcusr||User id to use in connecting to source database||[user name]|
|-srcpwd||Password to use in connecting to source database||[password]|
|-trgtusr||Instructs MTK to perform the deployment step||[user name]|
|-trgtpwd||Password to use in connecting to the target database||[password]|
The MTK configuration file, or "config" file, is a structured document whose sections allow users to control settings and specify input data for each step of the migration. MTK uses XML to ensure that each config file is well-formed and valid (New to XML? Learn more from the developerWorks XML zone). MTK includes a sample, valid config file, config.xml, which is a great starting point for command-line users. Edit a copy of this file to contain the settings for your migration by adding your inputs such as the project name and removing any parts you don't desire. For the curious, the config file's validation rules are contained in the "mtk.dtd" file also included in the product.
MTK's GUI has lots of options for each migration step, and the configuration file does too. But don't let this confuse or frustrate you, for most of the configuration file's sections and settings are optional. If you don't specify them, you'll get the default behavior of MTK, exactly the same as what happens in the GUI if you leave the default values alone.
Let's look at the config file's structure in general. The config file starts with some XML-specific lines and then the MTK tag, which encloses the PROJECT tag. The PROJECT tag encloses everything that users already associate with an MTK project.
So far so good. A PROJECT may contain a SPECIFY_SOURCE section and a CONVERSIONS section, both of which are optional. Making them optional gives flexibility. For example, if you want to perform imports or extractions but no translations, then fill in the appropriate subsections of SPECIFY_SOURCE and feel free to leave out the CONVERSIONS part. The sections of your configuration file must exist for the command-line arugments you use, however. If you run MTK with "-import" and don't include SPECIFY_SOURCE with at least one IMPORT tag, MTK will give an error and exit.
The CONVERSIONS section contains all the data for the translation, data transfer script generation, and deployment steps. It contains an optional GLOBAL_SETTINGS section and one or more CONVERSION (note this word is singular) sections. Just like in the GUI, a CONVERSION (note singular) represents a single source SQL file and is the basic unit on which translation, refinement, script generation and deployment are performed. Each CONVERSION section allows you to specify all the global-type mapping, deployment, etc. options you're familiar with from the GUI. If you find you use certain options consistently, place them in the GLOBAL_SETTINGS section, where they can then apply to all CONVERSIONs.
MTK uses the following logic to determine what setting to apply. If the setting is set in the CONVERSION, MTK applies that. If it's not set in the CONVERSION but is in the GLOBAL_SETTING, MTK takes that one. If the setting is not set in the config file at all, MTK applies the same defaults as the GUI. See the product documentation for the default values.
The MTK project
Whether you're using MTK from the GUI or the command-line, migrations always occur within a project. This piece is the foundation of every MTK migration, and that means you can work on any project using any combination of both interfaces. Here's an example of using both on the same project. In this scenario, the user wants to move schema objects and data daily from a source database to an IBM data server.
- Write and schedule a script that uses the MTK from the command-line to extract and translate the objects (like tables and views) when your database is at a low transaction period. A good time for this may be in the early morning, before the start of business.
- During business hours use the GUI to perform schema refinements and review any warnings or error messages from step (1) above. Remember, MTK greatly helps automate the migration process, but due to database differences, not all migrations are 100% automatable.
- Schedule another script that uses the command line to deploy the objects from (2) and move the data to the target IBM data server. Like in the first step, this script could be scheduled to take advantage of low peak transaction periods of both the source database and target IBM data server.
Whether you use MTK from the GUI or command line, the information about the project and files in the project directory remain the same as if MTK were used only from the GUI. Please note that the CLI and the GUI should not be used simultaneously on the same project.
Now let's explore using MTK from the command line through further, more complex examples. We demonstrate two scenarios with lots of variety so that you can see how many situations can be handled.
Example A: Migrating from multiple source databases to multiple target databases
One database is fine, but more are even better! How do you use MTK to extract the schema and data from multiple source databases and deploy to multiple target databases? Consider this scenario.
You'd like MTK to:
- Create a new migration project called "Example2" located in the directory "C:\MTK\projects".
- Connect to your Oracle database named "orcldb1" using an ODBC connection named "oraODBC1" and extract the entire source schema contained in "orcldb1" into your project. This includes all available tables, triggers, views, procedures, sequences and packages contained in the database. You want these extracted objects to be stored in a file called "file1.src".
- Connect to a second Oracle database named "orcldb2" using JDBC (IP = 192.168.1.10, port 1521, and service name "ora2svc") and extract all tables associated with the schema "doe" into your project in a file called "file2.src."
- Translate "file1.src" and "file2.src" into DB2 Viper 2 SQL, in that order. The translated output will be stored in DB2 files named"file1.db2" and "file2.db2" respectively.
- Generate the transfer scripts necessary to move data from all the source tables specified in "file1.db2" and "file2.db2" to a target database. Use load utility during data deployment.
- Connect to an existing Viper 2 database named "IBMatl" using the current system user logon credentials and deploy the objects contained in "file1.db2" and associated data here.
- Create a new Viper 2 database named "IBMlax" using the current system user logon credentials and deploy the objects in "file2.db2" and associated data to this database.
Now that we have detailed this complex scenario, how do we accomplish each and every step? The answer may be simpler than you suspect. We need the right arguments and the right configuration file to pass to the CLI. Remember that the arguments specify what migration steps to be performed and the configuration file contains the necessary data needed to perform the specified steps. We create a configuration file "exampleA.xml" (shown below) that contains the appropriate information to perform our migration tasks. Then we issue the command below and voila, our migration is complete!
Command issued from the prompt:
C:MTK> MTKMain.bat -config exampleA.xml -extract -convert -genscript -deploy Or C:MTK> MTKMain.bat -config exampleA.xml -all
MTK Configuration File: exampleA.xml
<?xml version="1.0"?> <!DOCTYPE MTK SYSTEM "mtk.dtd"> <MTK> <PROJECT NAME="Example2" DIRECTORY="c:\mtk\projects" SRCDBTYPE="oracle" TRGTDBTYPE="viper_2"> <SPECIFY_SOURCE> <EXTRACT EXTRACTTOFILE="file1" SRCPWD="usr1" SRCUSR="pwd4usr1" ALLOBJECTS="Y"> <ODBC_CONNECTION DSN="oraODBC1"></ODBC_CONNECTION> </EXTRACT> <EXTRACT EXTRACTTOFILE="file2" SRCPWD="usr2" SRCUSR="pwd4usr2"> <JDBC_CONNECTION IP="192.168.1.10" PORT="1521" SERVICE="ora2svc" ></JDBC_CONNECTION> <DATABASE NAME="orcldb2"> <SCHEMA NAME="doe" ALLTABLES="y"></SCHEMA> </DATABASE> </EXTRACT> </SPECIFY_SOURCE> <CONVERSIONS> <GLOBAL_SETTINGS> <DEPLOY_TO_TARGET TRGTDBNAME="IBMTest" USECURRENTSYSLOGIN="y" EXTRACTDATATOSYS="y" LOADDATATOTRGT="y" ></DEPLOY_TO_TARGET> </GLOBAL_SETTINGS> <CONVERSION> <CONVERT SRCSQLFILE="file1.src"></CONVERT> <GENERATE_DATA_TRANSFER_SCRIPTS FILEFORMAT="DEL"></GENERATE_DATA_TRANSFER_SCRIPTS> <DEPLOY_TO_TARGET TRGTDBNAME="IBMatl" RECREATE="y"></DEPLOY_TO_TARGET> </CONVERSION> <CONVERSION> <CONVERT SRCSQLFILE="file2.src"></CONVERT> <GENERATE_DATA_TRANSFER_SCRIPTS></GENERATE_DATA_TRANSFER_SCRIPTS> <DEPLOY_TO_TARGET TRGTDBNAME="IBMlax" RECREATE="y"></DEPLOY_TO_TARGET> </CONVERSION> </CONVERSIONS> </PROJECT> </MTK>
Example B: Mixed-mode use of MTK
In the MTK Project section above we presented a scenario that takes advantage of using MTK from both the command line and the GUI. Now let's roll up our sleeves and implement that migration plan!
We want to take advantage of using the CLI and GUI to migrate your Microsoft SQL Server database to your new IBM Viper 2 data server. Let us assume the following
- You will be moving your transaction database called "Projects" to your IBM Viper 2 instance.
- Your source database server is at a low transaction period between 11:00 p.m. to 1:00 a.m. and 3:00 a.m. to 5:00 a.m. every day.
- You have limited database administrator resources, and you want to maximize your entire database resource in general.
Following the three steps mentioned above you would like to:
- Schedule the CLI run at 11:05pm to:
- Extract your entire "Projects" database from your MSSQL Server.
- Convert each extracted DDL and DML statement into its equivalent DB2 syntax.
- During business hours, use the MTK GUI to analyze and evaluate the result of the conversion. Where necessary, you can also use the GUI to refine your conversions.
- Schedule the MTK CLI run at 3:05 am to:
- Deploy the converted objects to your Viper 2 database named "DB2Proj."
- Migrate your data from "Projects" to the target database on your IBM Viper 2 instance.
Here's a question you should know by heart now: what two things will you need? The arguments and a configuration file, of course! Let us take a closer look at what your 11:05 p.m. and 3:05 a.m. scheduled scripts could look like.
11:05 p.m. scheduled script:
cd c:\mtk MTKMain.bat -config exampleB.xml -extract -convert
3:05 a.m. scheduled script:
cd c:\mtk MTKMain.bat -config exampleB.xml -genscript -deploy -trgtusr "db2admin" -trgtpwd "pwd"
MTK Configuration File: exampleB.xml
<?xml version="1.0"?> <!DOCTYPE MTK SYSTEM "mtk.dtd"> <MTK> <PROJECT NAME="Example3" DIRECTORY="c:\mtk\projects" SRCDBTYPE="sql_server" TRGTDBTYPE="viper_2"> <SPECIFY_SOURCE> <EXTRACT EXTRACTTOFILE="file3" SRCPWD="sa" SRCUSR="pwd4sa"> <JDBC_CONNECTION IP="192.168.1.10" PORT="1234"></JDBC_CONNECTION> <DATABASE NAME="Projects" ENTIREDATABASE="y"></DATABASE> </EXTRACT> </SPECIFY_SOURCE> <CONVERSIONS> <CONVERSION> <CONVERT SRCSQLFILE="file3.src"> <TYPEMAPPING SRCSQLTYPE="TEXT" TRGTSQLTYPE="VARCHAR" TRGTSQLLENGTH="200" ></TYPEMAPPING> </CONVERT> <GENERATE_DATA_TRANSFER_SCRIPTS></GENERATE_DATA_TRANSFER_SCRIPTS> <DEPLOY_TO_TARGET TRGTDBNAME="DB2Proj" RECREATE="y" EXTRACTDATATOSYS="y" LOADDATATOTRGT="y" ></DEPLOY_TO_TARGET> </CONVERSION> </CONVERSIONS> </PROJECT> </MTK>
MTK makes database migration easy, and the new command line interface allows you to control MTK in powerful, flexible ways. Use the flags or arguments to specify what operations to perfom and the configuration file to specify the data on which to perform them. That's it! Now get migrating!
|MTK configuration files from the examples||Examples.zip||2KB|
- "New capabilities for migrating to DB2 and Informix in IBM Migration Toolkit 1.4.9" (developerWorks, Jan 2007): Learn about many enhancements to MKT, including support for migrating to IDS.
- "Migrating to IBM database servers gets easier with the latest MTK release" (developerWorks, Mar 2006): New to the Migration Toolkit? Learn more about it in this article.
- "Automate the Switch to DB2 Using the IBM DB2 UDB Migration Toolkit" (developerWorks, Sep 2002): Get the details on using the wizard and the GUI interface for the MKT.
- IBM Informix Dynamic Server 11 InfoCenter: Click on the Migrating link for documentation on the Migration Toolkit.
- MTK FAQ: This document answers many IBM Migration Toolkit questions.
- developerWorks Migration Station: Link to migration resources.
- developerWorks XML zone: Read articles and tutorials and link to resources on XML.
- DB2 z/OS 9: Learn about the latest version of DB2 for z/OS.
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.
- Send questions on issues directly to: firstname.lastname@example.org