SQL Server to DB2 Converter: Syntax Guide & Examples

Why Convert SQL Server to DB2?

Organizations migrate from SQL Server to IBM DB2 LUW (Linux, UNIX, and Windows) for enterprise-grade workload handling, mainframe integration, or to consolidate onto a single IBM data platform. DB2 excels at mixed OLTP/OLAP workloads, offers deep integration with IBM middleware and z/OS, and provides advanced features like row and column access control, temporal tables, and pureScale clustering.

DB2's licensing model can be more cost-effective for large-scale enterprise deployments, especially when bundled with other IBM software. DB2 also supports in-database analytics through IBM Watson integrations and has strong compliance features required by financial institutions and government agencies.

The main challenges are converting T-SQL-specific syntax like TOP, IDENTITY, ISNULL, GETDATE(), temp tables, and stored procedures from T-SQL to SQL PL. This guide covers every difference.

Key Syntax Differences

Feature	SQL Server	DB2
Row limiting	SELECT TOP 10	FETCH FIRST 10 ROWS ONLY
Pagination	OFFSET ... FETCH NEXT	OFFSET ... FETCH NEXT
Auto-increment	IDENTITY(1,1)	GENERATED ALWAYS AS IDENTITY
Identifier quoting	[brackets]	"double quotes"
String concatenation	a + b	a || b
NULL replacement	ISNULL(a, b)	COALESCE(a, b)
Current datetime	GETDATE()	CURRENT_TIMESTAMP
Row count	@@ROWCOUNT	ROW_COUNT()
String aggregation	STRING_AGG(col, ',')	LISTAGG(col, ',')
Temp tables	#table_name	DECLARE GLOBAL TEMPORARY TABLE

Common Conversions with Examples

TOP to FETCH FIRST

-- SQL Server
SELECT TOP 10 * FROM products ORDER BY price DESC;

SELECT * FROM products
ORDER BY price DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- DB2
SELECT * FROM products ORDER BY price DESC
FETCH FIRST 10 ROWS ONLY;

SELECT * FROM products
ORDER BY price DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

DB2 supports the SQL standard OFFSET ... FETCH NEXT syntax, which is the same as SQL Server 2012+. The key difference is replacing SELECT TOP N with FETCH FIRST N ROWS ONLY at the end of the query.

IDENTITY to GENERATED ALWAYS AS IDENTITY

-- SQL Server
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL
);

-- DB2
CREATE TABLE users (
    id INT NOT NULL GENERATED ALWAYS AS IDENTITY
        (START WITH 1, INCREMENT BY 1) PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

DB2 offers two options: GENERATED ALWAYS (DB2 always generates the value, you cannot insert explicit values) and GENERATED BY DEFAULT (DB2 generates a value only if you do not supply one). Use GENERATED BY DEFAULT when migrating data with existing ID values.

Date Function Differences

-- SQL Server
SELECT GETDATE();
SELECT DATEADD(DAY, 30, created_at) FROM orders;
SELECT DATEDIFF(DAY, start_date, end_date) FROM projects;
SELECT CONVERT(VARCHAR, created_at, 120) FROM orders;

-- DB2
SELECT CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY1;
SELECT created_at + 30 DAYS FROM orders;
SELECT DAYS(end_date) - DAYS(start_date) FROM projects;
SELECT VARCHAR_FORMAT(created_at, 'YYYY-MM-DD HH24:MI:SS') FROM orders;

DB2 uses CURRENT_TIMESTAMP (no parentheses) as a special register. Date arithmetic in DB2 uses labeled durations like + 30 DAYS, + 3 MONTHS, or + 1 YEAR instead of DATEADD.

ISNULL to COALESCE

-- SQL Server
SELECT ISNULL(nickname, name) AS display_name FROM users;
SELECT IIF(age >= 18, 'Adult', 'Minor') AS category FROM users;

-- DB2
SELECT COALESCE(nickname, name) AS display_name FROM users;
SELECT CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END AS category FROM users;

COALESCE is ANSI-standard and works in both SQL Server and DB2. DB2 does not have IIF, so use the standard CASE WHEN expression instead.

String Concatenation

-- SQL Server (+ operator)
SELECT first_name + ' ' + last_name AS full_name FROM users;

-- DB2 (|| operator)
SELECT first_name || ' ' || last_name AS full_name FROM users;

DB2 uses the || concatenation operator, which is the ANSI SQL standard. Unlike SQL Server's +, the || operator in DB2 will return NULL if any operand is NULL. Use COALESCE to handle NULLs: COALESCE(first_name, '') || ' ' || COALESCE(last_name, '').

STRING_AGG to LISTAGG

-- SQL Server 2017+
SELECT department, STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name)
FROM employees
GROUP BY department;

-- DB2
SELECT department, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name)
FROM employees
GROUP BY department;

DB2's LISTAGG function is very similar to SQL Server's STRING_AGG and supports the same WITHIN GROUP (ORDER BY ...) clause.

CONVERT/CAST Differences

-- SQL Server
SELECT CONVERT(VARCHAR(10), price) FROM products;
SELECT CAST(quantity AS DECIMAL(10,2)) FROM orders;
SELECT CONVERT(VARCHAR, order_date, 103) FROM orders; -- DD/MM/YYYY

-- DB2
SELECT CHAR(price) FROM products;
SELECT CAST(quantity AS DECIMAL(10,2)) FROM orders;
SELECT VARCHAR_FORMAT(order_date, 'DD/MM/YYYY') FROM orders;

DB2 supports CAST (ANSI standard) but does not support SQL Server's CONVERT with style codes. Use VARCHAR_FORMAT for date formatting and CHAR, VARCHAR, INTEGER, or DECIMAL scalar functions for type conversion.

Temporary Tables

-- SQL Server
CREATE TABLE #temp_results (
    id INT,
    total DECIMAL(10,2)
);
INSERT INTO #temp_results SELECT id, SUM(amount) FROM orders GROUP BY id;

-- DB2
DECLARE GLOBAL TEMPORARY TABLE temp_results (
    id INT,
    total DECIMAL(10,2)
) ON COMMIT PRESERVE ROWS NOT LOGGED;
INSERT INTO SESSION.temp_results SELECT id, SUM(amount) FROM orders GROUP BY id;

DB2 uses DECLARE GLOBAL TEMPORARY TABLE instead of the # prefix. Temporary tables live in the SESSION schema and require the SESSION. prefix when referenced. The ON COMMIT PRESERVE ROWS clause keeps data after a commit (similar to SQL Server behavior). NOT LOGGED improves performance by skipping transaction logging.

@@ROWCOUNT to ROW_COUNT()

-- SQL Server (inside a procedure)
UPDATE orders SET status = 'shipped' WHERE shipped_date IS NOT NULL;
IF @@ROWCOUNT = 0
    PRINT 'No rows updated';

-- DB2 (inside SQL PL procedure)
UPDATE orders SET status = 'shipped' WHERE shipped_date IS NOT NULL;
GET DIAGNOSTICS v_count = ROW_COUNT;
IF v_count = 0 THEN
    -- handle no rows updated
END IF;

In DB2 SQL PL, use GET DIAGNOSTICS to retrieve ROW_COUNT into a variable after DML statements.

Data Type Mapping

SQL Server Type	DB2 Type	Notes
BIT	SMALLINT	Use CHECK (col IN (0,1)) constraint
INT	INTEGER	Same 4-byte integer
BIGINT	BIGINT	Same
FLOAT	DOUBLE	8-byte float
NVARCHAR(n)	VARCHAR(n)	Use UTF-8 database encoding
NVARCHAR(MAX)	CLOB	Up to 2GB
VARCHAR(MAX)	CLOB	Up to 2GB
VARBINARY(MAX)	BLOB	Binary data
DATETIME2	TIMESTAMP	Up to 12-digit fractional seconds
DATETIMEOFFSET	TIMESTAMP WITH TIME ZONE	DB2 11.1+
MONEY	DECIMAL(19,4)	No MONEY type in DB2
UNIQUEIDENTIFIER	CHAR(36)	Store UUID as string
XML	XML	DB2 has native XML (pureXML) support

Stored Procedure Conversion (T-SQL to SQL PL)

T-SQL (SQL Server)	SQL PL (DB2)
DECLARE @var INT	DECLARE var INT;
SET @var = 1	SET var = 1;
BEGIN TRY...END TRY BEGIN CATCH...END CATCH	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN TRANSACTION / COMMIT	COMMIT (auto-commit off by default)
PRINT 'message'	CALL DBMS_OUTPUT.PUT_LINE('message')
RAISERROR / THROW	SIGNAL SQLSTATE '...'

-- SQL Server procedure
CREATE PROCEDURE GetActiveUsers @MinAge INT
AS
BEGIN
    SELECT name, email FROM users
    WHERE active = 1 AND age >= @MinAge
    ORDER BY name;
END;

-- DB2 SQL PL procedure
CREATE PROCEDURE GetActiveUsers (IN p_min_age INT)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE c1 CURSOR WITH RETURN FOR
        SELECT name, email FROM users
        WHERE active = 1 AND age >= p_min_age
        ORDER BY name;
    OPEN c1;
END;

DB2 procedures that return result sets must declare a cursor with WITH RETURN and specify DYNAMIC RESULT SETS. This is different from SQL Server, which implicitly returns result sets from SELECT statements.

Step-by-Step Migration Guide

1. Create the DB2 database with UTF-8 encoding. Use CREATE DATABASE mydb USING CODESET UTF-8 TERRITORY US to ensure Unicode support equivalent to SQL Server's NVARCHAR.
2. Export the schema. Use SQL Server Management Studio to script out all database objects. Select all tables, views, stored procedures, and functions.
3. Convert data types. Replace NVARCHAR with VARCHAR, BIT with SMALLINT, DATETIME2 with TIMESTAMP, IDENTITY with GENERATED ALWAYS AS IDENTITY, and [brackets] with double quotes.
4. Export data. Use BCP to export as delimited files, then import with DB2's LOAD or IMPORT utility. For large datasets, LOAD is significantly faster than IMPORT.
5. Convert queries. Replace TOP with FETCH FIRST, ISNULL with COALESCE, GETDATE() with CURRENT_TIMESTAMP, DATEADD with labeled durations, and IIF with CASE WHEN.
6. Rewrite stored procedures. Convert T-SQL procedures to DB2 SQL PL syntax. Key changes: remove @ from variables, replace TRY...CATCH with DECLARE HANDLER, add DYNAMIC RESULT SETS and cursors for result-returning procedures.
7. Convert temp tables. Replace #table references with DECLARE GLOBAL TEMPORARY TABLE and add the SESSION. schema prefix to all references.
8. Test and validate. Run your application against DB2 and compare outputs. Pay attention to string padding behavior (CHAR vs VARCHAR), date arithmetic, NULL handling, and isolation level differences.

For SQL fundamentals, see our What is SQL guide. For join syntax used across databases, visit SQL Joins Explained.

Convert Automatically with AI2SQL

Stop translating T-SQL syntax manually. Paste your SQL Server query into the SQL Converter tool and get the correct DB2 query with FETCH FIRST, COALESCE, CURRENT_TIMESTAMP, and proper data types automatically.

Try the converter free. See also: SQL Server to MySQL. Browse all converter tools.

Frequently Asked Questions

How do I convert SQL Server TOP to DB2?

Replace SELECT TOP N with FETCH FIRST N ROWS ONLY at the end of the query. SQL Server: SELECT TOP 10 * FROM users ORDER BY id. DB2: SELECT * FROM users ORDER BY id FETCH FIRST 10 ROWS ONLY. For OFFSET-FETCH pagination, DB2 supports the same OFFSET N ROWS FETCH NEXT M ROWS ONLY syntax as SQL Server.

How do I convert SQL Server IDENTITY to DB2?

Replace IDENTITY(1,1) with GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1). SQL Server: id INT IDENTITY(1,1) PRIMARY KEY. DB2: id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY. Use GENERATED BY DEFAULT instead of GENERATED ALWAYS if you need to insert explicit ID values during data migration.

What happens to NVARCHAR columns when converting to DB2?

DB2 LUW uses VARCHAR for character data and supports Unicode natively when the database is created with UTF-8 encoding. Replace NVARCHAR(n) with VARCHAR(n) in a UTF-8 database. NVARCHAR(MAX) maps to CLOB in DB2. DB2 also has VARGRAPHIC for double-byte character data if needed.