Oracle to DB2 Converter: Syntax Guide & Examples

Why Convert Oracle to DB2?

IBM DB2 LUW (Linux, Unix, Windows) is a popular choice for enterprise workloads, particularly in industries like banking, insurance, and government where IBM infrastructure is already established. Organizations migrating from Oracle to DB2 often do so to consolidate on an existing IBM stack, reduce Oracle licensing costs, or leverage DB2's strengths in transaction processing and analytics.

DB2 offers strong SQL standard compliance, excellent compression (row, page, and adaptive compression), built-in workload management (WLM), and tight integration with IBM's ecosystem including z/OS mainframes, Watson, and Cloud Pak for Data. For organizations already invested in IBM, DB2 is a natural fit.

While Oracle and DB2 share many SQL concepts, there are important differences in built-in functions, data types, procedural language syntax, and row-limiting approaches. This guide covers the key conversions you need to handle when migrating Oracle SQL to DB2 LUW.

Key Syntax Differences

Feature	Oracle	DB2 LUW
NULL replacement	NVL(a, b)	COALESCE(a, b)
Current date/time	SYSDATE	CURRENT_TIMESTAMP / CURRENT DATE
Row limiting	WHERE ROWNUM <= 10	FETCH FIRST 10 ROWS ONLY
String concatenation	a || b	a || b or CONCAT(a, b)
Sequence next value	seq_name.NEXTVAL	NEXT VALUE FOR seq_name
Dual table	SELECT 1 FROM DUAL	VALUES 1 or SELECT 1 FROM SYSIBM.SYSDUMMY1
Decode function	DECODE(col, v1, r1, def)	CASE WHEN col = v1 THEN r1 ELSE def END
Date arithmetic	SYSDATE + 30 (days)	CURRENT DATE + 30 DAYS
Outer join syntax	(+) operator	Standard LEFT JOIN / RIGHT JOIN
Procedural language	PL/SQL	SQL PL

Common Conversions with Examples

NVL to COALESCE

-- Oracle
SELECT NVL(nickname, name) AS display_name FROM users;
SELECT NVL2(commission, salary + commission, salary) AS total FROM employees;

-- DB2
SELECT COALESCE(nickname, name) AS display_name FROM users;
SELECT CASE WHEN commission IS NOT NULL THEN salary + commission ELSE salary END AS total FROM employees;

SYSDATE to CURRENT_TIMESTAMP

-- Oracle
SELECT SYSDATE FROM DUAL;
SELECT * FROM orders WHERE order_date > SYSDATE - 30;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

-- DB2
SELECT CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY1;
SELECT * FROM orders WHERE order_date > CURRENT DATE - 30 DAYS;
SELECT VARCHAR_FORMAT(CURRENT_TIMESTAMP, 'YYYY-MM-DD') FROM SYSIBM.SYSDUMMY1;

ROWNUM to FETCH FIRST

-- Oracle
SELECT * FROM employees WHERE ROWNUM <= 10;

-- Oracle (12c+ standard)
SELECT * FROM employees
FETCH FIRST 10 ROWS ONLY;

-- DB2
SELECT * FROM employees
FETCH FIRST 10 ROWS ONLY;

-- DB2 with offset (pagination)
SELECT * FROM employees
ORDER BY id
OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

DECODE to CASE

-- Oracle
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM users;

-- DB2
SELECT CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    ELSE 'Unknown'
END FROM users;

FROM DUAL Removal

-- Oracle
SELECT SYSDATE FROM DUAL;
SELECT 1 + 1 FROM DUAL;
SELECT user_seq.NEXTVAL FROM DUAL;

-- DB2
SELECT CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY1;
VALUES (1 + 1);
SELECT NEXT VALUE FOR user_seq FROM SYSIBM.SYSDUMMY1;

Sequences

-- Oracle
CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;
INSERT INTO users (id, name) VALUES (user_seq.NEXTVAL, 'Alice');
SELECT user_seq.CURRVAL FROM DUAL;

-- DB2
CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;
INSERT INTO users (id, name) VALUES (NEXT VALUE FOR user_seq, 'Alice');
SELECT PREVIOUS VALUE FOR user_seq FROM SYSIBM.SYSDUMMY1;

Or use GENERATED ALWAYS AS IDENTITY to avoid managing sequences manually:

-- DB2 (preferred for auto-increment)
CREATE TABLE users (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);

CONNECT BY to Recursive CTE

-- Oracle (hierarchical query)
SELECT employee_id, manager_id, name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- DB2 (recursive CTE)
WITH emp_tree (employee_id, manager_id, name, lvl) AS (
    SELECT employee_id, manager_id, name, 1
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name, t.lvl + 1
    FROM employees e
    JOIN emp_tree t ON e.manager_id = t.employee_id
)
SELECT * FROM emp_tree;

Date Formatting

-- Oracle
SELECT TO_DATE('2026-03-11', 'YYYY-MM-DD') FROM DUAL;
SELECT TO_CHAR(created_at, 'DD-MON-YYYY') FROM orders;

-- DB2
SELECT DATE('2026-03-11') FROM SYSIBM.SYSDUMMY1;
SELECT VARCHAR_FORMAT(created_at, 'DD-Mon-YYYY') FROM orders;

DB2 uses VARCHAR_FORMAT instead of Oracle's TO_CHAR for date-to-string conversion, and DATE(), TIMESTAMP() casting functions instead of TO_DATE.

Data Type Mapping

Oracle Type	DB2 Type	Notes
NUMBER(10)	INTEGER or BIGINT	Based on precision (1-9: INT, 10-18: BIGINT)
NUMBER(10,2)	DECIMAL(10,2)	Same precision/scale
NUMBER (no precision)	DECFLOAT or DECIMAL(31)	Arbitrary precision
VARCHAR2(n)	VARCHAR(n)	Drop the "2"
CHAR(n)	CHAR(n)	Same
CLOB	CLOB	Same (up to 2 GB in DB2)
BLOB	BLOB	Same (up to 2 GB in DB2)
DATE	TIMESTAMP	Oracle DATE includes time; DB2 DATE does not
TIMESTAMP	TIMESTAMP	Same
RAW(n)	VARCHAR(n) FOR BIT DATA	Variable-length binary
BINARY_FLOAT	REAL	4-byte float
BINARY_DOUBLE	DOUBLE	8-byte float
XMLTYPE	XML	DB2 has native XML (pureXML)

PL/SQL to SQL PL Conversion

DB2's procedural language is SQL PL, which differs from Oracle's PL/SQL in several important ways:

-- Oracle PL/SQL
CREATE OR REPLACE PROCEDURE update_salary(
    p_emp_id IN NUMBER,
    p_raise IN NUMBER
) AS
    v_current NUMBER;
BEGIN
    SELECT salary INTO v_current FROM employees WHERE employee_id = p_emp_id;
    UPDATE employees SET salary = v_current + p_raise WHERE employee_id = p_emp_id;
    DBMS_OUTPUT.PUT_LINE('Updated salary for ' || p_emp_id);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
END;

-- DB2 SQL PL
CREATE OR REPLACE PROCEDURE update_salary(
    IN p_emp_id INTEGER,
    IN p_raise DECIMAL(10,2)
)
LANGUAGE SQL
BEGIN
    DECLARE v_current DECIMAL(10,2);
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'Employee not found';

    SELECT salary INTO v_current FROM employees WHERE employee_id = p_emp_id;
    UPDATE employees SET salary = v_current + p_raise WHERE employee_id = p_emp_id;
END;

Key differences: DB2 uses IN/OUT/INOUT before parameter names (not after), DECLARE HANDLER replaces EXCEPTION blocks, and SIGNAL SQLSTATE replaces RAISE_APPLICATION_ERROR.

Step-by-Step Migration Guide

1. Use IBM Database Conversion Workbench (DCW). IBM provides the Database Conversion Workbench plugin for IBM Data Studio, which automates schema and SQL conversion from Oracle to DB2, including PL/SQL to SQL PL translation.
2. Enable Oracle compatibility mode. DB2 LUW supports an Oracle compatibility feature (DB2_COMPATIBILITY_VECTOR=ORA) that allows many Oracle SQL constructs to work natively in DB2, including NVL, DECODE, SYSDATE, ROWNUM, and PL/SQL syntax. This can simplify migration significantly.
3. Convert the schema. Replace VARCHAR2 with VARCHAR, NUMBER with appropriate DB2 numeric types (INTEGER, DECIMAL, BIGINT), and Oracle DATE (which includes time) with TIMESTAMP.
4. Handle Oracle-specific SQL. Replace NVL with COALESCE, DECODE with CASE, SYSDATE with CURRENT_TIMESTAMP, ROWNUM with FETCH FIRST n ROWS ONLY, (+) outer joins with standard LEFT/RIGHT JOIN, FROM DUAL with FROM SYSIBM.SYSDUMMY1, and seq.NEXTVAL with NEXT VALUE FOR seq.
5. Convert PL/SQL to SQL PL. Change parameter direction keywords from after to before the name, replace EXCEPTION blocks with DECLARE HANDLER, convert Oracle packages to DB2 modules, and update cursor syntax.
6. Convert hierarchical queries. Replace CONNECT BY ... START WITH with recursive CTEs using WITH ... UNION ALL. DB2 has excellent recursive CTE support.
7. Test with production-like data. Load a full copy of your Oracle data into DB2 and run regression tests. Pay attention to date arithmetic, NULL handling, empty string behavior, and transaction isolation differences.

For SQL fundamentals, see our What is SQL guide. For join syntax that works across databases, check SQL Joins Explained.

Convert Automatically with AI2SQL

Paste your Oracle query into the SQL Converter tool and get correct DB2 syntax with COALESCE, CURRENT_TIMESTAMP, FETCH FIRST, and proper data types. No need to remember the Oracle-to-DB2 differences.

Try the converter free. See also: Oracle to PostgreSQL and Oracle to SQL Server. Browse all converter tools.

Frequently Asked Questions

What is the DB2 equivalent of Oracle's NVL?

Use COALESCE in DB2. Oracle: NVL(column, 'default'). DB2: COALESCE(column, 'default'). COALESCE is SQL standard and accepts multiple arguments, returning the first non-NULL value. Alternatively, if you enable DB2's Oracle compatibility mode (DB2_COMPATIBILITY_VECTOR=ORA), NVL will work directly in DB2.

How do I convert Oracle ROWNUM to DB2?

Replace ROWNUM with FETCH FIRST n ROWS ONLY. Oracle: SELECT * FROM employees WHERE ROWNUM <= 10. DB2: SELECT * FROM employees FETCH FIRST 10 ROWS ONLY. For more complex row numbering, use the ROW_NUMBER() window function which both databases support.

Can I migrate Oracle PL/SQL procedures to DB2 SQL PL?

Yes, but it requires rewriting. DB2 uses SQL PL which shares concepts with PL/SQL but has different syntax. Key changes: Oracle packages become DB2 modules, EXCEPTION blocks become DECLARE HANDLER, RAISE_APPLICATION_ERROR becomes SIGNAL SQLSTATE, and parameter direction keywords go before the parameter name. IBM's Database Conversion Workbench can automate much of the conversion, and DB2's Oracle compatibility mode can run some PL/SQL natively.