Convert DB2 Tables to PostgreSQL
RazorSQL includes a database conversion tool that can convert one or many DB2 tables to PostgreSQL.
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:
Information needed on the screen includes the existing DB2 table to convert, the name of the new PostgreSQL table to create, and whether to execute the conversion directly on a PostgreSQL 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 PostgreSQL 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 PostgreSQL table. Listed below is an example SQL script that would be generated by the RazorSQL DB2 to PostgreSQL conversion tool.
CREATE TABLE STAFF (
"ID" int NOT NULL,
"NAME" varchar(9),
DEPT int,
JOB char(5),
YEARS int,
SALARY decimal(7,2),
COMM decimal(7,2)
);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (10, 'Sanders', 20, 'Mgr ', 7, 98357.50, null);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (20, 'Pernal', 20, 'Sales', 8, 78171.25, 612.45);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (30, 'Marenghi', 38, 'Mgr ', 5, 77506.75, null);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (40, 'O''Brien', 38, 'Sales', 6, 78006.00, 846.55);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (50, 'Hanes', 15, 'Mgr ', 10, 80659.80, null);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (60, 'Quigley', 38, 'Sales', null, 66808.30, 650.25);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (70, 'Rothman', 15, 'Sales', 7, 76502.83, 1152.00);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (80, 'James', 20, 'Clerk', null, 43504.60, 128.20);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (90, 'Koonitz', 42, 'Sales', 6, 38001.75, 1386.70);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (100, 'Plotz', 42, 'Mgr ', 7, 78352.80, null);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (110, 'Ngan', 15, 'Clerk', 5, 42508.20, 206.60);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (120, 'Naughton', 38, 'Clerk', null, 42954.75, 180.00);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (130, 'Yamaguchi', 42, 'Clerk', 6, 40505.90, 75.60);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (140, 'Fraye', 51, 'Mgr ', 6, 91150.00, null);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (150, 'Williams', 51, 'Sales', 6, 79456.50, 637.65);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (160, 'Molinare', 10, 'Mgr ', 7, 82959.20, null);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (170, 'Kermisch', 15, 'Clerk', 4, 42258.50, 110.10);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (180, 'Abrahams', 38, 'Clerk', 3, 37009.75, 236.50);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (190, 'Sneider', 20, 'Clerk', 8, 34252.75, 126.50);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (200, 'Scoutten', 42, 'Clerk', null, 41508.60, 84.20);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (210, 'Lu', 10, 'Mgr ', 10, 90010.00, null);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (220, 'Smith', 51, 'Sales', 7, 87654.50, 992.80);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (230, 'Lundquist', 51, 'Clerk', 3, 83369.80, 189.65);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (240, 'Daniels', 10, 'Mgr ', 5, 79260.25, null);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (250, 'Wheeler', 51, 'Clerk', 6, 74460.00, 513.30);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (260, 'Jones', 10, 'Mgr ', 12, 81234.00, null);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (270, 'Lea', 66, 'Mgr ', 9, 88555.50, null);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (280, 'Wilson', 66, 'Sales', 9, 78674.50, 811.50);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (290, 'Quill', 84, 'Mgr ', 10, 89818.00, null);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (300, 'Davis', 84, 'Sales', 5, 65454.50, 806.10);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (310, 'Graham', 66, 'Sales', 13, 71000.00, 200.30);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (320, 'Gonzales', 66, 'Sales', 4, 76858.20, 844.00);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (330, 'Burke', 66, 'Clerk', 1, 49988.00, 55.50);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (340, 'Edwards', 84, 'Sales', 7, 67844.00, 1285.00);
INSERT INTO STAFF("ID", "NAME", DEPT, JOB, YEARS, SALARY, COMM) VALUES (350, 'Gafney', 84, 'Clerk', 5, 43030.50, 188.00);
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 PostgreSQL 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);
