IBM DB2 to Microsoft SQL Server Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, SQL queries and SQL scripts from IBM DB2 to Microsoft SQL Server (MSSQL, MS SQL), Azure SQL and Azure Synapse.

• SQLines SQL Converter - SQL Conversion and Assessment tool
• SQLines Data - Data Transfer, Schema Migration and Validation tool

Databases

• IBM DB2 for LUW, OS/400 (iSeries) and z/OS (zSeries) 11.x, 10.x, 9.x, 8.x and 7.x
• Microsoft SQL Server 2022, 2019, 2017, 2016, 2014, 2012 and 2008

• SQL Language Elements
• Data Types
• Built-in SQL Functions
• SELECT Statement
• CREATE DATABASE
• CREATE TABLE
• CREATE INDEX
• CREATE PROCEDURE
• CREATE FUNCTION
• Procedural SQL Statements
• SQL Statements

Converting SQL language elements from DB2 to SQL Server:

Data type mapping from DB2 to SQL Server:

	DB2		SQL Server
1	BIGINT	64-bit integer	BIGINT
2	BLOB(n)	Binary large object, 1 ⇐ n ⇐ 2G	VARBINARY(max)
3	CHAR(n), CHARACTER(n)	Fixed-length string, 1 ⇐ n ⇐ 254	CHAR(n), CHARACTER(n)
4	CHAR(n) FOR BIT DATA	Fixed-length byte string, 1 ⇐ n ⇐ 254	BINARY(n)
5	CHARACTER VARYING(n)	Variable-length string, 1 ⇐ n ⇐ 32672	CHARACTER VARYING(n)
6	CLOB(n)	Character large object, 1 ⇐ n ⇐ 2G	VARCHAR(max)
7	DATE	Date (year, month and day)	DATE	Since SQL Server 2008
8	DBCLOB(n)	UTF-16 character large object, 1 ⇐ n ⇐ 1G	NVARCHAR(max)
9	DECIMAL(p,s), DEC(p,s)	Fixed-point number	DECIMAL(p,s), DEC(p,s)
10	DECFLOAT(16 | 34)	IEEE floating-point number	FLOAT
11	DOUBLE [PRECISION]	Double-precision floating-point number	FLOAT
12	FLOAT(p)	Double-precision floating-point number	FLOAT
13	GRAPHIC(n)	Fixed-length UTF-16 string, 1 ⇐ n ⇐ 127	NCHAR(n)
14	INTEGER, INT	32-bit integer	INTEGER, INT
15	LONG VARCHAR	Variable-length string, ⇐ 32700	VARCHAR(max)
16	NCHAR(n)	Fixed-length UTF-16 string, 1 ⇐ n ⇐ 127	NCHAR(n)
17	NCHAR VARYING(n)	Varying-length UTF-16 string, 1 ⇐ n ⇐ 16336	NCHAR VARYING(n)
18	NCLOB(n)	UTF-16 character large object, 1 ⇐ n ⇐ 1G	NVARCHAR(max)
19	NUMERIC(p,s), NUM(p,s)	Fixed-point number	NUMERIC(p,s)
20	NVARCHAR(n)	Varying-length UTF-16 string, 1 ⇐ n ⇐ 16336	NVARCHAR(n)
21	REAL	Single-precision floating-point number	REAL
22	SMALLINT	16-bit integer	SMALLINT
23	TIME	Time (hour, minute and second)	TIME(0)
24	TIMESTAMP(p)	Date and time with fraction	DATETIME2(p)
25	VARCHAR(n)	Variable-length string, 1 ⇐ n ⇐ 32672	VARCHAR(n)
26	VARCHAR(n) FOR BIT DATA	Variable-length byte string, 1 ⇐ n ⇐ 32672	VARBINARY(n)
27	VARGRAPHIC(n)	Variable-length UTF-16 string, 1 ⇐ n ⇐ 16336	NVARCHAR(n)
28	XML	XML data	XML

Converting string functions:

	DB2		SQL Server
1	CHAR(string, length)	Get substring	CAST(string AS CHAR(length))
2	CHAR(datetime, format)	Convert datetime to string	CONVERT(VARCHAR, datetime, style)
3	HEX(string)	Convert string to hex string	CONVERT(VARCHAR, CONVERT(VARBINARY, string, 2))
4	LOCATE(substring, str, start)	Get position of substring	CHARINDEX(substring, str, start)
5	SUBSTR(string, pos, len)	Get a substring of string	SUBSTRING(string, pos, len)
	SUBSTR(string, pos)		SUBSTRING(string, pos, LEN(string))
6	TRANSLATE(string, to, from)	Replace characters	TRANSLATE(string, from, to)

Converting date and time functions:

	DB2			SQL Server
1	CHAR(datetime, format)		Convert datetime to string	CONVERT(VARCHAR, datetime, style)
2	CURRENT DATE	CURRENT_DATE	Get the current date	CONVERT(DATE, GETDATE())
3	DAYOFWEEK_ISO(expr)		Get day of week, 1 is Monday, 7 is Sunday	SET DATEFIRST 1; DATEPART(dw, expr)

Converting numeric functions:

Converting system functions:

Converting NULL handling functions:

Converting XML functions:

Converting SQL SELECT statement from DB2 to SQL Server:

Converting CREATE DATABASE statement:

Converting CREATE TABLE statement:

Implicit DEFAULT values in DB2:

Converting CREATE INDEX statement:

Additional DB2 for z/OS clauses:

USING STOGROUP clause (DB2 for z/OS only):

Converting stored procedures from IBM DB2 to SQL Server:

For more information, see Conversion of Procedural SQL Statements.

Converting user-defined functions from DB2 to SQL Server:

	DB2		SQL Server
1	CREATE OR REPLACE FUNCTION		CREATE FUNCTION
2	IN | OUT | INOUT param datatype(length)		@param datatype(length)
3	RETURNS datatype(length)		RETURNS datatype(length)
4	DETERMINISTIC		Removed
5	NOT DETERMINISTIC		Removed
6	LANGUAGE SQL		Removed
7	CONTAINS SQL		Removed
8	NO SQL		Removed
9	READS SQL DATA		Removed
10	MODIFIES SQL DATA		Removed
11	SPECIFIC name		Removed
12	CALLED ON NULL INPUT		Removed
13	INHERIT SPECIAL REGISTERS		Removed
14	[NO] EXTERNAL ACTION		Removed
15	BEGIN ATOMIC		BEGIN
16	RETURN can be inside IF ELSE		RETURN as the last outer statement is required
17	No specific delimiter at the end		GO

For more information, see Conversion of Procedural SQL Statements.

Converting procedural SQL statements used in stored procedures, functions and triggers from IBM DB2 to SQL Server:

Flow-of-control statements:

1	CASE WHEN ... END CASE	CASE statement	IF ... ELSE
2	FOR var AS SELECT … DO sql END FOR	For each row loop	OPEN cursor and WHILE loop
3	IF THEN [ELSEIF] ELSE END IF	IF statement	IF BEGIN END [ELSE IF] ELSE
4	WHILE condition DO sql END WHILE	A loop statement	WHILE condition BEGIN sql END

Converting SQL statements: