New capabilities for migrating to DB2 and Informix in IBM Migration Toolkit 1.4.9

Migrate Oracle PL/SQL and more!

January is a time of celebrating change, and there has been much change in the IBM® Migration Toolkit (MTK) that deserves celebration, too! The latest release of IBM's free migration toolkit, MTK 1.4.9, includes many new features that make migrating to your favorite IBM database even easier -- support for more DB2® features, migration support for Oracle PL/SQL to IBM Informix® Dynamic Server (IDS), Oracle's UTL_FILE package, and more. Read on for a summary of these improvements.

Share:

Hemant Bhatia, Software Engineer, IBM

Hemant Bhatia is a Software Engineer at IBM's Lenexa lab. He has worked on IBM's Informix 4GL, Informix classic databases, DB2 Information Integrator, and IBM Migration Toolkit.



Nicholas Geib, Software Engineer, IBM

Nicholas Geib is a Software Engineer at IBM's Lenexa lab working on the IBM Migration Toolkit.



04 January 2007

Oracle to DB2

A number of new features have been added in the MTK to enhance the migration support to DB2 for Linux®, UNIX®, and Windows® (DB2 LUW). These enhancements include the support for migrating to DB2 9, as well as support for translating new SQL statements and improving existing translations of some SQL statements. This section provides an overview of the three new features that are now supported for translation to DB2 LUW by the MTK:

  1. Range partitioning
  2. Data compression
  3. MERGE statement

Range partitioning

MTK now provides support for translating Oracle range partitioning (also known as table partitioning). Translation of this feature is supported only when DB2 9.1 or higher is the target. MTK translates the range_values_clause and the built-in function PARTITION().

Currently translations of statements containing the range partitioning clause are supported only when these statements are provided in an input file using the import option in the MTK. DB2 UDB does not allow MAXVALUE to be followed by any other values, and the range must be valid for each partition value. As a workaround, you can change the values in the ENDING clause as follows: (MAXVALUE, MAXVALUE, MAXVALUE).

Listings 1 and 2 show the translation of a range partitioning clause by MTK:

Listing 1. Range partitioning clause
CREATE TABLE accessnumbers (area INT, exchange INT)
PARTITION BY RANGE (area, exchange)
       ( PARTITION access1 VALUES LESS THAN (1,1),
         PARTITION access2 VALUES LESS THAN (10,100),
         PARTITION access3 VALUES LESS THAN (MAXVALUE, MAXVALUE) );
SELECT * FROM accessnumbers PARTITION(access1) ;
Listing 2. MTK translation
CREATE TABLE accessnumbers(
    area DECIMAL(31,0),
    exchange DECIMAL(31,0)
)
PARTITION BY RANGE(
    area,
    exchange
)(
    PARTITION access1 STARTING (MINVALUE, MINVALUE) INCLUSIVE
        ENDING (1, 1) EXCLUSIVE,
    PARTITION access2 STARTING (1, 1) INCLUSIVE
        ENDING (10, 100) EXCLUSIVE,
    PARTITION access3 STARTING (10, 100) INCLUSIVE
        ENDING (MAXVALUE, MAXVALUE) EXCLUSIVE)!

SELECT * FROM accessnumbers WHERE DATAPARTITIONNUM(area) =
    ( SELECT SEQNO FROM SYSCAT.DATAPARTITIONS WHERE TABNAME = UPPER('accessnumbers')
          AND  DATAPARTITIONNAME = UPPER('access1'))!

Data Compression

MTK now provides support for translating the Oracle table_compression clause. Translation of this feature is supported only when migrating to DB2 9.1 or higher.

In conversions to DB2 9.1, the ALTER TABLE statement with data compression is supported, and limited support is provided for CREATE TABLE statement. In the case of the CREATE TABLE statement, the translation of Oracle key_compression clause and partitioned tables with compression are not supported. In DB2 9.1, the compression takes effect only after the table dictionary is built, which is usually during the table REORG phase.

Listings 3 and 4 show the translations of a table_compression clause by MTK:

Listing 3. Table_compression clause
CREATE TABLE SALES_HISTORY_COMP1 (
  PART_ID       VARCHAR2(50) NOT NULL,
  STORE_ID      VARCHAR2(50) NOT NULL,
  SALE_DATE     DATE NOT NULL,
  QUANTITY      NUMBER(10,2) NOT NULL
)
COMPRESS;
ALTER TABLE SALES_HISTORY COMPRESS;
Listing 4. MTK translation
CREATE TABLE SALES_HISTORY_COMP1(
    PART_ID VARCHAR(50) NOT NULL,
    STORE_ID VARCHAR(50) NOT NULL,
    SALE_DATE TIMESTAMP NOT NULL,
    QUANTITY DECIMAL(10,2) NOT NULL
) COMPRESS YES!

ALTER TABLE SALES_HISTORY COMPRESS YES!

MERGE statement

Support for translating the Oracle MERGE statement to DB2 UDB now also has been provided in the MTK. The DELETE clause of the merge_update_clause is translated to a separate DB2 matching-condition and search-condition. This feature is supported for translation when DB2 8.1 or higher is the target. Translation of the error_logging_clause of the Oracle MERGE statement is not supported.

Listings 5 and 6 show the translation of an Oracle MERGE statement by MTK:

Listing 5. MERGE statement
MERGE INTO merge_tab10b t
USING (SELECT  school_id,teacher_name,field FROM merge_tab10a) s
ON (0 = 1)
WHEN NOT MATCHED THEN
INSERT VALUES (s.teacher_name,s.field)
WHERE s.school_id = 100;
Listing 6. MTK translation
MERGE INTO merge_tab10b t USING
(SELECT school_id, teacher_name, field FROM merge_tab10a) s
      ON ( 0 = 1 )
      WHEN NOT MATCHED
      AND s.school_id = 100
      THEN INSERT  VALUES (s.teacher_name,s.field)!

Oracle PL/SQL to IDS

Earlier this year, MTK announced limited support for migrating from Oracle to IBM IDS. Now the latest MTK, Version 1.4.9, migrates Oracle's procedural programming language (PL/SQL) to IDS' Stored Procedure Language (SPL). Many Oracle users employ PL/SQL to add custom logic to their data processing in a variety of ways. This logic can be very complex and often requires much time to migrate manually. MTK 1.4.9 allows you to automatically move your PL/SQL logic in procedures, functions, cursors, packages, and more to IDS:

  1. Procedures and functions
  2. Triggers
  3. Cursors
  4. Exception handling

Procedures and functions

All Oracle PL/SQL is based on two components: what Oracle calls subprograms and anonymous PL/SQL blocks. Subprograms occur in one of two forms -- procedures or functions. These both embody a set of PL/SQL statements and attach a name to that set, allowing the logic to be easily referenced by other PL/SQL and SQL code. Procedures and functions occur frequently in Oracle databases, and MTK migrates both of them. Anonymous PL/SQL blocks, on the other hand, do not attach a name to the set of logical statements. These are typically used for developing or testing procedures and functions and are not storable in the Oracle database. MTK does not migrate anonymous PL/SQL blocks.

Given the following Oracle procedure and function:

Listing 7. Oracle procedure and function
CREATE PROCEDURE ADD_NEW_EMPLOYEE (empName CHAR(50)) AS
	nextEid INT DEFAULT 0;
BEGIN
	SELECT MAX(eid) INTO nextEid FROM employee;
	nextEid := nextEid + 1;
	INSERT INTO employee VALUES (nextEid, empName);
END;

CREATE FUNCTION NUM_DEPARTMENTS RETURN INT AS
  i1 INT;
BEGIN
  SELECT COUNT(*) INTO i1 FROM dept;
  RETURN i1;
END;

MTK generates the following IDS SPL:

Listing 8. MTK-generated IDS SPL
CREATE PROCEDURE ADD_NEW_EMPLOYEE (empName CHAR(50) )
    DEFINE nextEid DECIMAL(32,0);
    LET nextEid = 0.0;
    SELECT ROUND(MAX(eid)) INTO nextEid FROM employee;
    LET nextEid = nextEid + 1;
    INSERT INTO employee VALUES (nextEid,empName);
END PROCEDURE;

CREATE FUNCTION NUM_DEPARTMENTS()
RETURNING DECIMAL(32,0)
    DEFINE i1 DECIMAL(32,0);
    LET i1 = NULL;
    SELECT COUNT(*) INTO i1 FROM dept;
    RETURN i1;
END FUNCTION;

In addition to these examples above, MTK migrates procedures that utilize features like positional and named parameters, overloading, and recursion. There are some limitations that MTK imposes -- for example, only IN parameters are supported. See the product documentation for details of supported areas and limitations.

Triggers

Triggers are sets of PL/SQL that execute automatically when certain events in the database occur. Use MTK 1.4.9 to automatically migrate your Oracle triggers to IDS. For example, see the Oracle trigger in Listing 9, which captures the percentage change and date of each employee's salary change, and the IDS translation (Listing 10):

Listing 9. Oracle trigger
CREATE TRIGGER salary_change_trigger
BEFORE UPDATE OF salary on emp_salary
REFERENCING OLD as old NEW as NEW FOR EACH ROW
BEGIN
	INSERT INTO salary_change VALUES (:new.eid,
		:new.salary / :old.salary, CURRENT_DATE);
END;
Listing 10. IDS translation
CREATE TRIGGER salary_change_tri1
UPDATE OF salary ON emp_salary
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
(
      INSERT INTO salary_change VALUES (NEW.eid,NEW.salary /
      OLD.salary,CURRENT YEAR TO FRACTION(5))
);

Oracle triggers offer some features that IDS does not support, and thus MTK does not support their translation. These include defining multiple triggers for the same INSERT, UPDATE, or DELETE event for the same table, Oracle's REFERENCING PARENT syntax, and INSTEAD OF triggers.

Cursors

Cursors are a common database abstraction that allow programs and you to view and, thereby, process the results of a query one row at a time. Oracle cursors can be declared, opened, fetched from, and closed. Oracle also allows cursors to be tightly bound to a looping statement, a construct called a cursor FOR loop. In contrast, IDS SPL supports a similar cursor looping statement but does not allow cursors to be explicitly declared, opened, or closed. Such functionality is available in other tools (the ESQL/C programming language, for example).

MTK supports migrating Oracle cursors to IDS. However, due to some differences, there are a number of restrictions:

  • All FETCH statements must be into the same variable(s)
  • At most, one cursor per PL/SQL block can be declared and used
  • Cursors in nested PL/SQL blocks are not supported
  • Looping statements (WHILE, LOOP, for example) must not that contain FETCH statements
  • Cursor attributes are not supported
  • Cursor variables are not supported

Listings 11 and 12 contain examples of an Oracle cursor and the IDS translation:

Listing 11. Oracle cursor
CREATE OR REPLACE FUNCTION sum_remaining_vacation RETURN INT AS
  vac_sum INT DEFAULT 0;
  cursor emp_cur IS SELECT * FROM employee;
BEGIN
	FOR emp_rec in emp_cur LOOP
		vac_sum := vac_sum + emp_rec.vac_days;
	END LOOP;
	RETURN vac_sum;
END;
Listing 12. IDS translation
CREATE FUNCTION sum_remaining_vac1()
RETURNING DECIMAL(32,0)
    DEFINE vac_sum DECIMAL(32,0);
    DEFINE emp_rec_EID DECIMAL(32,0);
    DEFINE emp_rec_ENAME CHAR(50);
    DEFINE emp_rec_VAC_DAYS DECIMAL(32,0);
    LET vac_sum = 0.0;
    FOREACH emp_cur for SELECT * INTO emp_rec_EID, emp_rec_ENAME,
    emp_rec_VAC_DAYS FROM employee
	LET vac_sum = vac_sum + emp_rec_VAC_DAYS;

    END FOREACH;
    RETURN vac_sum;
END FUNCTION ;

Exception handling

Databases can encounter errors, and Oracle PL/SQL allows you to interact with them using the EXCEPTION concept. PL/SQL offers much functionality when working with exceptions -- you can handle or catch them, define new exceptions, raise them, and so on. MTK supports the majority of PL/SQL exception statements.

Some exceptions are common and have been grouped into a set of pre-defined or system exceptions. These include Oracle's ZERO_DIVIDE, ROWTYPE_MISMATCH, and LOGIN_DENIED. While Oracle's predefined exceptions are characters or word, IDS exceptions are numbers. MTK automatically maps one to the other. Further, Oracle allows you to map exception words to numbers using the PRAGMA EXCEPTION_INIT declaration. Again, MTK handles this automatically.

MTK migrates Oracle user-defined exceptions, calls to the RAISE_APPLICATION_ERROR procedure, and statements that raise and handle exceptions to IDS.


Oracle UTL_FILE package

The Oracle database offers a number of packages that provide functionality outside the scope of a traditional database. One of these, the UTL_FILE package, contains utility methods that allow you to interact with the file system of the machine on which the database resides. These package methods can be called by PL/SQL programs, among other programming environments. Although the signatures and functionality of these methods vary between releases, the core set of functionality has remained the same.

MTK supports the migration of six core UTL_FILE methods to DB2 UDB for LUW and IDS. Only the Oracle 9.2 signatures of these methods are supported. The Oracle 9.2 method functionality is achieved through a series of user-defined functions that MTK deploys to the target database. These six supported UTL_FILE methods are:

  • FOPEN
  • FCLOSE
  • GET_LINE
  • PUT_LINE
  • FRENAME
  • FREMOVE

In Listing 13, see an example of an Oracle PL/SQL program that reads from one file and writes to a second:

Listing 13. Oracle PL/SQL program
CREATE PROCEDURE COPY_START_OF_FILE(numLines INT) AS
	file1 utl_file.file_type;
	file2 utl_file.file_type;
	line VARCHAR(3000);
	i INT DEFAULT 0;
BEGIN
	file1 := utl_file.fopen('DIR1', 'readme.txt', 'r', 3000);
	file2 := utl_file.fopen('DIR1', 'write_to_me.txt', 'w');

        WHILE i < numLines LOOP
		utl_file.get_line(file1, line);
		utl_file.put_line(file2, line);
		i := i + 1;
	END LOOP;

	utl_file.fclose(file1);
	utl_file.fclose(file2);
END;

Listings 14 and 15 show the MTK and IDS translations to DB2 UDB for LUW:

Listing 14. MTK translation to DB2 UDB for LUW
CREATE PROCEDURE COPY_START_OF_FILE (numLines DECIMAL(31,0) )
LANGUAGE SQL

BEGIN
    DECLARE file1 DECIMAL(31,0);
    DECLARE file2 DECIMAL(31,0);
    DECLARE line VARCHAR(3000);
    DECLARE i DECIMAL(31,0) DEFAULT 0.0;
    DECLARE file1_OFFSET BIGINT DEFAULT 0;

    SET file1 = ORA.FOPEN('/tmp', 'readme.txt', 'r', 3000);
    SET file2 = ORA.FOPEN('/tmp', 'write_to_me.txt', 'w');

    WHILE i < numLines DO
        CALL ORA.GET_LINE('/tmp','readme.txt','r',3000,
					line,3000,3000,file1_OFFSET);
        CALL ORA.PUT_LINE('/tmp','write_to_me.txt','w',1,line);
        SET i = i + 1;
    END WHILE ;

    CALL ORA.FCLOSE(file1);
    CALL ORA.FCLOSE(file2);
END!
Listing 15. MTK translation to IDS
CREATE PROCEDURE COPY_START_OF_FILE (numLines DECIMAL(32,0) )
    DEFINE file1 DECIMAL(32,0);
    DEFINE file2 DECIMAL(32,0);
    DEFINE line LVARCHAR(3000);
    DEFINE i DECIMAL(32,0);
    DEFINE file1_OFFSET INT8;

    LET file1 = NULL;
    LET file2 = NULL;
    LET line = NULL;
    LET i = 0.0;
    LET file1 = ORA.FOPEN('/tmp', 'readme.txt', 'r', 3000);
    LET file2 = ORA.FOPEN('/tmp', 'write_to_me.txt', 'w');

    WHILE i < numLines
        LET file1_OFFSET = 0;
        SELECT temp_file1_OFFSET, temp_line INTO file1_OFFSET, line
          FROM informix.dual
          WHERE ORA.GET_LINE('/tmp', 'readme.txt', 'r', 3000,
			temp_line # LVARCHAR, 3000, 3000, file1_OFFSET,
			temp_file1_OFFSET # INT8) = 1;

        EXECUTE PROCEDURE PUT_LINE('/tmp','write_to_me.txt',
						'w',1,line);

        LET i = i + 1;
    END WHILE ;

    EXECUTE PROCEDURE ORA.FCLOSE(file1);
    EXECUTE PROCEDURE ORA.FCLOSE(file2);
END PROCEDURE;

Additional enhancements

This section describes changes and enhancements that have been added to the MTK since the MTK 1.4 release in addition to the features described above:

  1. Migrating to DB2 UDB on iSeries
  2. Windows silent install
  3. JRE bundling
  4. MTK FAQ
  5. MTK 2.0.1.1 supports MySQL

Migrating to DB2 UDB on iSeries

A number of enhancements have been added in the MTK for migrations to DB2 UDB on iSeries. These are:

  1. Support for migrating to version V5R4M0 of DB2 for i5/OS
  2. You are no longer limited to your schema for deploying to DB2 -- you can specify an alternate schema at create data scripts and deploy time
  3. Improved support for migrations, where the source and the iSeries machine are not accessible on the same network. You can extract the database objects and data and create the data movement scripts on one network. You can then be zip and send the project to the remote location. After you unzip the project on a machine on the second network, MTK can deploy the database objects and data on the remote system.
  4. Support for VARCHAR to CHAR data type mapping, which provides better performance on DB2 UDB for i5/OS. This is supported for all the sources that can be migrated to DB2 UDB for i5/OS.
  5. Use of native DB2-like operator, where possible, for Sybase and SQL Server translations for improved performance

Windows silent install

MTK now provides the option of performing a silent install on the Windows platform. In order to perform a silent install of the MTK on Windows, follow these simple steps:

  1. Unzip the zip file downloaded from the MTK download site. (The zip file contains the MTKSilentInstall.iss file, the readme file, and the mtk.exe file.)
  2. Run the installation, using the MTKSilentInstall.iss response file, by executing the following command:

    mtk.exe -s -f1"<full path of the MTKSilentInstall.iss file>" 
    -f2"<full path of the install log file>"

The -s option indicates that the install will be done silently. Please note that there should be no space between the f1 option and the location of the silent install iss file. There, also, should be no space between the -f2 option and the location of the install log file.

Once the silent install is completed, you can open the install log file and check the ResultCode. If the ResultCode is set to 0, the install was successful.

JRE Bundling

The MTK installable product no longer includes the Java Runtime Environment (JRE). Because of this, you must have JRE 1.4.2 or later installed and accessible through the PATH environment variable. You can verify that Java is accessible and is at least at the 1.4.2 or higher level by executing the following command:

java -version

MTK FAQ

MTK documentation has been enhanced by adding a section on Frequently Asked Questions (FAQ) about the MTK. These FAQ have been compiled from various user queries that the MTK development team has received. The questions cover a wide range of topics from all the five migration steps performed by the MTK. Detailed answers provided in the FAQ should help you with the most common issues that arise when using the MTK. Users are strongly encouraged to go through the FAQ in the MTK documentation before sending any queries to the MTK development team.

MTK 2.0.1.1 supports MySQL

A prototype of MTK 2.0.1.1 is now available for download. This prototype provides new migration support for MySQL (Versions 4 and 5) to Informix Dynamic Server, DB2 UDB on Linux, Unix, and Windows, and DB2 UDB for i5/OS.


Connecting to the source database

MTK allows you to connect directly to your source database. Once connected, MTK can automatically extract the database objects (tables, views, and indexes, for example) and the data inside the tables. Connecting to a source database (for example, Oracle) is quite easy.

  1. First, download and install the Oracle JDBC driver. (Since MTK requires Java 1.4 or later, ojdbc14.jar works.)
  2. Follow Oracle's instructions on setting all the appropriate environment variables (for example, PATH and CLASSPATH).
  3. Next, in your MTK migration project, on the Specify Source tab, click the Extract button. This brings up the "Connect to Database" window (see Figure 1):
    Figure 1. Source Database Connection Window
    Source Database Connection Window
  4. Gather the appropriate connection information.

    Since you're connecting to Oracle, you can look in Oracle's configuration file, tnsnames.ora. A sample configuration file is given in Listing 16, below. (This file typically resides in the ORACLE_HOME/network/admin or ORACLE_HOME/net80/admin directory.)
    Listing 16. A sample tnsnames.ora file
    MY_ORACLE_DB =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = brutus)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SID = ORA_ON_BRUTUS)
          (SERVER = DEDICATED)
        )
      )
  5. Identify the entry for the database you wish to migrate.
  6. Finally, enter this info in the Connect to Database window (again, see Figure 1):
    1. The service ID (SID) from tnsnames.ora goes on the first line of the MTK window.
    2. Check the box for Use native JDBC driver.
    3. Use a program like nslookup to resolve the HOST brutus to an IP address, and then enter this and the port into the appropriate fields.
    4. Lastly, enter the username and password credentials.

Now you're ready to connect to your Oracle database through MTK!

Resources

Learn

Get products and technologies

  • Download MTK: Download the easy-to-use tool that allows you to migrate your data from a wide variety of source databases to either DB2 or Informix Dynamic Server, regardless of platform.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

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
ArticleID=186973
ArticleTitle=New capabilities for migrating to DB2 and Informix in IBM Migration Toolkit 1.4.9
publish-date=01042007