Convert DB2 Tables to MySQL

RazorSQL includes a database conversion tool that can convert one or many DB2 tables to MySQL. 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 MySQL Conversion

Information needed on the screen includes the existing DB2 table to convert, the name of the new MySQL table to create, and whether to execute the conversion directly on a MySQL 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 MySQL 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 MySQL table. Listed below is an example SQL script that would be generated by the RazorSQL DB2 to MySQL conversion tool.

CREATE TABLE SALES ( SALES_DATE date, SALES_PERSON varchar(15), REGION varchar(15), SALES int ); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2005-12-31', 'LUCCHESSI', 'Ontario-South', 1); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2005-12-31', 'LEE', 'Ontario-South', 3); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2005-12-31', 'LEE', 'Quebec', 1); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2005-12-31', 'LEE', 'Manitoba', 2); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2005-12-31', 'GOUNOT', 'Quebec', 1); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-29', 'LUCCHESSI', 'Ontario-South', 3); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-29', 'LUCCHESSI', 'Quebec', 1); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-29', 'LEE', 'Ontario-South', 2); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('1996-03-29', 'LEE', 'Ontario-North', 2); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-29', 'LEE', 'Quebec', 3); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-29', 'LEE', 'Manitoba', 5); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-29', 'GOUNOT', 'Ontario-South', 3); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-29', 'GOUNOT', 'Quebec', 1); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-29', 'GOUNOT', 'Manitoba', 7); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-30', 'LUCCHESSI', 'Ontario-South', 1); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-30', 'LUCCHESSI', 'Quebec', 2); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-30', 'LUCCHESSI', 'Manitoba', 1); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-30', 'LEE', 'Ontario-South', 7); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-30', 'LEE', 'Ontario-North', 3); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-30', 'LEE', 'Quebec', 7); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-30', 'LEE', 'Manitoba', 4); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-30', 'GOUNOT', 'Ontario-South', 2); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-30', 'GOUNOT', 'Quebec', 18); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-31', 'GOUNOT', 'Manitoba', 1); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-31', 'LUCCHESSI', 'Manitoba', 1); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-31', 'LEE', 'Ontario-South', 14); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-31', 'LEE', 'Ontario-North', 3); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-31', 'LEE', 'Quebec', 7); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-31', 'LEE', 'Manitoba', 3); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-31', 'GOUNOT', 'Ontario-South', 2); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-03-31', 'GOUNOT', 'Quebec', 1); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-04-01', 'LUCCHESSI', 'Ontario-South', 3); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-04-01', 'LUCCHESSI', 'Manitoba', 1); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-04-01', 'LEE', 'Ontario-South', 8); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-04-01', 'LEE', 'Ontario-North', null); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-04-01', 'LEE', 'Quebec', 8); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-04-01', 'LEE', 'Manitoba', 9); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-04-01', 'GOUNOT', 'Ontario-South', 3); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-04-01', 'GOUNOT', 'Ontario-North', 1); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-04-01', 'GOUNOT', 'Quebec', 3); INSERT INTO SALES(SALES_DATE, SALES_PERSON, REGION, SALES) VALUES ('2006-04-01', 'GOUNOT', 'Manitoba', 7);

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 MySQL 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 varchar(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 varchar(24) NOT NULL, DEPTNO char(3) NOT NULL, RESPEMP char(6) NOT NULL, PRSTAFF decimal(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, '2002-01-01', '2003-02-01', null); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('AD3110', 'GENERAL ADMIN SYSTEMS', 'D21', '000070', 6.00, '2002-01-01', '2003-02-01', 'AD3100'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('AD3111', 'PAYROLL PROGRAMMING', 'D21', '000230', 2.00, '2002-01-01', '2003-02-01', 'AD3110'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('AD3112', 'PERSONNEL PROGRAMMING', 'D21', '000250', 1.00, '2002-01-01', '2003-02-01', 'AD3110'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('AD3113', 'ACCOUNT PROGRAMMING', 'D21', '000270', 2.00, '2002-01-01', '2003-02-01', 'AD3110'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('IF1000', 'QUERY SERVICES', 'C01', '000030', 2.00, '2002-01-01', '2003-02-01', null); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('IF2000', 'USER EDUCATION', 'C01', '000030', 1.00, '2002-01-01', '2003-02-01', null); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('MA2100', 'WELD LINE AUTOMATION', 'D01', '000010', 12.00, '2002-01-01', '2003-02-01', null); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('MA2110', 'W L PROGRAMMING', 'D11', '000060', 9.00, '2002-01-01', '2003-02-01', 'MA2100'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('MA2111', 'W L PROGRAM DESIGN', 'D11', '000220', 2.00, '2002-01-01', '1982-12-01', 'MA2110'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('MA2112', 'W L ROBOT DESIGN', 'D11', '000150', 3.00, '2002-01-01', '1982-12-01', 'MA2110'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('MA2113', 'W L PROD CONT PROGS', 'D11', '000160', 3.00, '2002-02-15', '1982-12-01', 'MA2110'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('OP1000', 'OPERATION SUPPORT', 'E01', '000050', 6.00, '2002-01-01', '2003-02-01', null); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('OP1010', 'OPERATION', 'E11', '000090', 5.00, '2002-01-01', '2003-02-01', 'OP1000'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('OP2000', 'GEN SYSTEMS SERVICES', 'E01', '000050', 5.00, '2002-01-01', '2003-02-01', null); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('OP2010', 'SYSTEMS SUPPORT', 'E21', '000100', 4.00, '2002-01-01', '2003-02-01', 'OP2000'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('OP2011', 'SCP SYSTEMS SUPPORT', 'E21', '000320', 1.00, '2002-01-01', '2003-02-01', 'OP2010'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('OP2012', 'APPLICATIONS SUPPORT', 'E21', '000330', 1.00, '2002-01-01', '2003-02-01', 'OP2010'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('OP2013', 'DB/DC SUPPORT', 'E21', '000340', 1.00, '2002-01-01', '2003-02-01', 'OP2010'); INSERT INTO PROJECT(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ) VALUES ('PL2100', 'WELD LINE PLANNING', 'B01', '000020', 1.00, '2002-01-01', '2002-09-15', '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);