DB2 to PostgreSQL migration is an uneasy journey, which contains a number of pitfalls. In the first part of the series of articles, we started to explore the topic of handlers migration and considered the ways to transform EXIT and CONTINUE handlers. If you haven't read the first part yet, then here's a link for you:

https://kirpichny.hashnode.dev/from-db2-to-postgresql-a-comprehensive-guide-on-handlers-conversion-part-1

As a solution to eliminate the hassles of manual migration, Ispirer developed SQLWays Wizard that automates and simplifies the process of converting DB2 to PostgreSQL database. Based on an intelligent migration core, this tool can analyze data types, relationships between objects, and consider reserved words. Moreover, code structures are converted even if there are no equivalents in a target database. As a result, migration to a new database is 2-3 times faster and more efficient.

In the second article, we will continue reviewing the issue of migrating handlers from DB2 to Postgres. We will discover the handlers of condition type: SQLWARNING exceptions, NOT FOUND, SQLEXCEPTION, specific exceptions, and effective solutions to migrate them from DB2 to PostgreSQL. So, let’s start discovering this topic.

Condition type

Based on the declaration, a handler in DB2 LUW can be set for various types of condition exceptions (both general SQLEXCEPTION types and specific ones, for example, SQLSTATE '42704'). Now we will discuss how it can work depending on the type of handler event (EXIT or CONTINUE).

According to the documentation, exceptions in DB2 database are divided into classes based on the value of the SQLSTATE variable:

• SQLWARNING (SQLSTATE values starting with 01)

• NOT FOUND (SQLSTATE values starting from 02)

• SQLEXCEPTION (any SQLSTATE values that do not start with 00 (executed correctly), or 01 (warning), or 02 (not found), which is not specifically handled elsewhere).

It is also possible to catch certain types of errors that return to SQLSTATE, for example,

• SQLSTATE '42704' - undefined object name or constraints,

• SQLSTATE '42710' - duplicate object name or constraint,

• SQLSTATE '02000' (or NOT FOUND) - no rows matching the condition were found, etc.

SQLWARNING exceptions

Let's start with FOR SQLWARNING (the SQLSTATE value starting from 01). In DB2 such a handler looks like this:

CREATE PROCEDURE my_procedure_warning()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLWARNING
  BEGIN
    -- handle the exception here
  END;  
    -- statement1;
    -- statement2;
    -- statementN;
END;

As such, there is no general SQLWARNING condition in PostgreSQL database, but there is a subgroup of codes that are classified as Warnings (presented here https://www.postgresql.org/docs/current/errcodes-appendix.html). There are noticeably fewer of them than in IBM DB2, but it is a feature of Postgres, and most of the codes are not defined here.

Therefore, in order to catch Warnings, we will take into account these features, and in the EXCEPTION block we have no choice but to list all the codes from this group.

CREATE OR REPLACE PROCEDURE my_procedure() 
AS $$
DECLARE 
BEGIN

BEGIN
    -- statement1;
EXCEPTION
  WHEN SQLSTATE '01000' OR SQLSTATE '0100C' OR SQLSTATE '01008' OR SQLSTATE '01003' OR SQLSTATE '01007' OR SQLSTATE '01006' OR SQLSTATE '01004' OR SQLSTATE '01P01' THEN
-- handle the exception here
END;

BEGIN
    -- statement2;
EXCEPTION
  WHEN SQLSTATE '01000' OR SQLSTATE '0100C' OR SQLSTATE '01008' OR SQLSTATE '01003' OR SQLSTATE '01007' OR SQLSTATE '01006' OR SQLSTATE '01004' OR SQLSTATE '01P01' THEN
-- handle the exception here
END;

BEGIN
 -- statementN;
EXCEPTION
  WHEN SQLSTATE '01000' OR SQLSTATE '0100C' OR SQLSTATE '01008' OR SQLSTATE '01003' OR SQLSTATE '01007' OR SQLSTATE '01006' OR SQLSTATE '01004' OR SQLSTATE '01P01' THEN
-- handle the exception here
END;
$$ LANGUAGE plpgsql;

We also remember that the handler in the DB2 example is specified as CONTINUE, and applying the rules described in the Conversion of CONTINUE handlers, we wrap each statement with a BEGIN/END block with an EXCEPTION. If the handler is EXIT, we add the RETURN command to interrupt the execution of the procedure (it makes sense to do it if there are more than 1 such blocks, and add RETURN to each except the last one if it is at the end of the procedure and no other commands follow it):

BEGIN
    -- statement1;
EXCEPTION
  WHEN SQLSTATE '01000' OR SQLSTATE '0100C' OR SQLSTATE '01008' OR SQLSTATE '01003' OR SQLSTATE '01007' OR SQLSTATE '01006' OR SQLSTATE '01004' OR SQLSTATE '01P01' THEN
-- handle the exception here
RETURN;
END;

NOT FOUND exceptions

The handler with the condition FOR NOT FOUND, in general, can be declared in DB2 as follows:

CREATE PROCEDURE my_procedure_warning()
BEGIN
  DECLARE CONTINUE HANDLER FOR NOT FOUND
  BEGIN
    -- handle the exception here
  END;  
    -- statement1;
    -- statement2;
    -- statementN;
END;

Exceptions resulting from SELECT INTO, INSERT, UPDATE, DELETE, or FETCH commands are handled, if the result is an empty table or zero rows. And this makes certain adjustments to the implementation of handlers in PostgreSQL.

Initially, it is essential to pay attention to the conversion of the SELECT INTO command from DB2 to PostgreSQL. The fact is that if more than 1 row in DB2 meets the search conditions, then the SQLSTATE=21000 error occurs. In the meantime, in PostgreSQL the first row of the resulting rowset will be inserted and the error will not occur.

Contents of the table abc:

Result:

SQL Error [21000]: The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.. SQLCODE=-811, SQLSTATE=21000

Query returned successfully

We can eliminate this difference by adding the STRICT option, which also allows the query to return only one row, thereby migrating SELECT INTO SELECT INTO STRICT from DB2 to PostgreSQL.

PostgreSQL

select c1 INTO STRICT a FROM abc WHERE c2<3;

Result:

ERROR:  query returned more than one row

In addition, the case when the search conditions match 0 lines is no less important. In IBM DB2 the result will be NULL, in PostgreSQL no insertion will occur.

DB2	PostgreSQL
select c1 INTO a FROM abc WHERE c2=0;	select c1 INTO STRICT a FROM abc WHERE c2=0;

Result (SELECT * FROM RESULT_TABLE):

C1	C1
[NULL]

To get a similar result of the SELECT INTO command in PostgreSQL, we decided to convert it adding a BEGIN/END block with EXCEPTION and process only the NO_DATA_FOUND (no rows) error inside. It is one of two possible SELECT INTO STRICT execution errors if the query did not return 1 row. The second error TOO_MANY_ROWS (more than one row) is not a matter of our interest, because if there are more than 1 row, the error in PostgreSQL will occur in the same way as in DB2, and there is no need to process it. This check will help us to get the same result in both DB2 and PostgreSQL databases in cases when it does not return anything.

PostgreSQL

BEGIN select c1 INTO STRICT a FROM abc WHERE C2<3; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END;

Result:

C1

[NULL]

Thus, if no strings matching the search conditions were found, this will trigger an exception, which will be ignored and the result will be NULL.

In the case of NOT FOUND handler conversion, its body will have to be moved to EXCEPTION WHEN NO_DATA_FOUND after the SELECT INTO command:

BEGIN
select c1 INTO STRICT a FROM abc WHERE c2<3;
EXCEPTION 
WHEN NO_DATA_FOUND THEN
 -- handle the exception here
END;

So, what should be added after INSERT, UPDATE, DELETE, FETCH? All commands, including SELECT INTO, change the values of a special boolean variable FOUND, which determines the execution status of the previous command. If at least 1 line was used during its execution, then the value true will be written to FOUND, otherwise false. And by checking its value, we can thereby duplicate the NOT FOUND handler logic in PostgreSQL. Let 's take an example:

CREATE PROCEDURE nf_hand(OUT p_sql_message varchar(100))
BEGIN
  DECLARE SQLSTATE char(5) DEFAULT '00000';
  DECLARE CONTINUE HANDLER FOR NOT FOUND
        BEGIN
         set p_sql_message = ‘no rows’;
        END;
  UPDATE abc SET c2 = 2 WHERE c1 =0;
END;

Result: 'no rows'

Here we update the rows of the abc table in which the value of column c1 is 0. But none of the rows matches this condition, which will cause a NOT FOUND exception, and this will result in the message ‘02000’ (no rows found).

In PostgreSQL, this will be implemented as follows:

CREATE OR replace PROCEDURE nf_hand(INOUT p_sql_message VARCHAR(100) DEFAULT NULL)
LANGUAGE plpgsql
   AS $$
BEGIN
   UPDATE abc SET c2 = 2 WHERE c1 =0;
    IF (NOT FOUND) then
          p_sql_message := ‘no rows’;
      END IF;    
END; $$;

Result: 'no rows'

As we can see, the results of both procedures are the same, which means that the DB2 procedure was correctly converted to PostgreSQL, and its logic is identical.

Similarly, we will add an IF (NOT FOUND) check after the INSERT, DELETE, FETCH statements with logic from the handler inside. Remember that if the handler is EXIT, then it is obligatory to add the RETURN command.

SQLEXCEPTION exception

Let's also analyze the conversion of the FOR SQLEXCEPTION handler from IBM DB2 to PostgreSQL database. This condition is triggered when SQLSTATE values do not start with 00, or 01, or 02. That is, any other exception that has not been specifically handled by another handler will be caught by the handler FOR SQLEXCEPTION. In PostgreSQL, the equivalent is clear, we have already mentioned in the examples above that it is EXCEPTION WHEN OTHERS.

Let's take an example of 2 handlers: CONTINUE HANDLER FOR NOT FOUND and EXIT HANDLER FOR SQLEXCEPTION in DB2:

CREATE PROCEDURE err_hadling(OUT p_sql_message varchar(100), OUT p_sql_message1 varchar(100) )
BEGIN
DECLARE l_count INTEGER;
DECLARE SQLSTATE char(5) DEFAULT '00000';
DECLARE CONTINUE HANDLER FOR NOT FOUND
        BEGIN
         set p_sql_message = 'no rows';
        END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
         set p_sql_message1 = SQLSTATE;
        END;
UPDATE abc SET c2 = 2 WHERE c1 =0;
SET l_count = 1 / 0;
INSERT INTO table1 VALUES(1,1) ;
END;

The result of the procedure will be OUT parameters with the values ‘no rows’ and ‘22012’ (division by 0), because there were no rows satisfying the condition c1=0 of the UPDATE command. As a result, the handler FOR NOT FOUND was triggered, the value of the parameter p_sql_message was set as ‘no rows’, after which the control was returned to the command following UPDATE. The SET command, in turn, led to a division by 0 error, and as a result it was processed by the handler FOR SQLEXCEPTION - p_sql_message1, the SQLSTATE value was assigned at that moment equal to 22012, and the procedure was exited (handler event type - EXIT), and the table1 was not inserted.

The same logic of work should be reproduced in PostgreSQL, therefore, taking into account the descriptions of conversions of certain handlers, we will perform the following transformation:

• The logic of processing CONTINUE HANDLER FOR NOT FOUND will be moved to the IF NOT FOUND condition after each INSERT, UPDATE, DELETE or FETCH command, or to EXCEPTION WHEN NO_DATA_FOUND after the SELECT INTO command (without RETURN);

• The logic of EXIT HANDLER FOR SQLEXCEPTION will be implemented in EXCEPTION WHEN OTHERS, which should be added after each command that may cause an error (except those with SQLSTATE starting with 00, 01, 02) + RETURN.

The conversion results will be the PostgreSQL procedure:

CREATE OR replace PROCEDURE err_hadling(INOUT p_sql_message VARCHAR(100) DEFAULT NULL, INOUT p_sql_message1 VARCHAR(100) DEFAULT NULL)
LANGUAGE plpgsql
   AS $$
   DECLARE
   l_count  INTEGER;
BEGIN
    BEGIN
       UPDATE abc SET c2 = 2 WHERE c1 =0;
       IF (NOT FOUND) then
         p_sql_message := ‘no rows’;
       END IF;    
       EXCEPTION
           WHEN OTHERS THEN
           p_sql_message1 := SQLSTATE;
           RETURN;
    END;
    BEGIN
           l_count := 1 / 0;
        EXCEPTION
           WHEN OTHERS THEN
           p_sql_message1 := SQLSTATE;
           RETURN;
      END;
    BEGIN
        INSERT INTO table1 VALUES(1,1) ;
        IF (NOT FOUND) then
          p_sql_message := ‘no rows’;
        END IF;
        EXCEPTION
           WHEN OTHERS THEN
           p_sql_message1 := SQLSTATE;
           RETURN;
    END;
END; $$;

When calling the procedure, 2 parameters with the values ‘no rows’ and ‘22012’ will also be returned. At the same time, each statement will be processed in the appropriate way in case of errors in PostgreSQL.

Specific exceptions

In addition to the general SQLWARNING, NOT FOUND, SQLEXCEPTION conditions, a handler for any SQLSTATE value can be defined in DB2 database, for example, to find errors of non-existent objects.

CREATE PROCEDURE not_exists()
BEGIN
  DECLARE EXIT HANDLER FOR SQLSTATE '42704'
  BEGIN
    -- handle the exception here
  END;  
    -- statement1;
    -- statement2;
    -- statementN;
END;

The equivalent handler in PostgreSQL database:

CREATE PROCEDURE not_exists()
LANGUAGE plpgsql
   AS $$
BEGIN
   -- statement1;
   -- statement2;
   -- statementN;
EXCEPTION 
WHEN SQLSTATE '42704' THEN
   -- handle the exception here
END; $$;

In general, such handlers will be converted from DB2 to PostgreSQL according to the schema.

HANDLER FOR SQLSTATE ‘[SPECIFIC CODE]’ => EXCEPTION WHEN SQLSTATE ‘[SPECIFIC CODE]’

It is only necessary to consider whether there is a corresponding code in PostgreSQL database, which can always be checked in the documentation https://www.postgresql.org/docs/current/errcodes-appendix.html.

Note that you can use different SQLSTATE values in the WHEN SQLSTATE block to handle different errors in PostgreSQL, but the WHEN OTHERS check should necessarily be at the end after checking for the remaining exceptions (if they are in the source), as in the example below:

BEGIN
   -- statement1;
   -- statement2;
   -- statementN;
EXCEPTION 
WHEN SQLSTATE '42704' THEN
   -- handle the exception here
WHEN SQLSTATE '42710' THEN
   -- handle the exception here
WHEN OTHERS THEN
   -- handle the exception here
END; $$;

For convenience, all the listed correspondences of handlers in IBM DB2 and PostgreSQL databases can be presented in the following table:

The process of migrating DB2 to Postgres is complicated by the absence of equivalents of the exception in PostgreSQL, because error codes often coincide. Thus, the main task at this point is to locate all handlers correctly so that the logic of the work is the same as in DB2. We always try to ensure that the results of the objects' work coincide in DB2 and PostgreSQL.

By the way, the Ispirer migration tool is able to convert all the mentioned types of handlers from DB2 to PostgreSQL automatically, which can significantly speed up database migration in general and help to avoid manual edits.

Conclusion

In this article, we have reviewed in detail the ways to migrate exceptions from DB2 to PostgreSQL. This guide will be useful for everyone, including those who plan database migration manually, as well as those who choose an automated approach.

We recommend not to waste time on manual migration, but to trust the automated Ispirer Toolkit solution. This tool will not only save you money, effort and time. The solution allows to eliminate the risk of the human factor, and therefore minimize the number of errors when migrating IBM DB2 to PostgreSQL. Try the free 30-day trial and evaluate the possibilities of automatic migration yourself.