From DB2 to PostgreSQL: a comprehensive guide on handlers conversion. Part 1

It is a well-known fact that database migration is a multi-step process with a great number of pitfalls. The process involves careful preparation, risk assessment, defining the migration method and implies significant financial costs. In order to ensure a smooth transition from DB2 to PostgreSQL, it is necessary to adhere to the following stages of migration:

1. Assessment. This stage involves evaluating the existing database, defining the number of lines of code, the size of the database, and other key parameters.

2. Planning. Development of a strategy taking into account all the features of the DB2 database, defining deadlines.

3. Schema migration. Database schema conversion from DB2 to PostgreSQL using tools for automated migration or manually.

4. Data migration. Transferring data from the DB2 database to PostgreSQL.

At Ispirer, we are convinced that it is necessary to automate the migration process to the maximum. To do this, we have created the SQLWays Wizard tool, which automates the database conversion process to PostgreSQL so that the migration to a new database is fast and efficient. SQLWays Wizard is based on an intelligent migration core that analyzes data types, relationships between objects, and reserved words and even code structures that do not have any equivalents in a PostgreSQL database. Such capabilities ensure high quality of database conversion and reduce the amount of manual code adjustments.

In addition, our experts are constantly utilizing machine learning techniques in Ispirer Toolkit. Adding new conversion rules to the solution allows to convert even the most intricate code. In this article, we are going to demonstrate the capabilities of Ispirer Toolkit and how we leverage them using the example of handlers migration from DB2 to PostgreSQL.

This article will be especially beneficial to all those who are planning to migrate DB2 to PostgreSQL, even if you are not going to use our tool. So, let's dive deep into the topic.

The SQL conversion feature

The conversion of SQL schema objects, such as stored procedures, functions, triggers, etc., is one of the most difficult stages of database migration. Actually, migration from DB2 to PostgreSQL is not an exception. At first sight, it may not be obvious, because all relational databases use the SQL language. However, each database has its own features that require changes to SQL code to make it work in another database. Some differences are quite easy to fix. For example, in order to convert DB2 to PostgreSQL, in most cases the LISTAGG() function should be replaced with STRING_AGG(), and the BITAND() function with the & operator. Identifying and correcting such cases is extremely important. It should be noted, though, that it can take a lot of time and effort.

Most often if the PostgreSQL database doesn’t have a direct equivalent, a search for a suitable solution may be required.

Recently, we have completed several projects on DB2 to PostgreSQL migration during which the most difficult one turned out to be the migration of IBM Db2 handlers. Next, we will elaborate on this topic and discover in detail what solutions we have chosen to transfer handlers to PostgreSQL.

Conversion of handlers

Handler of DB2 is a mechanism that makes it possible to handle errors appearing at the time of executing SQL-operators and perform particular actions. For example, writing info about the error to the log or sending notifications to an administrator.

The general form of a handler in IBM Db2 is:

DECLARE handler-type HANDLER FOR condition SQL-procedure-statement;

Here the handler-type defines what is going on after SQL-procedure-statement is completed. EXIT implies quit from the procedure, CONTINUE implies further execution of the command following the one that called the handler.

PostgreSQL doesn’t have exact equivalents to HANDLERS, however similar functionality is possible due to the combination of exception-handling instructions and control structures.

So, let’s examine the issue of handlers, starting with the description of their types, their classification, and consider all the conversion cases from DB2 to PostgreSQL with examples.

EXIT and CONTINUE handlers

1. Conversion of CONTINUE handlers

Example of CONTINUE handler in DB2 LUW:

CREATE PROCEDURE my_procedure()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    -- handle the exception here
  END;
  -- your code here
END;

The example above includes creating a procedure my_procedure() and declaring a CONTINUE handler for exceptions SQLEXCEPTION (one of the common conditions for checking along with SQLWARNING and NOT FOUND, that we will consider in the next article). In the BEGIN block we can write code for error handling, for example, writing an error message to the log or sending a notification.

If a DB2 exception of the SQLEXCEPTION type occurs during the execution of an SQL statement, control is transferred to the BEGIN CONTINUE type of the handler.

Such a handler in DB2 has to be converted to EXCEPTION WHEN OTHERS blocks in PostgreSQL:

CREATE OR REPLACE PROCEDURE my_procedure() 
AS $$
BEGIN
  -- your code here
EXCEPTION
  WHEN OTHERS THEN
    -- handle the error here
END;
$$ LANGUAGE plpgsql;

It is worth mentioning that the case is general, but as we delve into the principles of handlers operation in IBM Db2 to Postgres migration and the differences in error handling in PostgreSQL, it becomes easier to identify each specific case so that it is correctly interpreted in the target technology.

One important detail concerning DB2 to PostgreSQL migration we would like to draw your attention to is the following. When transferring control to the EXCEPTION block described in the BEGIN block with the code, control will not be returned to the statement following the one that triggered this handler. Also, the execution of the current BEGIN block will be interrupted (CONTINUE handler doesn’t imply exiting the procedure even if an error occurs).

To make it clear, let’s consider the following IBM Db2 example:

CREATE PROCEDURE my_procedure(OUT p_sql_message varchar(20)
)
BEGIN
  DECLARE l_count INTEGER;
     DECLARE SQLSTATE char(5) DEFAULT '00000';
  DECLARE CONTINUE 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;

The procedure in DB2 consists of several statements, and its logic will assume the following:

• First of all, the following command will be executed

INSERT INTO tab1(col1, col2) values (1,1);

• Second, when SET l_count = 1/0; is enabled the exception will be called, and the control will be transferred to the BEGIN block of the CONTINUE handler; the p_sql_message variable will be assigned the concatenation result 'SQLSTATE: '||SQLSTATE;

• Execution will continue and the insert command will be executed

INSERT INTO tab1(col1, col2) values (2,2);

As a result of execution, 2 rows are placed in the tab1 table, and the message 'SQLSTATE: 22012' will also be returned.

To get a similar result of the procedure, the conversion from DB2 to PostgreSQL should be performed taking into account the following conditions:

• After executing the EXCEPTION block commands, the execution of the procedure should be continued

• After exceptions are triggered, all execution results inside the BEGIN block with EXCEPTION are rolled back. The reason for this is that any error that occurs in the PL/pgSQL function interrupts the execution of the function and the transaction associated with it.

The solution provided by us takes into account both points, and according to it, each code statement should be wrapped in a BEGIN block with an EXCEPTION and logic from the CONTINUE HANDLER, so that no execution results to the error statements will not be canceled, and the procedure will be completed to the end.

The example in 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;
END;

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

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

The result in PostgreSQL also includes 2 extra rows in the tab1 table, the error of the assignment operation l_count := 1/0; was processed (the corresponding message text was assigned to the p_sql_message parameter), and all commands following the erroneous one were also executed.

Now, let’s consider the topic of EXIT handlers conversion from IBM DB2 to PostgreSQL in more detail.

2. Conversion of EXIT handlers

Here is an example of a DB2 LUW procedure with an EXIT handler, where we will add some of the details described above:

CREATE PROCEDURE my_procedure()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- handle the exception here
  END;  
    -- statement1;
    -- statement2;
    -- statementN;
END;

This procedure will be converted to PostgreSQL as follows:

CREATE OR REPLACE PROCEDURE my_procedure() 
AS $$  
BEGIN

BEGIN
    -- statement1;
EXCEPTION
  WHEN OTHERS THEN
   p_sql_message := 'SQLSTATE: '||SQLSTATE;
RETURN;
END;

BEGIN
    -- statement2;
EXCEPTION
  WHEN OTHERS THEN
   p_sql_message := 'SQLSTATE: '||SQLSTATE;
RETURN;
END;

BEGIN
 -- statementN;
EXCEPTION
  WHEN OTHERS THEN
   p_sql_message := 'SQLSTATE: '||SQLSTATE;
END;
$$ LANGUAGE plpgsql;

The conversion approach repeats the conversion of CONTINUE-handlers. However, in the PostgreSQL example above it is necessary to add the RETURN command in the EXCEPTION block. Its purpose directly corresponds to the handler event, when the EXIT handler is triggered and its commands are executed, the procedure should be exited. We can achieve this with the RETURN command. We also wrap each command in a BEGIN block with an EXCEPTION so that the execution results before the error statement are saved.

Conclusion

This article serves as a helpful guide for developers who need to convert handlers from DB2 to Postgres as part of a database migration process. We provided clear explanations of the procedures involved in the conversion process, including how to correctly convert handlers to PostgreSQL. Overall, this article provides practical advice and best practices that can help developers navigate the DB2 to PostgreSQL conversion process more efficiently.

The choice is always up to you - to migrate DB2 database manually or automate the process and simplify the transferring to PostgreSQL to save money, time and effort. Ispirer Toolkit allows to migrate 2-3 times faster than migrating from 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.

You can try the free 30-day trial version of our tool in order to evaluate the effectiveness of our solution and migrate DB2 database to PostgreSQL with ease.