SQLines Tools db2-to-postgresql
https://www.sqlines.com/
2026-04-10T06:15:44+00:00SQLines Tools
https://www.sqlines.com/
https://www.sqlines.com/lib/images/favicon.icotext/html2022-12-22T17:17:31+00:00CREATE PROCEDURE Statement - IBM DB2 to PostgreSQL Migration
https://www.sqlines.com/db2-to-postgresql/create_procedure?rev=1671729451&do=diff
SQLines SQL Converter can help you convert IBM DB2 stored procedures to PostgreSQL:
IBM DB2:
--#SET TERMINATOR /
CREATE PROCEDURE sp_updateDeptLoc (IN p_loc VARCHAR(25), IN p_deptno CHARACTER(5))
LANGUAGE SQL
BEGIN
UPDATE dept SET location = p_loc WHERE deptno = p_deptno;
END
/text/html2023-12-14T00:20:31+00:00DECLARE CURSOR WITH RETURN - IBM DB2 to PostgreSQL Migration
https://www.sqlines.com/db2-to-postgresql/cursor_with_return?rev=1702513231&do=diff
DB2 allows you to return one or more result sets from a stored procedure. You can declare a cursor specifying WITH RETURN clause that allows you to open the cursor and return its rows to the application, for example:
IBM DB2:
--#SET TERMINATOR /
CREATE PROCEDURE sp_selectDept(IN p_deptno CHARACTER(5))
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE cur CURSOR WITH RETURN FOR
SELECT * FROM dept WHERE deptno = p_deptno;
OPEN cur;
END
/text/html2021-05-17T19:37:57+00:00DECLARE Statement - IBM DB2 to PostgreSQL Migration
https://www.sqlines.com/db2-to-postgresql/declare?rev=1621280277&do=diff
In DB2 the DECLARE statement allows you to declare variables, handlers, exception and cursors in stored procedures. You have to use it inside a BEGIN-END block, for example:
IBM DB2:
--#SET TERMINATOR /
CREATE PROCEDURE sp_updateDeptLoc(IN p_loc VARCHAR(25), IN p_deptno CHARACTER(5))
LANGUAGE SQL
BEGIN
DECLARE v_loc VARCHAR(25);
DECLARE v_deptno CHAR(5);
SET v_loc = p_loc;
SET v_deptno = p_deptno;
UPDATE dept SET location = v_loc WHERE deptno = v_deptno;
END
…text/html2021-05-18T10:05:10+00:00Dynamic SQL using Prepared Cursors - IBM DB2 to PostgreSQL Migration
https://www.sqlines.com/db2-to-postgresql/prepared_cursor?rev=1621332310&do=diff
In DB2 you can use the prepared statements to dynamically (at runtime) define the SQL query for a cursor, for example:
IBM DB2:
--#SET TERMINATOR /
CREATE PROCEDURE sp_selectDept(IN p_deptno CHARACTER(5))
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE query VARCHAR(50);
DECLARE cur CURSOR WITH RETURN FOR s1;
SET query = 'SELECT * FROM dept'; -- or some complex logic to dynamically build SQL here
PREPARE s1 FROM query;
OPEN cur;
END
/text/html2021-05-14T10:19:21+00:00SET Statement - IBM DB2 to PostgreSQL Migration
https://www.sqlines.com/db2-to-postgresql/set?rev=1620987561&do=diff
In DB2 the SET statement allows you to assign values to variables in stored procedures, functions or triggers, for example:
IBM DB2:
CREATE PROCEDURE sp_updateDeptLoc(IN p_loc VARCHAR(25), IN p_deptno CHARACTER(5))
LANGUAGE SQL
BEGIN
DECLARE v_loc VARCHAR(25);
DECLARE v_deptno CHAR(5);
SET v_loc = p_loc;
SET v_deptno = p_deptno;
UPDATE dept SET location = v_loc WHERE deptno = v_deptno;
END
/text/html2022-09-12T16:38:24+00:00VALUES Statement - IBM DB2 to PostgreSQL Migration
https://www.sqlines.com/db2-to-postgresql/values?rev=1663000704&do=diff
In DB2 you can use the VALUES statement to return one or more rows with one or more columns. In PostgreSQL you can use multiple SELECT statements with UNION ALL operator.
DB2:
-- Return 3 rows with 1 column in each row
VALUES 1, 2, 3;
PostgreSQL: