DB2 to SQL Server migration: a closer look

Alexander Kirpichny

Product Owner, 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 your data from DB2 to SQL Server seamlessly. Join us as we break down the process with tips to make it easier and avoid problems.

Migration Steps

Let's first understand the main steps of migration to give you an overview of the whole process:

• Database assessment. The foundation of any migration is a thorough assessment of the existing database. This initial stage involves a comprehensive current state analysis to identify key elements shaping subsequent decisions. With migration assistant tools like Assessment Wizard, you can create a data migration report to evaluate the workload and complexity required for the project.
• Database schema migration. Armed with a clear understanding, the focus shifts to the migration of the 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. Schema conversion may result in mistakes or discrepancies that may influence database functionality. Testing identifies 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 project settings. Thorough testing guarantees optimal efficiency and reliability and addresses 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. At the same time, a robust strategy ensures a successful migration without compromising data integrity.
• Cutover. The final and decisive moment is the cutover phase. Careful planning will minimize disruptions as the other system takes operational control. It’s 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 Server that make migration a tough nut to crack.

Exception handlers

In DB2, to handle some kind of exception, you need to use handlers. It can create certain events or erroneous states (SQLSTATE). For example, the cursor fetch ended, the SQL queries 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 Server, error handling works differently. It uses a try-catch approach, similar to many programming languages. However, unlike DB2, you can't catch specific exceptions. For example, you can't set a condition to only catch a "division by zero" error while ignoring other errors.

It’s not always possible to directly match DB2 error codes with MSSQL. For example, to detect an error requiring the cursor to be reopened, you might need to check multiple scripts:

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

 
	@@error 16905: The cursor is already open
 

For errors like casting a string to a number, context is crucial. In DB2, there’s always one script, but in MSSQL Server, you need to identify the specific type it corresponds to:

 
	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.
…
 

Determining the exact error and handling it correctly is often complex and not always automatable. Additionally, what DB2 treats as a warning (codes starting with "01") is not considered an error in MSSQL Server and therefore does not trigger a catch.

At the same time, there is a big disadvantage in the DB2 approach. It is never clear which instruction led to the error. To bypass this point, you need 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. In MSSQL Server, it is much easier to identify the location of the error and fix it. Therefore, developers are often happy to migrate to another SQL dialect.

Migrating exception-handling methods is challenging because replicating DB2's logic in MSSQL is difficult, and some behaviors cannot be reproduced at all. Each instruction must be analyzed to identify potential errors, wrapped in a try-catch, and paired with error handling based on the DB2 handlers for the relevant SQLSTATE.

Converting exception handlers makes migrating from DB2 to SQL Server complex. It requires careful analysis and expertise. While some may argue for manual migration, it is time-consuming. Automated solutions streamline the migration process and handle the differences between the 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 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.

When you convert code from DB2 to SQL Server, transactions must be adjusted to meet SQL Server's requirements. This process can take time, but using the "save transaction" command instead of "commit" can simplify the work.

Functions

MSSQL Server has limited support for user-defined functions. So, even simple conversions get challenging at times. However, workarounds are available for certain cases. For example, here's a way to bypass the restriction on using try-catch in a 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
 

When a workaround isn’t possible, functions may need to be replaced with procedures that use output parameters. This approach is more complex since it requires updating all the function calls. As a result, both the object type and the business logic at each call point need to be adjusted and carefully debugged.

The good news is that this transformation can be automated by identifying the rules for replacing functions with procedures. If you have many similar cases in your code, we recommend using automated software like the Ispirer Toolkit to make your DB2 migration easier.

Global variables

DB2 has global variables that can be used throughout the code, but SQL Server does not. It's recommended to create a table where each global variable has a corresponding column with the same data type. However, using such a table is less convenient and more costly than to access 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' 
…
 

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

System tables

Almost every DBMS has a specific way of storing metadata about itself. Checking for the presence of objects or retrieving lists of tables and columns requires careful attention. If these tables are used in dynamic SQL queries, migration errors may arise during the database's operation. Migrating system objects is usually hard to automate and often create the need for manual adjustments.

Procedure and function calls

There are significant differences in calling procedures and functions in DB2 database 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 Server:

 
	SELECT FN_CAST FROM SYSIBM.SYSDUMMY1
 

 
	SELECT dbo.FN_CAST (DEFAULT)
 

The requirements for procedure calls are also different. For example, in MSSQL Server, 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 dates are stored in a text format, it may be difficult to convert them 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 for the conversion of a string to a date without explicitly specifying the format.

But if you set the style correctly in MSSQL Server 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 you need to extract a value from a table field and convert it to a date without knowing its format? DB2 offers several options for storing dates in text format. You can create an additional function to determine the format and convert the date 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”.

If the dates are stored in a table or as string variables, directly retrieving them becomes impractical. You’ll need to convert the dates to an acceptable MS SQL Server format. The best approach is to create a function that checks the input string's format and modifies it before converting it to a date. It's important to review all such casts and use this function where necessary.

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

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

In all instances where these constants are compared, additional analysis is required to ensure accuracy. These constants are often used as default values for table fields. When maintaining the database, you must either keep this legacy and continue setting 1 year of our era or update the field with new values and adjust all comparisons in the code.

As you can see, converting dates can create much confusion. It's important to be very careful when converting a string to a date or time. It won’t always be possible to do this automatically without manual adjustments, extra checks, and instructions.

Regular expressions

Surprisingly, compared to other languages, in MSSQL Server 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 using a database.

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 that features extensive knowledge and experience.

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