Oracle to DB2 UDB Conversion Guide2003

3.1 MTK overview

The IBM DB2 Migration Toolkit (MTK) (Figure 3-1) is available free of charge from IBM at the following URL: http://www.ibm.com/software/data/db2/migration/mtk

Figure 3-1: The IBM DB2 Migration Toolkit (MTK)

MTK was developed by the IBM Silicon Valley Laboratory in San Jose, California with assistance and contributions from the Watson Laboratory in Hawthorne, NY. Some driving factors in creating MTK are:

• The need to develop a tool that was closely linked and kept pace with the technical development of DB2 and the databases with which it interacts.

• The need to address "real world" migration concerns. Because of IBM's significant experience in this area, the developers have created a tool that meets the requirements of IBM migration teams while addressing the significant issues involved in customer migrations.

• The need for a tool that would convert as much, and as accurately as possible.

• The need for a tool that would be available, free of charge to those interested in engaging in a migration.

3.1.1 MTK facts

The following operating systems, conversion sources, and conversion targets are supported by MTK.

• MTK can be installed on the following operating systems:

  • Windows NT 4.0, Windows 2000, Windows XP

  • Linux

  • AIX 4.3.3.0 or later

  • Sun Solaris 5.7

• MTK supports the following databases as conversion sources:

  • Sybase Enterprise, Versions 11 and 12

  • Microsoft SQL Server, versions 6, 7, and 2000

  • Oracle 8i

  Note

  Oracle 8i is the officially supported version. MTK can also work with Oracle 7 and 9i databases except for some of the special functionality.

• MTK supports the following versions of DB2 UDB as conversion targets:

  • DB2 UDB v7.2 (FixPak 6 or later) (EE/EEE)

  • DB2 UDB v8.1 (FixPak 2 or later), multiplatform, all editions

  • DB2 UDB for iSeries Version 5.2 or later

3.1.2 MTK features

MTK converts the following Oracle source database constructs into equivalent DB2 UDB database objects:

• Data types

• Tables

• Columns

• Views

• Indexes

• Constraints

• Packages

• Stored procedures

• Functions

• Triggers

• Sequences

MTK enables the following tasks:

• Obtaining source database metadata (DDL) by EXTRACTING information from the source database system catalogs through (JDBC/ODBC)

• Obtaining source database metadata (DDL) by IMPORTING DDL scripts created by SQL*Plus or third-party tools

• Automating the conversion of database object definitions, including stored procedures, triggers, packages, tables, views, indexes, and sequences

• Deploying SQL and Java compatibility functions that permit the converted code to "behave" functionally similar to the source code

• "On the fly" conversion of PL/SQL statements using SQL Translator tool. Also effective as a DB2 SQL PL learning aid for PL/SQL developers.

• Viewing conversion information and messages

• Deployment of the converted objects into a new or existing DB2 UDB database

• Generating and running data movement (unload/load) scripts or performing the data movement on-line.

• Tracking 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

3.1.3 MTK GUI interface

The MTK GUI interface (Figure 2-2) presents five tabs each of which represents a specific task in the conversion process. The tabs are organized from left to right and are titled:

• Specify Source

• Convert

• Refine

• Generate Data Transfer Scripts

• Deploy to DB2

The menu bar contains Application, Project, Tools, and Help:

• Application: Allows you to set up your preferences such as an editor.

• Project: You can tart a new project, open or modify an exiting project, import SQL source file, or perform backup restore function through here.

• Tools: You can launch to SQL Translator, reports, and the log.

• Help: MTK help text

Figure 3-2: MTK GUI interface

3.1.4 Migration tasks

The five tabs in the MTK user interface first screen represent the five phases of the migration process. The following is an overview of these five tasks:

• Task 1: Specify source

  The SPECIFY SOURCE task (Figure 3-3) focuses on Extracting or Importing database metadata (DDL) into the tool. The database objects defined in this DDL will then be used as the source code for conversion to DB2 UDB equivalent objects. Extraction requires a connection to the source database through ODBC or JDBC. Once the ODBC/JDBC connection is established, MTK will 'read' the system catalogs of the source database and extract the definitions for use in the conversion process. IMPORTING, on the other hand, requires an existing file, or files, which contain database object DDL. The Import task copies the existing DDL from the file system into MTK project directory for use in the database structure conversion process. Using MTK to perform data movement will be limited if IMPORTING is chosen.

  Figure 3-3: Specify source

• Task 2: Convert

  During the CONVERT task (Figure 3-4) the user may complete several optional tasks before the actual conversion of the source code. These are:

  • Selecting format options for the converted code. Examples of options are: including the source code as comments in the converted code; including DROP before create object statements, among others.

  • Making changes to the default mapping between a source data type and its target DB2 data type.

  Figure 3-4: Convert

  Once the optional tasks are completed the user can click the Convert button and the source DDL statement is converted into DB2 DDL.

  Each conversion generates two files:

  • db2 file contains all of the source code converted to DB2 UDB target code.

  • .rpt file can be opened and viewed from this pane, it is best to examine it during the next task, which is Refine.

• Task 3: Refine

  During the REFINE task (Figure 3-5) the user may:

  • Examine the results of the conversion

  • View various types of messages generated by the tool and, if necessary specify changes to be made to the converted DDL

  Figure 3-5: Refine

  If the user makes any changes to the converted DDL, one must return to the Convert step to apply the changes.

  You can use other tools such as the SQL Translator, Log, and Reports to help you refine the conversion. After you have refined the DB2 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 DB2 step to execute the DB2 DDL statements.

• Task 4: Generate data transfer scripts

  In the GENERATE DATA TRANSFER task (Figure 3-6), scripts are generated that will be used to:

  • Unload data from the source environment

  • Load or Import data into DB2 UDB

  Figure 3-6: Generate Data Transfer script

  Before creating the scripts one may choose some advanced options that will affect how the IMPORT or LOAD utility operates. This will allow the user to refine the Load or Import specifications to correspond with the requirements of their data and environment.

• Task 5: Deploy to DB2

  The DEPLOY task (Figure 3-7) is used to install database objects and Import/Load data into the target DB2 database. In this task, one can:

  • Choose to create the database or install the objects in an existing database.

  • Execute the DDL to create the database objects.

  • Extract data from the source database.

  • Load/import the source data into the target DB2 tables or choose any combination of the above three.

  Figure 3-7: Deploy to DB2

An overview of all the tasks in the MTK conversion process is shown in Figure 3-8.

Figure 3-8: MTK conversion tasks overview

3.1.5 The MTK SQL Translator

The MTK SQL Translator (Figure 3-9) enables "on-the-fly" conversion of individual statements, a series of statements, or stored procedures. The translator requires that all of the dependent objects for the SQL that you wish to convert are available to MTK. This may be accomplished in either of two ways:

• The current project already contains all of the converted objects on which the desired SQL depends (tables, views, etc.)

• The objects on which the SQL statement depends will be created in the SQL Translator window by placing them before the SQL that is to be converted.

Figure 3-9: The MTK SQL Translator