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:
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
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
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'))!
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
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!
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
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 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
DELETE event for the same table, Oracle's
REFERENCING PARENT syntax,
INSTEAD OF triggers.
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:
FETCHstatements 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 (
LOOP, for example) must not that contain
- 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 ;
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
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:
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;
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
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>"
-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.
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 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 18.104.22.168 supports MySQL
A prototype of MTK 22.214.171.124 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
- 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!
- "Migrating to IBM database servers gets easier with the latest MTK release" (developerWorks, March 2006): New to the Migration Toolkit? Learn more about it in this article.
- Porting to DB2 for Linux, UNIX, and Windows site: Looking for a strategy to migrate your database and application? Find the information you need to port an application and its data from other database management systems to DB2.
- IDS InfoCenter: Learn more about IDS SPL (IDS InfoCenter > Guide to SQL: Syntax > SPLs Statements).
- Experience report: Database Migration: Read about using MTK to migrate to DB2 on System i.
- developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
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.
- DB2 for Linux, UNIX, and Windows forum: Send us your questions (to distinguish from other DB2 discussion threads, please prepend your MKT questions with "migrate-team").
- Participate in developerWorks blogs and get involved in the developerWorks community.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.