DB2 to PostgreSQL: migration of transactions. Part 3

While migrating from IBM DB2 LUW to PostgreSQL, the use of handlers with transactions can significantly influence the conversion process.

Transactions in databases are one of the basic concepts. It means a set of statements for changing data in the database. And most often when talking about transactions, we are talking about ACID properties (Atomicity, Consistency, Isolation, Durability), the presence of which ensures the consistency, integrity and reliability of data in the database.

Transactions in IBM DB2 and PostgreSQL comply with ACID principles. Both DB2 and PostgreSQL support standard transaction management instructions such as COMMIT and ROLLBACK. These instructions allow you to explicitly manage the result of a transaction, either by committing changes or rolling them back. Also, both databases use mechanisms of concurrency control with the help of locks, multi-version concurrency control (MVCC).

That being said, there are also certain differences in transactions in DB2 and PostgreSQL, for example:

DB2 LUW and PostgreSQL offer distinct isolation levels to manage transactions. In DB2 LUW, you can choose from Read Stability, Cursor Stability, Repeatable Read, and Uncommitted read isolation levels. In contrast, PostgreSQL provides options such as Read Uncommitted, Read Committed, Repeatable Read, and Serializable isolation levels.
Both DB2 LUW and PostgreSQL use a locking mechanism known as Multi-Version Concurrency Control (MVCC), which allows readers to access a consistent snapshot of the data while writers modify it, but PostgreSQL's locking mechanism is more flexible and allows fine-grained control over locking behavior.

As part of the handler migration from DB2 to Postgres, we should also note that in addition to the standard transaction management commands COMMIT, ROLLBACK and SAVEPOINT, the BEGIN/END and EXCEPTION blocks can be used for more fine-tuning. Moreover, the transaction cannot be completed in the BEGIN/END block with an EXCEPTION, i.e. any explicit use of transaction management commands is unacceptable.

When migrating IBM DB2 to Postgres, it is of great importance where these commands were used, in the body of the procedure or in the handler block. Before learning the example below, please, note that a statement provided implies any command like SELECT, UPDATE, DELETE, INSERT INTO, CALL, FETCH, OPEN, CLOSE, as well as assignment commands, etc. So, let’s find out how the command position affects its execution:

1) If neither a procedure block nor a handler block includes transactions (let’s generalize that there will always be a ROLLBACK in the handler), then in PostgreSQL we should wrap each statement in a BEGIN/END block with an EXCEPTION (add RETURN, if in DB2 it was an EXIT handler). Execution results made before the statement that caused the error are not rolled back. Here is a comparison between DB2 and PostgreSQL execution in IBM DB2:

CREATE PROCEDURE my_procedure(OUT p_sql_message varchar(20)
)
BEGIN
  DECLARE l_count INTEGER;
  DECLARE SQLSTATE char(5) DEFAULT '00000';
 DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET p_sql_message = 'SQLSTATE: '||SQLSTATE;
  END;
  INSERT INTO tab1(col1, col2) values (1,1);
  SET l_count = 1/0;
  INSERT INTO tab1(col1, col2) values (2,2);
END;

PostgreSQL:

CREATE OR REPLACE PROCEDURE my_procedure() 
AS $$
DECLARE 
  l_count INTEGER;
BEGIN

BEGIN
  INSERT INTO tab1(col1, col2) values (1,1);
EXCEPTION
  WHEN OTHERS THEN
   p_sql_message := 'SQLSTATE: '||SQLSTATE;
  RETURN;
END;

BEGIN
  l_count := 1/0;
EXCEPTION
  WHEN OTHERS THEN
   p_sql_message := 'SQLSTATE: '||SQLSTATE;
   RETURN;
END;

BEGIN
  INSERT INTO tab1(col1, col2) values (2,2);
EXCEPTION
  WHEN OTHERS THEN
   p_sql_message := 'SQLSTATE: '||SQLSTATE;
END;
$$ LANGUAGE plpgsql;

2) If there is a ROLLBACK in the handler, but there are no transactions in the body of the procedure, then it is possible to make one general EXCEPTION at the end (with ROLLBACK, but without RETURN). All execution results made before the statement that caused the error are rolled back. The DB2 and PostgreSQL examples are below:

CREATE PROCEDURE my_procedure(OUT p_sql_message varchar(20)
)
BEGIN
  DECLARE l_count INTEGER;
  DECLARE SQLSTATE char(5) DEFAULT '00000';
 DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_sql_message = 'SQLSTATE: '||SQLSTATE;
  END;
  INSERT INTO tab1(col1, col2) values (1,1);
  SET l_count = 1/0;
  INSERT INTO tab1(col1, col2) values (2,2);
END;

CREATE OR REPLACE PROCEDURE my_procedure() 
AS $$
DECLARE 
  l_count INTEGER;
BEGIN
  INSERT INTO tab1(col1, col2) values (1,1);
  l_count := 1/0;
  INSERT INTO tab1(col1, col2) values (2,2);
EXCEPTION
  WHEN OTHERS THEN
   ROLLBACK;
   p_sql_message := 'SQLSTATE: '||SQLSTATE;
END;
$$ LANGUAGE plpgsql;

Actually, the ROLLBACK can be omitted here, because all the execution results made in the BEGIN/END block will be removed automatically without specifying the command. In our example we’ll leave it for making things clear.

3) If there is a ROLLBACK in the handler and there are transactions in the body of the procedure, then it is necessary to wrap the statements before this command inside the procedure with one common BEGIN/END block with EXCEPTION (and RETURN, if it was an EXIT handler in DB2), and after it as well, if there is further logic that can lead to an error. The comparison in DB2 and PostgreSQL is below:

CREATE PROCEDURE my_procedure(OUT p_sql_message varchar(20)
)
BEGIN
  DECLARE l_count INTEGER;
  DECLARE SQLSTATE char(5) DEFAULT '00000';
 DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_sql_message = 'SQLSTATE: '||SQLSTATE;
  END;
  INSERT INTO tab1(col1, col2) values (1,1);
  COMMIT;
  SET l_count = 1/0;
  INSERT INTO tab1(col1, col2) values (2,2);
END;

CREATE OR REPLACE PROCEDURE my_procedure() 
AS $$
DECLARE 
  l_count INTEGER;
BEGIN

BEGIN
  INSERT INTO tab1(col1, col2) values (1,1);
EXCEPTION
  WHEN OTHERS THEN
   ROLLBACK;
   p_sql_message := 'SQLSTATE: '||SQLSTATE;
  RETURN;
END;

COMMIT;
BEGIN
  l_count := 1/0;
  INSERT INTO tab1(col1, col2) values (2,2);
EXCEPTION
  WHEN OTHERS THEN
   ROLLBACK;
   p_sql_message := 'SQLSTATE: '||SQLSTATE;
END;
$$ LANGUAGE plpgsql;

Based on the DB2 and PostgreSQL examples it is clear that transaction management commands are not left inside the BEGIN/END block with EXCEPTION, but rather taken outside of it. Their use is allowed only inside the EXCEPTION error handling block itself. If there are several such commands, each of them will thereby divide the procedure body into BEGIN/END blocks with EXCEPTION before and after itself.

4) If there is no ROLLBACK in the handler, but there are transactions in the body of the procedure, then we should wrap each statement before this command in a BEGIN/END block with an EXCEPTION (and RETURN, if it was an EXIT handler in DB2). The same should be done after the command, if there is further logic that can lead to an error. Here is a comparison between DB2 and PostgreSQL results:

CREATE PROCEDURE my_procedure(OUT p_sql_message varchar(20)
)
BEGIN
  DECLARE l_count INTEGER;
  DECLARE SQLSTATE char(5) DEFAULT '00000';
 DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET p_sql_message = 'SQLSTATE: '||SQLSTATE;
  END;
  INSERT INTO tab1(col1, col2) values (1,1);
  SET l_count = 1/0;
  INSERT INTO tab1(col1, col2) values (2,2);
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE my_procedure() 
AS $$
DECLARE 
  l_count INTEGER;
BEGIN

BEGIN
  INSERT INTO tab1(col1, col2) values (1,1);
EXCEPTION
  WHEN OTHERS THEN
   p_sql_message := 'SQLSTATE: '||SQLSTATE;
  RETURN;
END;

BEGIN
  l_count := 1/0;
EXCEPTION
  WHEN OTHERS THEN
   p_sql_message := 'SQLSTATE: '||SQLSTATE;
   RETURN;
END;
BEGIN
  INSERT INTO tab1(col1, col2) values (2,2);
EXCEPTION
  WHEN OTHERS THEN
   p_sql_message := 'SQLSTATE: '||SQLSTATE;
RETURN;
END;

COMMIT;
$$ LANGUAGE plpgsql;

In PostgreSQL examples above the COMMIT command can be omitted, because by default PostgreSQL executes transactions in “autocommit” mode, that is, each instruction is executed in its own transaction, and commit is executed at the end of the instruction (in case execution is successful, otherwise rollback is performed).

For example, in the call stack CALL proc1() → CALL proc2() → CALL proc3(), the second and third procedures can manage transactions. But if there are transactions in the called and it's calling procedures, the nested transactions call should be perceived as if there were a separate COMMIT or ROLLBACK command, and, therefore, it should not be allowed to use in the BEGIN/END with the EXCEPTION block and take this call outside of it, otherwise it provides an error “ERROR: invalid_transaction_termination SQL state: 2D000”. Let's take it as an example.

Suppose we have 2 procedures in DB2, my_procedure_child with an explicit COMMIT and calling it my_procedure_parents with a handler:

CREATE PROCEDURE my_procedure_child(OUT p_sql_message varchar(20))
BEGIN   
  INSERT INTO tab1(col1, col2) values (1,1);
  COMMIT;
END;


CREATE PROCEDURE my_procedure_parent(OUT p_sql_message varchar(20))
BEGIN
  DECLARE SQLSTATE char(5) DEFAULT '00000';
    DECLARE ret varchar(20);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
         set p_sql_message = sqlstate;
        END;
    INSERT INTO tab1(col1, col2) VALUES(2,2);
    call mp_sp02(ret);

    if ret is null then
        SET p_sql_message= 'mp_sp02 success!';
    else
        SET p_sql_message= 'mp_sp02 failed!';
    end if;

END;

As a result, two rows should be added to tab1 in DB2:

Col1	Col2
1	2
2	2

Ispirer Toolkit which implements the logic described above, converts it to PostgreSQL as follows:

CREATE PROCEDURE mp_sp02_test(INOUT p_sql_message VARCHAR(20) DEFAULT NULL)
LANGUAGE plpgsql
   AS $$
BEGIN
   INSERT INTO mp_TAB22  VALUES(2,2);
   COMMIT;
END; $$;

CREATE PROCEDURE my_procedure_parent(INOUT p_sql_message VARCHAR(20) DEFAULT NULL)
LANGUAGE plpgsql
   AS $$
   DECLARE 
   ret  VARCHAR(20);
BEGIN
   BEGIN
   INSERT INTO tab1(col1, col2) VALUES(2,2);
   EXCEPTION
   WHEN OTHERS THEN
      p_sql_message := sqlstate;
      RETURN;
END;
BEGIN
   CALL my_procedure_child(ret);
EXCEPTION
   WHEN OTHERS THEN
      p_sql_message := sqlstate;
   RETURN;
END;
    if ret is null then
      p_sql_message := 'mp_sp02 success!';
else
      p_sql_message := 'mp_sp02 failed!';
   end if;

END; $$;

But as a result of calling my_procedure_parent() in PostgreSQL, the following message appears: ‘2D000’ (invalid_transaction_termination). Due to the manual verification it becomes clear that the reason is in the COMMIT command. When calling the mp_sp02_test() procedure, it cannot be called in the EXCEPTION block. Therefore, the manual conversion option in PostgreSQL will look like this (CALL is placed outside the BEGIN/END block with EXCEPTION):

CREATE PROCEDURE mp_sp02_test(INOUT p_sql_message VARCHAR(20) DEFAULT NULL)
LANGUAGE plpgsql
   AS $$
BEGIN
   INSERT INTO mp_TAB22  VALUES(2,2);
   COMMIT;
END; $$;

CREATE PROCEDURE my_procedure_parent(INOUT p_sql_message VARCHAR(20) DEFAULT NULL)
LANGUAGE plpgsql
   AS $$
   DECLARE 
   ret  VARCHAR(20);
BEGIN
   BEGIN
   INSERT INTO tab1(col1, col2) VALUES(2,2);
   EXCEPTION
   WHEN OTHERS THEN
      p_sql_message := sqlstate;
      RETURN;
END;

   CALL my_procedure_child(ret);

    if ret is null then
      p_sql_message := 'mp_sp02 success!';
else
      p_sql_message := 'mp_sp02 failed!';
   end if;

END; $$;

An important point to keep in mind in DB2 to PostgreSQL migration is that when there are cases of using handlers in procedures together with calls to other procedures with transactions, it is risky to rely only on automatic conversion from DB2 to PostgreSQL. It is better to check each case of their use manually.

At the moment, Ispirer Toolkit is able to recognize and convert all cases of using handlers, as we are constantly adding and developing the tool. The level of automation in DB2 to PostgreSQL direction with our toolkit is up to 90%, which helps reduce manual conversion efforts and the overall migration project timeframe.

Conclusion

In summary, the conversion of handlers and transactions from DB2 to PostgreSQL is a significant element of database migration, requiring particular attention and expertise. We provided clear explanations of the transactions and best ways to convert them. By understanding the intricate dynamics of DB2 to PostgreSQL migration and adhering to best practices, developers can navigate this migration more efficiently and effectively.

The ultimate decision rests with each individual - to migrate manually or automate the process to save money, time and effort. Ispirer Toolkit allows to accomplish a migration 2-3 times faster than converting DB2 to PostgreSQL manually. Moreover, automated database migration can result in significant cost savings. By reducing manual effort, automating repetitive tasks, and minimizing errors, you can minimize the overall migration expenses.

Explore the free 30-day demo version of our tool in order to evaluate the effectiveness of our solution and make the right choice.

DB2 to PostgreSQL: migration of transactions. Part 3

Conclusion

Subscribe to my newsletter

Alexander Kirpichny

Alexander Kirpichny