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.

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

Migration Reference

Language Elements

Converting SQL language elements from DB2 to SQL Server:

DB2 SQL Server
1 string1 CONCAT string 2 CONCAT … String concatenation operator string1 + string 2 + …
2 datetime +/- expr interval_unit Datetime interval expressions DATEADD function

Data Types

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

Built-in SQL Functions

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:

DB2 SQL Server
1 INT(string) Convert to INT CAST(string AS INT)
2 SMALLINT(expr) Convert to SMALLINT CAST(expr AS SMALLINT)

Converting system functions:

DB2 SQL Server
1 USER Get the current user SYSTEM_USER

Converting NULL handling functions:

DB2 SQL Server
1 VALUE(exp, exp2, …) Return first non-NULL expression COALESCE(exp, exp2, …)

Converting XML functions:

DB2 SQL Server
1 XMLELEMENT(NAME name, value) Create a XML element SELECT ... FOR XML PATH (name)
2 XMLFOREST(value, value2, ...) Create multiple XML elements SELECT ... FOR XML PATH

SELECT statement

Converting SQL SELECT statement from DB2 to SQL Server:

DB2 SQL Server
1 SYSIBM.SYSDUMMY1 table A single row, single column dummy table FROM clause removed
2 FOR READ ONLY Retrieve rows for read only Removed
3 WITH UR Uncommitted read WITH (NOLOCK)

CREATE DATABASE Statement

Converting CREATE DATABASE statement:

DB2 for z/OS SQL Server
1 CREATE DATABASE database_name CREATE DATABASE database_name
2 BUFFERPOOL name The default bufferpool name Removed
3 INDEXBP name The default bufferpool for indexes Removed
4 STOGROUP name Storage group Removed
5 CCSID ASCII | EBCDIC | UNICODE Data encoding Removed

CREATE TABLE Statement

Converting CREATE TABLE statement:

DB2 SQL Server
1 GENERATED ALWAYS | BY DEFAULT AS IDENTITY Identity column IDENTITY
2 FOR BIT DATA Binary data encoding Removed
3 FOR SBCS | MIXED DATA Column data encoding (DB2 for z/OS) Removed
4 IN tablespace Tablespace name Removed
5 CCSID ASCII | UNICODE | EBCDIC Character set clause Removed
6 DATA CAPTURE NONE | CHANGES Change data capture Removed
7 AUDIT NONE | CHANGES | ALL Audit type (DB2 for z/OS) Removed
8 WITH RESTRICT ON DROP Drop restriction Removed
9 [NOT] VOLATILE Table size variation (DB2 for z/OS) Removed
10 APPEND NO | YES Append rows on insert or load (DB2 for z/OS) Removed

Implicit DEFAULT values in DB2:

DB2 SQL Server
1 column CHAR(n) WITH DEFAULT column CHAR(n) DEFAULT ''
2 column VARCHAR(n) WITH DEFAULT column VARCHAR2(n) DEFAULT ''
3 column INTEGER WITH DEFAULT column NUMBER(10) DEFAULT 0
4 column DECIMAL(p, s) WITH DEFAULT column NUMBER(p, s) DEFAULT 0
5 column NUMERIC(p, s) WITH DEFAULT column NUMBER(p, s) DEFAULT 0
6 column DATE WITH DEFAULT column DATE DEFAULT GETDATE()
7 column TIMESTAMP WITH DEFAULT column TIMESTAMP DEFAULT GETDATE()

CREATE INDEX Statement

Converting CREATE INDEX statement:

DB2 SQL Server
1 PCTFREE num Free space to leave in an index page Removed
2 COMPRESS YES | NO Index compression enabled or not Removed

Additional DB2 for z/OS clauses:

DB2 for z/OS SQL Server
1 FREEPAGE num Leave a free page per num pages Removed
2 BUFFERPOOL name Bufferpool for index Removed
3 GBPCACHE CHANGED | ALL | NONE Pages written to global buffer pool Removed
4 NOT CLUSTER Not the clustering index Removed
5 CLOSE NO | YES Data set eligible for closing or not Removed
6 COPY YES | NO COPY utility is allowed for index or not Removed
7 PIECESIZE size Maximum addressability of data set Removed
8 [NOT] PADDED Pad variable-length columns Removed

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

DB2 for z/OS SQL Server
1 PRIQTY num Removed
2 SECQTY num Removed
3 ERASE NO | YES Removed

CREATE PROCEDURE Statement

Converting stored procedures from IBM DB2 to SQL Server:

DB2 SQL Server
1 CREATE OR REPLACE PROCEDURE name CREATE OR ALTER PROCEDURE name
2 name() When without parameters name
3 IN | OUT | INOUT param datatype(length) param datatype OUT
4 [DYNAMIC] RESULT SETS num Removed as not required
5 LANGUAGE SQL Removed
6 [NOT] DETERMINISTIC Removed
7 MODIFIES SQL DATA Removed

For more information, see Conversion of Procedural SQL Statements.

CREATE FUNCTION Statement

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.

Procedural SQL Statements

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

DB2 SQL Server
1 DECLARE var datatype DEFAULT value Variable declaration DECLARE @var datatype = value
2 DECLARE var, var2, … datatype DECLARE @var datatype, @var2 datatype;
3 DECLARE cur CURSOR FOR sql Cursor declaration DECLARE cur CURSOR FOR sql
DECLARE cur CURSOR WITH RETURN
FOR select_stmt
Return a result set Declaration removed
select_stmt is moved to replace OPEN cur
4 SET var = value Assignment statement SET @var = value
5 SET (var, var2, …) = (value, value2, …) SET @var = value; SET @var2 = value2; …
6 SET (var, var2 …) = (SELECT c1, c2 FROM …) SELECT @var=c1, @var2=c2 FROM …

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

SQL Statements

Converting SQL statements:

DB2 SQL Server
1 COMMENT ON tab (col IS 'text', … ) Comment on column (DB2 for z/OS) EXECUTE sp_addextendedproperty
2 DECLARE GLOBAL TEMPORARY TABLE table Create a temporary table CREATE TABLE #table
3 VALUES expression Construct a result set SELECT expression