Convert DB2 Tables to Oracle

RazorSQL includes a database conversion tool that can convert one or many DB2 tables to Oracle. The conversion tool can be accessed via the following menu option in RazorSQL:

DB Tools -> Database Conversion

Inside the Database Conversion menu there are options for converting one table or to convert a selection of tables within a specific schema or database.

Selecting the Single Table option brings up the following screen:

DB2 Oracle Conversion

Information needed on the screen includes the existing DB2 table to convert, the name of the new Oracle table to create, and whether to execute the conversion directly on a Oracle database connection or whether to generate an SQL script file with the DDL and SQL insert statements for the conversion.

After clicking the convert button, RazorSQL will generate a Oracle specific create table statement for the DB2 table. It will also generate an SQL insert statement for each row in the DB2 table that can be executed against the new Oracle table. Listed below is an example SQL script that would be generated by the RazorSQL DB2 to Oracle conversion tool.

CREATE TABLE PRODUCT ( PID varchar2(10) NOT NULL, NAME varchar2(128), PRICE number(30,2), PROMOPRICE number(30,2), PROMOSTART date, PROMOEND date, DESCRIPTION clob, PRIMARY KEY (PID) ); INSERT INTO PRODUCT(PID, NAME, PRICE, PROMOPRICE, PROMOSTART, PROMOEND, DESCRIPTION) VALUES ('100-100-01', 'Snow Shovel, Basic 22 inch', 9.99, 7.25, to_date('11/19/2004', 'MM/DD/YYYY'), to_date('12/19/2004', 'MM/DD/YYYY'), 'Snow Shovel, Basic 22 inch
Basic Snow Shovel, 22 inches wide, straight handle with D-Grip
9.991 kg'); INSERT INTO PRODUCT(PID, NAME, PRICE, PROMOPRICE, PROMOSTART, PROMOEND, DESCRIPTION) VALUES ('100-101-01', 'Snow Shovel, Deluxe 24 inch', 19.99, 15.99, to_date('12/18/2005', 'MM/DD/YYYY'), to_date('02/28/2006', 'MM/DD/YYYY'), 'Snow Shovel, Deluxe 24 inch
A Deluxe Snow Shovel, 24 inches wide, ergonomic curved handle with D-Grip
19.992 kg'); INSERT INTO PRODUCT(PID, NAME, PRICE, PROMOPRICE, PROMOSTART, PROMOEND, DESCRIPTION) VALUES ('100-103-01', 'Snow Shovel, Super Deluxe 26 inch', 49.99, 39.99, to_date('12/22/2005', 'MM/DD/YYYY'), to_date('02/22/2006', 'MM/DD/YYYY'), 'Snow Shovel, Super Deluxe 26 inch
Super Deluxe Snow Shovel, 26 inches wide, ergonomic battery heated curved handle with upgraded D-Grip
49.993 kg'); INSERT INTO PRODUCT(PID, NAME, PRICE, PROMOPRICE, PROMOSTART, PROMOEND, DESCRIPTION) VALUES ('100-201-01', 'Ice Scraper, Windshield 4 inch', 3.99, null, null, null, 'Ice Scraper, Windshield 4 inch
Basic Ice Scraper 4 inches wide, foam handle
3.99');

When electing to convert multiple tables, in addition to the create table statement and SQL insert statements, RazorSQL can generate alter table statements to set up foreign key relationships. Listed below is an example DB2 to Oracle conversion script that includes the alter table statement to create the foreign key on the Project table that references the Department table.

CREATE TABLE DEPARTMENT ( DEPTNO char(3) NOT NULL, DEPTNAME varchar2(36) NOT NULL, MGRNO char(6), ADMRDEPT char(3) NOT NULL, LOCATION char(16), PRIMARY KEY (DEPTNO) ); CREATE TABLE PROJECT ( PROJNO char(6) NOT NULL, PROJNAME varchar2(24) NOT NULL, DEPTNO char(3) NOT NULL, RESPEMP char(6) NOT NULL, PRSTAFF number(5,2), PRSTDATE date, PRENDATE date, MAJPROJ char(6), PRIMARY KEY (PROJNO) ); INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('A00', 'SPIFFY COMPUTER SERVICE DIV.', '000010', 'A00', null); INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('B01', 'PLANNING', '000020', 'A00', null); INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('C01', 'INFORMATION CENTER', '000030', 'A00', null); INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('D01', 'DEVELOPMENT CENTER', null, 'A00', null); INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('D11', 'MANUFACTURING SYSTEMS', '000060', 'D01', null); INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('D21', 'ADMINISTRATION SYSTEMS', '000070', 'D01', null); INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('E01', 'SUPPORT SERVICES', '000050', 'A00', null); INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('E11', 'OPERATIONS', '000090', 'E01', null); INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('E21', 'SOFTWARE SUPPORT', '000100', 'E01', null); INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('F22', 'BRANCH OFFICE F2', null, 'E01', null); INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('G22', 'BRANCH OFFICE G2', null, 'E01', null); INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('H22', 'BRANCH OFFICE H2', null, 'E01', null); INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('I22', 'BRANCH OFFICE I2', null, 'E01', null); INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('J22', 'BRANCH OFFICE J2', null, 'E01', null); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('AD3100', 'ADMIN SERVICES', 'D01', '000010', 6.50, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), null); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('AD3110', 'GENERAL ADMIN SYSTEMS', 'D21', '000070', 6.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), 'AD3100'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('AD3111', 'PAYROLL PROGRAMMING', 'D21', '000230', 2.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), 'AD3110'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('AD3112', 'PERSONNEL PROGRAMMING', 'D21', '000250', 1.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), 'AD3110'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('AD3113', 'ACCOUNT PROGRAMMING', 'D21', '000270', 2.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), 'AD3110'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('IF1000', 'QUERY SERVICES', 'C01', '000030', 2.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), null); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('IF2000', 'USER EDUCATION', 'C01', '000030', 1.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), null); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('MA2100', 'WELD LINE AUTOMATION', 'D01', '000010', 12.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), null); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('MA2110', 'W L PROGRAMMING', 'D11', '000060', 9.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), 'MA2100'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('MA2111', 'W L PROGRAM DESIGN', 'D11', '000220', 2.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('12/01/1982', 'MM/DD/YYYY'), 'MA2110'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('MA2112', 'W L ROBOT DESIGN', 'D11', '000150', 3.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('12/01/1982', 'MM/DD/YYYY'), 'MA2110'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('MA2113', 'W L PROD CONT PROGS', 'D11', '000160', 3.00, to_date('02/15/2002', 'MM/DD/YYYY'), to_date('12/01/1982', 'MM/DD/YYYY'), 'MA2110'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('OP1000', 'OPERATION SUPPORT', 'E01', '000050', 6.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), null); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('OP1010', 'OPERATION', 'E11', '000090', 5.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), 'OP1000'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('OP2000', 'GEN SYSTEMS SERVICES', 'E01', '000050', 5.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), null); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('OP2010', 'SYSTEMS SUPPORT', 'E21', '000100', 4.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), 'OP2000'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('OP2011', 'SCP SYSTEMS SUPPORT', 'E21', '000320', 1.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), 'OP2010'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('OP2012', 'APPLICATIONS SUPPORT', 'E21', '000330', 1.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), 'OP2010'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('OP2013', 'DB/DC SUPPORT', 'E21', '000340', 1.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('02/01/2003', 'MM/DD/YYYY'), 'OP2010'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('PL2100', 'WELD LINE PLANNING', 'B01', '000020', 1.00, to_date('01/01/2002', 'MM/DD/YYYY'), to_date('09/15/2002', 'MM/DD/YYYY'), 'MA2100'); ALTER TABLE DEPARTMENT ADD FOREIGN KEY (MGRNO) REFERENCES EMPLOYEE (EMPNO); ALTER TABLE DEPARTMENT ADD FOREIGN KEY (ADMRDEPT) REFERENCES DEPARTMENT (DEPTNO); ALTER TABLE PROJECT ADD FOREIGN KEY (DEPTNO) REFERENCES DEPARTMENT (DEPTNO); ALTER TABLE PROJECT ADD FOREIGN KEY (RESPEMP) REFERENCES EMPLOYEE (EMPNO); ALTER TABLE PROJECT ADD FOREIGN KEY (MAJPROJ) REFERENCES PROJECT (PROJNO);