Differences

This shows you the differences between two versions of the page.

db2-to-postgresql [January 04, 2018 2:27 am]
sqlines
db2-to-postgresql [May 18, 2021 3:07 am] (current)
sqlines
Line 1: Line 1:
====== IBM DB2 to PostgreSQL Migration ====== ====== IBM DB2 to PostgreSQL Migration ======
-SQLines open source tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from IBM DB2 to PostgreSQL:+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:
  * [[/sqldata/db2-to-postgresql|SQLines Data]] - Data transfer, schema migration and validation tool   * [[/sqldata/db2-to-postgresql|SQLines Data]] - Data transfer, schema migration and validation tool
Line 8: Line 8:
**Databases:** **Databases:**
-  * IBM DB2 for LUW and z/OS 11.x, 10.x, 9.x, 8.x and 7.x +  * IBM DB2 for LUW, z/OS and OS/400 11.x, 10.x, 9.x, 8.x and 7.x 
-  * PostgreSQL 10.x and 9.x  +  * PostgreSQL 13.x, 12.x, 11.x, 10.x and 9.x  
====== Migration Reference ====== ====== Migration Reference ======
  * [[/db2-to-postgresql#data-types|Data Types]]   * [[/db2-to-postgresql#data-types|Data Types]]
 +  * [[/db2-to-postgresql#create-procedure-statement|CREATE PROCEDURE]]
 +  * [[/db2-to-postgresql#procedural-sql-statements|Procedural SQL Statements]]
===== Data Types ====== ===== Data Types ======
Line 47: Line 49:
| 26 | VARGRAPHIC(//n//) | Variable-length UTF-16 string, 1 <= //n// <= 16336 | VARCHAR(//n//) | | 26 | VARGRAPHIC(//n//) | Variable-length UTF-16 string, 1 <= //n// <= 16336 | VARCHAR(//n//) |
| 27 | XML | XML data | XML | | 27 | XML | XML data | XML |
 +
 +===== CREATE PROCEDURE Statement =====
 +
 +Converting CREATE PROCEDURE statement from IBM DB2 to PostgreSQL:
 +
 +| | **DB2** || **PostgreSQL** |
 +| 1 | [[/db2-to-postgresql/create_procedure|CREATE OR REPLACE PROCEDURE]] //name// || [[/db2-to-postgresql/create_procedure|CREATE OR REPLACE FUNCTION]]{{:exclamation.png|}} //name// |
 +| 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 [[/db2-to-postgresql#procedural-sql-statements|Conversion of Procedural SQL Statements]].
 +
 +===== Procedural SQL Statements =====
 +
 +Converting procedural SQL statements used in stored procedures, functions and triggers from IBM DB2 to PostgreSQL:
 +
 +Variable declarations:
 +
 +| | **DB2** || **PostgreSQL** |
 +| 1 | [[/db2-to-postgresql/declare|Declarations are inside BEGIN END block]] || [[/db2-to-postgresql/declare|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 | [[/db2-to-postgresql/set|SET v1 = value]] || [[/db2-to-postgresql/set|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 | [[/db2-to-postgresql/cursor_with_return|DECLARE cur CURSOR WITH RETURN ...]] | Return result set | [[/db2-to-postgresql/cursor_with_return|cur REFCURSOR]] |
 +| 3 | [[/db2-to-postgresql/prepared_cursor|DECLARE cur WITH RETURN FOR stmt]] \\ [[/db2-to-postgresql/prepared_cursor|PREPARE stmt FROM 'query_string']] | Dynamic cursors | [[/db2-to-postgresql/prepared_cursor|OPEN cur FOR EXECUTE 'query_string']] |
 +