DB2 to SQL Server migration: a closer look

Alexandr Kirpichny

Chief Product Officer, Ispirer Systems

Today, we're talking about moving from DB2 to SQL Server. It's a bit tricky, like navigating rough waters. We'll look at the challenges step by step to help you understand how to migrate DB2 to SQL Server seamlessly. Join us as we break down the process, giving you tips to make it easier and avoid problems.

Migration Steps

Let's first understand the main steps of migration before discussing the difficulties that usually come with it:

• Database assessment. The foundation of any migration endeavor lies in a thorough assessment of the existing database. This initial stage involves a comprehensive analysis of the current state, identifying key elements that will shape subsequent decisions.
• Database schema migration. Once armed with a clear understanding, the focus shifts to the migration of the database schema. This step demands precision, as the structure forms the backbone of the database and influences its performance in the new environment.
• Testing the converted schema. Sometimes schema conversion may result in some mistakes or discrepancies that may influence database functionality. Testing is aimed at identifying all of the troubles caused by conversion.
• Testing database performance. An often underestimated yet critical phase involves assessing the performance of the migrated database in its new setting. Thorough testing is conducted to guarantee optimal efficiency and reliability, addressing any performance bottlenecks that may arise.
• Data migration. The actual transfer of data from the old to the new system is a meticulous process. Attention to detail is paramount to prevent data inconsistencies, and a robust strategy ensures a successful migration without compromising data integrity.
• Cutover. The final and decisive moment arrives with the cutover phase. Careful planning is essential to minimize disruptions as the new system takes operational control. This marks the culmination of the migration process, and effective cutover strategies are key to a seamless transition.

Now let’s focus on the differences between DB2 and MSSQL that make migration a tough nut to crack.

Exception handlers

In DB2, in order to handle some kind of exception, you need to use handlers. It undoubtedly leads to certain events or erroneous states (SQLSTATE). For example, the cursor fetch ended, the query did not return a single row, accessing a non-existent table, etc.

DB2 documentation contains a complete list of error codes. Depending on the declaration, handlers can act on the entire procedure either inside a certain begin end block, trigger all exceptions in general, or only on certain erroneous states from the given list.

In MSSQL, the approach to this problem is completely different. Here, try-catch is used for error handling, as in many other programming languages. Accordingly, unlike DB2, it is impossible to catch only specific exceptions. For example, it is impossible to add a specific condition “try” in order to go to catch only if a division by zero error is received, and ignore all the others.

Moreover, it is not always possible to find an unambiguous correspondence between the error codes of DB2 and MSSQL.

For example, to determine that an error has occurred to reopen the cursor, you can check the following codes:

 
	SQLSTATE 24502: The cursor identified in an OPEN statement is already open
 

 
	@@error 16905: The cursor is already open
 

But for the error of casting a string to a number, you need to look at the context, because in DB2 it is always one code, and in MSSQL you need to know for sure which type it corresponds:

 
	SQLSTATE 22018: The value of a string argument was not acceptable to the function
 

 
	@@error 245: Conversion failed when converting the varchar value to data type int.
@@error 8114: Error converting data type varchar to numeric.
…
 

That is why the task of determining which error has occurred and its correct processing becomes not trivial at all and is not amenable to automation in all cases.Separately, it is worth noting that everything that is considered a warning in DB2 (the error code starts with “01”) is not considered an erroneous state in MSSQL at all and, accordingly, does not fall into catch.

But at the same time, there is an undoubted big disadvantage in the DB2 approach. It is never clear which instruction led to the error. And to bypass this point, it is necessary to create a special string variable, into which the stage of the procedure is written.

For example, in this case, the v_step variable is started, which is written to the error log table using the sp_write_error_log procedure:

 
	DECLARE v_err_mess VARCHAR(255) DEFAULT ' ';
DECLARE v_step        VARCHAR(255) DEFAULT ' ';
 
DECLARE EXIT HANDLER FOR SQLEXCEPTION
handle: BEGIN
   GET DIAGNOSTICS EXCEPTION 1 v_err_mess = MESSAGE_TEXT;
   CALL sp_write_error_log (v_err_mess, v_step);
END handle;
 
<some logic>
SET v_step= 'BEFORE CURSOR DECLARATION';
<some logic>
SET v_step= 'AFTER XMLSERIALIZE';
<some logic>
 

All this makes debugging the procedure in DB2 quite time-consuming for the developer, unlike MSSQL, where it is much easier to identify the location of the error and fix it. Therefore, developers are often happy to switch to another SQL dialect.

The problem with migrating constructions or exception handling methods is that it is difficult to reproduce the same logic in MSSQL as it was in DB2, and some behavior is completely impossible. It is necessary to analyze literally every instruction to determine what errors it can provoke, wrap it in a try-catch and add error handling described in the DB2 handlers that correspond to the necessary SQLSTATE.

The issue of converting exception handlers makes migration from DB2 to SQL Server a complicated process that requires a decent level of scrutiny and expertise in this field. Some skeptics may state that the whole process can be migrated manually. However, manual migration always implies a significant amount of time. Automated solutions obviously help to speed up the transition while addressing all the differences between source and target databases.

Transactions

DB2 does not control the use of transactions. They can be opened in one procedure and closed in another.

In the case of Microsoft SQL Server it does not work like this. In SQL Server, you can only start as many transactions as you finish or undo in a single procedure.

Then, when converting the code from DB2 to MS, it will be necessary to streamline the use of transactions in accordance with the requirements of SQL Server.

It can be quite time consuming. Sometimes the work can be simplified if you use the “save transaction” command instead of “commit”.

Functions

MSSQL provides limited capabilities in handling user defined functions. Therefore, sometimes converting even the simplest function can cause difficulties. But for some cases, you can find a workaround. For example, this is how you can bypass the ban on using try-catch in the function.

 
	CREATE FUNCTION fn_CAST(IN IN_STR VARCHAR(20) DEFAULT '0')
RETURNS INTEGER
LANGUAGE SQL
P1: BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION
      RETURN NULL;
   RETURN CAST(IN_STR AS INTEGER);
END P1
 
 

 
	CREATE FUNCTION fn_CAST(@IN_STR   VARCHAR(20)  = '0')
RETURNS INT
AS 
BEGIN
   RETURN TRY_CAST(@IN_STR AS INT)
END
 

In cases where this is not possible, functions should be replaced with procedures with output parameters. It is troublesome, because it requires changing all the calls. Accordingly, it leads to not only a change in the object type itself, but also a long-term debugging of business logic at the places where it is called.

The good news is that this transformation can be automated if you determine all the rules where functions should be replaced with procedures. Should you have a lot of similar cases in your code, we highly recommend simplifying your migration and referring to automated solutions like Ispirer Toolkit to make migration from DB2 a smooth step.

Global variables

DB2 has global variables that can be used in any object of the code. Since SQL Server doesn’t have global variables, it is recommended to create a table, where each global variable corresponds to a column with the identical data type as the variable. Undoubtedly, using such a table is much less convenient and more expensive than accessing a global variable.

 
CREATE VARIABLE GLVAR_1 INT DEFAULT 1;
CREATE VARIABLE GLVAR_2 CHAR(1) DEFAULT ‘Y’;
…
SET GLVAR_1 := 2;
…
IF GLVAR_2 <> 'N' THEN …
…
 

 
	CREATE TABLE GLOBAL_VARS (
  GLVAR_1 INT,
  GLVAR_2 CHAR(1));
INSERT INTO GLOBAL_VARS SELECT 1, 'Y';
…
UPDATE GLOBAL_VARS SET GLVAR_1 = 2;
…
IF (SELECT GLVAR_2 FROM GLOBAL_VARS) <> 'N' 
…
 

Actually, there are other ways to simulate global variables. The most curious readers will be able to find them and choose the best option on their own.

System tables

In nearly every DBMS, there's a specific method for storing metadata about itself. Verifying the presence of objects, retrieving a list of tables or their columns—these functionalities all need thorough scrutiny. If these tables are utilized in dynamic queries (a common scenario), migration errors may unexpectedly surface during the database's operation. Typically, tasks related to migrating system objects are challenging to automate and necessitate manual adjustments.

Procedure and function calls

There are significant differences in calling procedures and functions in DB2 and SQL Server. For example, to call a function from the previous example with a default parameter, you should use the word DEFAULT in MSSQL:

 
	SELECT FN_CAST FROM SYSIBM.SYSDUMMY1
 

 
	SELECT dbo.FN_CAST (DEFAULT)
 

The requirements for procedure calls are also different. For example, in MSSQL, you cannot call a procedure with a composite parameter (sp_proc 2+3). You need to create a separate variable, write the result to it and use it as a parameter. It is also mandatory to write “OUT” for each output parameter when calling, otherwise it will not be written to the variable.

These and many other differences do not make it impossible, but certainly complicate the transition from one database to another. Having large amounts of code and connections between objects, the best way out is to use software that will analyze the entire database and bring all calls into line.

Date format

If the date is stored in a text format, it may be difficult to convert it to the DATE type if the default format is set. Different DBMS use different storage formats, besides, it can be changed. Therefore, in such cases, setting the format becomes mandatory. Let's compare the conversion of a string to the default date:

 
	DATE('21.12.2023') -- 2023-12-21
DATE('12.21.2023') -- The string representation of a datetime value is out of range.
DATE('12/21/2023') -- 2023-12-21.
DATE('21/12/2023') -- The string representation of a datetime value is out of range.
 

 
	SET DATEFORMAT DMY
CONVERT(DATE, '21.12.2023') -- 2023-12-21
CONVERT(DATE, '12.21.2023') -- Conversion failed when converting date and/or time from character string.
CONVERT(DATE, '12/21/2023') -- Conversion failed when converting date and/or time from character string.
CONVERT(DATE, '21/12/2023') -- 2023-12-21
 

 
	DATE('21.12.2023') -- 2023-12-21
DATE('12.21.2023') -- The string representation of a datetime value is out of range.
DATE('12/21/2023') -- 2023-12-21.
DATE('21/12/2023') -- The string representation of a datetime value is out of range.
 

 
	SET DATEFORMAT MDY
CONVERT(DATE, '21.12.2023') -- Conversion failed when converting date and/or time from character string.
CONVERT(DATE, '12.21.2023') -- 2023-12-21
CONVERT(DATE, '12/21/2023') -- 2023-12-21
CONVERT(DATE, '21/12/2023') -- Conversion failed when converting date and/or time from character string.
 

As evident, there isn't a one-size-fits-all solution that allows for the conversion of a string to a date without explicitly specifying the format.

But if you set the style correctly in MSSQL when converting a string to a date (the third parameter), then the date will be received correctly:

 
	DATE('21.12.2023')
DATE('12/21/2023')
 

 
	CONVERT(DATE, '21.12.2023', 104) 
CONVERT(DATE, '12/21/2023', 101) 
 

What if the requirement is to extract a variable or a value from a table field and convert it to a date without prior knowledge of its format? DB2 provides several options for storing dates in text format. Consequently, you can create a supplementary function to find out the format in which the date is presented and perform the conversion accordingly.

 
		CREATE FUNCTION dbo.db2str_to_date (@InStr VARCHAR(10))
RETURNS DATE
AS 
BEGIN
   DECLARE @OutDate DATE = TRY_CONVERT(DATE, @InStr,23)  -- 23 = yyyy-mm-dd
   IF @OutDate IS NULL
   begin
      IF CHARINDEX('.', @InStr) <> 0
         SET @OutDate = TRY_CONVERT(DATE, @InStr,104)  -- 104 = dd.mm.yyyy
      ELSE IF @OutDate IS NULL
         SET @OutDate = TRY_CONVERT(DATE, @InStr,101)  -- 101 = mm/dd/yyyy
   end
   RETURN @OutDate
END
 

Take note of the differences in separators in the textual representation of time and timestamp. For instance,

DB2: “2000-01-10-23.59.57.123456”
MSSQL: “2000-01-10 23:59:57.123456”.

As a result, if the date is stored in a table or as a string variable, direct retrieval of the date becomes impractical. An additional conversion to an acceptable MS SQL format becomes necessary. The optimal approach is to create a supplementary function that examines the input string's format and modifies it if needed before converting it to a date. In this scenario, it is crucial to scrutinize all such casts and incorporate the use of this function.

Another headache is the difference in zero/starting values for dates in these dialects.

DB2: “0001-01-01”
MSSQL: “1900-01-01”.

As you can see, it is quite difficult to avoid confusion when converting dates. Therefore, you need to be very careful in each case of converting a string to a date or time, it will not always be possible to do this automatically, without manual adjustments and additional checks and instructions.

Regular expressions

Surprisingly, compared to other languages, in MSSQL everything is very bad with regulars. Therefore, if you need almost any meaningful use of regular expressions (regexp_substr, regexp_count, etc.), you either need to write and connect a dll(Dynamic Link Library) to the server, for example, in C#, or use OLE because it is not possible to do this only by means of a database.

Conclusion

In conclusion, the journey from DB2 to SQL Server demands careful consideration of potential obstacles. The significance of this transition underscores the need for a proficient team with extensive knowledge and experience.

Ispirer Toolkit is a valuable tool for converting databases. Besides, the Ispirer team provides complete migration services. From the initial database analysis to the ultimate database launch, our team is dedicated to handling every aspect of the migration process. For a seamless and efficient database migration, reach out to us, and let Ispirer guide you through this transformation with expertise and timeliness.