 | Level: Introductory Hemant Bhatia, Software Engineer, IBM
Nicholas Geib, Software Engineer, IBM
04 Jan 2007 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.
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:
-
Range partitioning
-
Data compression
-
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:
-
Procedures and functions
-
Triggers
-
Cursors
-
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:
-
Migrating to DB2 UDB on iSeries
-
Windows silent install
-
JRE bundling
-
MTK FAQ
-
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:
- Support for migrating to version V5R4M0 of DB2 for i5/OS
- 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
- 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.
- 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.
- 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:
- 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.)
- 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:
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.
- First, download and install the Oracle JDBC driver. (Since MTK requires
Java 1.4 or later, ojdbc14.jar works.)
- Follow Oracle's instructions on setting all the appropriate environment
variables (for example, PATH and CLASSPATH).
- 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
- 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)
)
)
|
- Identify the entry for the database you wish
to migrate.
- Finally, enter this info in the Connect to Database window (again, see
Figure 1):
- The service ID (SID) from tnsnames.ora goes on the first line of the MTK
window.
- Check the box for Use native JDBC driver.
- 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.
- 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
About the authors | | | 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 is a Software Engineer at IBM's Lenexa lab working on the IBM Migration Toolkit. |
Rate this page
| |
