Schema Migration -- IBM Db2 to Azure SQL¶

-- Db2: analyze DECFLOAT column precision
SELECT
    MAX(LENGTH(STRIP(CAST(decfloat_col AS VARCHAR(50)), TRAILING, '0')) -
        POSSTR(CAST(decfloat_col AS VARCHAR(50)), '.')) AS max_scale
FROM your_table
WHERE decfloat_col IS NOT NULL;

-- Db2
SELECT * FROM employees
ORDER BY hire_date DESC
FETCH FIRST 10 ROWS ONLY;

-- T-SQL (option 1: TOP)
SELECT TOP 10 * FROM employees
ORDER BY hire_date DESC;

-- T-SQL (option 2: OFFSET...FETCH, ANSI-compliant)
SELECT * FROM employees
ORDER BY hire_date DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

-- Db2
SELECT * FROM employees
ORDER BY emp_id
OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

-- T-SQL
SELECT * FROM employees
ORDER BY emp_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Db2: VALUES used as a standalone SELECT
VALUES (CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP);

-- T-SQL: use SELECT
SELECT GETDATE(), CAST(GETDATE() AS TIME), SYSDATETIME();

-- Db2: days between two dates
SELECT DAYS(end_date) - DAYS(start_date) FROM contracts;

-- T-SQL
SELECT DATEDIFF(DAY, start_date, end_date) FROM contracts;

-- Db2: add months
SELECT start_date + 3 MONTHS FROM contracts;

-- T-SQL
SELECT DATEADD(MONTH, 3, start_date) FROM contracts;

-- Db2: add days
SELECT start_date + 30 DAYS FROM contracts;

-- T-SQL
SELECT DATEADD(DAY, 30, start_date) FROM contracts;

-- Db2: date durations
SELECT hire_date + 1 YEAR + 6 MONTHS + 15 DAYS FROM employees;

-- T-SQL
SELECT DATEADD(DAY, 15, DATEADD(MONTH, 6, DATEADD(YEAR, 1, hire_date)))
FROM employees;

-- Db2: MONTHS_BETWEEN
SELECT MONTHS_BETWEEN(end_date, start_date) FROM contracts;

-- T-SQL
SELECT DATEDIFF(MONTH, start_date, end_date) FROM contracts;

-- Db2: POSSTR (find position of substring)
SELECT POSSTR(full_name, ',') FROM employees;

-- T-SQL: CHARINDEX (note: argument order reversed)
SELECT CHARINDEX(',', full_name) FROM employees;

-- Db2: STRIP (remove leading/trailing characters)
SELECT STRIP(account_code, BOTH, '0') FROM accounts;

-- T-SQL: TRIM (SQL Server 2017+)
SELECT TRIM('0' FROM account_code) FROM accounts;

-- Db2: HEX function
SELECT HEX(binary_col) FROM data_table;

-- T-SQL: CONVERT with style 2
SELECT CONVERT(VARCHAR(MAX), binary_col, 2) FROM data_table;

-- Db2: DIGITS function (convert number to character)
SELECT DIGITS(account_num) FROM accounts;

-- T-SQL: RIGHT + REPLICATE for zero-padded conversion
SELECT RIGHT(REPLICATE('0', 10) + CAST(account_num AS VARCHAR(10)), 10)
FROM accounts;

-- Db2 special registers -> T-SQL equivalents
-- CURRENT DATE          -> CAST(GETDATE() AS DATE)
-- CURRENT TIME          -> CAST(GETDATE() AS TIME)
-- CURRENT TIMESTAMP     -> SYSDATETIME()
-- CURRENT USER          -> CURRENT_USER or SUSER_SNAME()
-- CURRENT SCHEMA        -> SCHEMA_NAME()
-- CURRENT SERVER        -> @@SERVERNAME
-- CURRENT PATH          -> (no equivalent; schemas are explicit)
-- CURRENT TIMEZONE      -> DATENAME(TZOFFSET, SYSDATETIMEOFFSET())

-- Db2 MERGE
MERGE INTO target_table t
USING source_table s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET t.name = s.name, t.amount = s.amount
WHEN NOT MATCHED THEN
    INSERT (id, name, amount) VALUES (s.id, s.name, s.amount)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- T-SQL MERGE (nearly identical but requires semicolon terminator)
MERGE INTO target_table AS t
USING source_table AS s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET t.name = s.name, t.amount = s.amount
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, name, amount) VALUES (s.id, s.name, s.amount)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- Db2: get next sequence value
VALUES NEXT VALUE FOR order_seq INTO :order_id;
-- or in a SELECT
SELECT NEXT VALUE FOR order_seq FROM SYSIBM.SYSDUMMY1;

-- T-SQL: get next sequence value
SELECT NEXT VALUE FOR order_seq;  -- standalone SELECT works
-- or in a variable
DECLARE @order_id BIGINT = NEXT VALUE FOR order_seq;

-- Db2: DECIMAL function for casting
SELECT DECIMAL(amount_char, 10, 2) FROM transactions;

-- T-SQL: CAST or CONVERT
SELECT CAST(amount_char AS DECIMAL(10,2)) FROM transactions;

-- Db2: INTEGER function
SELECT INTEGER(quantity_char) FROM orders;

-- T-SQL
SELECT CAST(quantity_char AS INT) FROM orders;

-- Db2: isolation in query
SELECT * FROM accounts WITH UR;   -- Uncommitted Read
SELECT * FROM accounts WITH CS;   -- Cursor Stability
SELECT * FROM accounts WITH RS;   -- Read Stability
SELECT * FROM accounts WITH RR;   -- Repeatable Read

-- T-SQL: table hints
SELECT * FROM accounts WITH (NOLOCK);           -- Read Uncommitted
SELECT * FROM accounts WITH (READCOMMITTED);    -- default
SELECT * FROM accounts WITH (REPEATABLEREAD);   -- Repeatable Read
SELECT * FROM accounts WITH (SERIALIZABLE);     -- Serializable

-- Db2
CREATE TABLE employees (
    emp_id INTEGER NOT NULL,
    ssn CHAR(9) NOT NULL,
    name VARCHAR(100),
    CONSTRAINT pk_employees PRIMARY KEY (emp_id),
    CONSTRAINT uk_ssn UNIQUE (ssn)
);

-- T-SQL (identical syntax)
CREATE TABLE employees (
    emp_id INT NOT NULL,
    ssn CHAR(9) NOT NULL,
    name VARCHAR(100),
    CONSTRAINT pk_employees PRIMARY KEY (emp_id),
    CONSTRAINT uk_ssn UNIQUE (ssn)
);

-- Db2
ALTER TABLE orders ADD CONSTRAINT chk_status
    CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED'));

-- T-SQL (identical)
ALTER TABLE orders ADD CONSTRAINT chk_status
    CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED'));

-- Db2: standard index
CREATE INDEX idx_emp_dept ON employees (dept_id);

-- T-SQL (identical)
CREATE INDEX idx_emp_dept ON employees (dept_id);

-- Db2: unique index with INCLUDE columns
CREATE UNIQUE INDEX idx_emp_email ON employees (email)
    INCLUDE (name, dept_id);

-- T-SQL
CREATE UNIQUE INDEX idx_emp_email ON employees (email)
    INCLUDE (name, dept_id);

-- Db2: clustering index (physical ordering)
CREATE INDEX idx_orders_date ON orders (order_date) CLUSTER;

-- T-SQL: clustered index (physical ordering)
CREATE CLUSTERED INDEX idx_orders_date ON orders (order_date);

-- Db2
CREATE TABLE FINANCE.accounts (
    account_id INTEGER NOT NULL,
    balance DECIMAL(15,2)
);

-- T-SQL
CREATE TABLE FINANCE.accounts (
    account_id INT NOT NULL,
    balance DECIMAL(15,2)
);

-- Db2: GENERATED ALWAYS
CREATE TABLE orders (
    order_id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    order_date DATE
);

-- T-SQL: IDENTITY
CREATE TABLE orders (
    order_id INT IDENTITY(1,1),
    order_date DATE
);

-- Db2: GENERATED BY DEFAULT (allows explicit values)
CREATE TABLE orders (
    order_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    order_date DATE
);

-- T-SQL: IDENTITY with SET IDENTITY_INSERT for explicit values
CREATE TABLE orders (
    order_id INT IDENTITY(1,1),
    order_date DATE
);
-- To insert explicit values:
SET IDENTITY_INSERT orders ON;
INSERT INTO orders (order_id, order_date) VALUES (100, '2026-01-01');
SET IDENTITY_INSERT orders OFF;