IBM DB2 to Microsoft SQL Server Migration

SQLines provides tools and services to help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries, embedded SQL statements and SQL scripts from IBM DB2 to Microsoft SQL Server.

We also help convert embedded SQL statements in C/C++ (ODBC, ESQL/C), C#, Java, PowerBuilder, VB/VB.NET, ASP/ASP.NET and Perl/PHP applications.

  • IBM DB2 for LUW and z/OS 10.x, 9.x, 8.x and 7.x
  • Microsoft SQL Server 2014, 2012, 2008 and 2005

DB2 to SQL Server Migration Tools

SQLines SQL Converter Tool

SQLines SQL Converter tool allows you to convert database schema (DDL), queries and DML statements, views, stored procedures, functions and triggers from IBM DB2 to Microsoft SQL Server.

SQLines tool converts SQL scripts and standalone SQL statements. To migrate data and database schema from an IBM DB2 database use SQLines Data tool.

Try SQLines Online or download a Trial Version.

DB2 to SQL Server Migration Reference

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 NCHAR(n) Fixed-length UTF-16 string, 1 ⇐ n ⇐ 127 NCHAR(n)
16 NCHAR VARYING(n) Varying-length UTF-16 string, 1 ⇐ n ⇐ 16336 NCHAR VARYING(n)
17 NCLOB(n) UTF-16 character large object, 1 ⇐ n ⇐ 1G NVARCHAR(max)
18 NUMERIC(p,s), NUM(p,s) Fixed-point number NUMERIC(p,s)
19 NVARCHAR(n) Varying-length UTF-16 string, 1 ⇐ n ⇐ 16336 NVARCHAR(n)
20 REAL Single-precision floating-point number REAL
21 SMALLINT 16-bit integer SMALLINT
22 TIME Time (hour, minute and second) TIME(0)
23 TIMESTAMP(p) Date and time with fraction DATETIME2(p)
24 VARCHAR(n) Variable-length string, 1 ⇐ n ⇐ 32672 VARCHAR(n)
25 VARCHAR(n) FOR BIT DATA Variable-length byte string, 1 ⇐ n ⇐ 32672 VARBINARY(n)
26 VARGRAPHIC(n) Variable-length UTF-16 string, 1 ⇐ n ⇐ 16336 NVARCHAR(n)
27 XML XML data XML

Built-in SQL Functions

Converting SQL functions:

DB2 SQL Server
1 CURRENT DATE CURRENT_DATE Get the current date CONVERT(DATE, GETDATE())
2 USER Get the current user SYSTEM_USER

Queries

Converting SQL SELECT statement:

DB2 SQL Server
SYSIBM.SYSDUMMY1 table A single row, single column dummy table FROM clause removed

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 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 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 FOR var AS SELECT … DO sql END FOR For each row loop OPEN cursor and WHILE loop
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 …
7 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