SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from IBM DB2 to PostgreSQL:
Databases:
Data type mapping between DB2 and PostgreSQL:
Converting CREATE PROCEDURE statement from IBM DB2 to PostgreSQL:
DB2 | PostgreSQL | ||
1 | CREATE OR REPLACE PROCEDURE name | CREATE OR REPLACE FUNCTION![]() |
|
2 | (IN | OUT | INOUT param datatype(length), …) | (IN | OUT | INOUT param datatype(length), …) | |
3 | LANGUAGE SQL | Removed | |
4 | BEGIN procedure_body END | RETURNS VOID AS $$ BEGIN procedure_body END; $$ LANGUAGE plpgsql; |
For more information, see Conversion of Procedural SQL Statements.
Converting procedural SQL statements used in stored procedures, functions and triggers from IBM DB2 to PostgreSQL:
Variable declarations:
DB2 | PostgreSQL | ||
1 | Declarations are inside BEGIN END block | Declarations are before BEGIN END block | |
2 | DECLARE var datatype DEFAULT value | var datatype DEFAULT value | |
3 | DECLARE var, var2, … datatype | var datatype; var2 datatype; … | |
4 | DECLARE cur CURSOR FOR query | Cursor declaration | cur CURSOR FOR query |
Variable Assignments:
DB2 | PostgreSQL | ||
1 | SET v1 = value | v1 := value | |
2 | SET v1 = value, v2 = value2, … | v1 := value; v2 := value2; … | |
3 | SET (v1, v2, …) = (value, value2, …) | v1 := value; v2 := value2; … | |
4 | SET (v1, v2, …) = (SELECT c1, c2, …) | SELECT c1, c2, … INTO v1, v2, … |
Cursor operations:
DB2 | PostgreSQL | ||
1 | DECLARE cur CURSOR FOR query | Cursor declaration | cur CURSOR FOR query |
2 | DECLARE cur CURSOR WITH RETURN ... | Return result set | cur REFCURSOR |
3 | DECLARE cur WITH RETURN FOR stmt PREPARE stmt FROM 'query_string' | Dynamic cursors | OPEN cur FOR EXECUTE 'query_string' |