Migrating DB2 LUW to Aurora PostgreSQL: A Field Guide

Why Teams Are Moving Off DB2 LUW

The DB2 LUW migration conversation starts in one of three places: IBM licensing renewal sticker shock, a cloud-first mandate that landed on a 15-year-old DB2 installation, or a DBA team that can no longer recruit junior talent who know DB2.

All three are real forcing functions. IBM's DB2 licensing — processor-based, annual maintenance, support tiers — runs $25,000–$200,000/year for enterprise environments. When the renewal arrives on the same quarter as a CFO asking about cloud costs, Aurora's pay-per-use model looks compelling. Aurora PostgreSQL eliminates the licensing cost entirely and runs on managed infrastructure with built-in Multi-AZ replication, automated backups, and no DBA overhead for patching.

The skills argument is also real: the PostgreSQL talent pool is orders of magnitude larger than DB2 LUW. Your next hire is more likely to know PostgreSQL deeply than DB2, and your existing team can learn PostgreSQL in months rather than years.

What's less often stated honestly: DB2 LUW to Aurora PostgreSQL is a hard migration. Not impossible, not a reason to stay on DB2, but harder than Oracle-to-Aurora or SQL Server-to-RDS because DB2's dialect, system catalog, and PL/SQL equivalent (DB2 SQL PL) are further from PostgreSQL than Oracle PL/SQL is. This guide tells you what you're actually getting into.

Phase 1: Assessment

Before writing a single line of migration code, spend two weeks in assessment. The output should be a migration complexity score for each database object.

What to inventory:

-- DB2: Count objects by type
SELECT TABSCHEMA, TYPE, COUNT(*) AS cnt
FROM SYSCAT.TABLES
WHERE TABSCHEMA NOT LIKE 'SYS%'
GROUP BY TABSCHEMA, TYPE
ORDER BY cnt DESC;

For each schema, catalog:

• Tables (row count, size, partition strategy)
• Views (dependency depth — views that reference views need careful ordering)
• Stored procedures and functions (SQL PL vs. Java vs. external — only SQL PL migrates to PL/pgSQL; the others need a different strategy)
• Triggers
• Sequences and IDENTITY columns
• User-defined types and functions
• Indexes (including MDC and range-clustered tables, which have no direct PostgreSQL equivalent)

Complexity tiers:

• Green: Simple CRUD tables, standard SQL procedures, straightforward views
• Yellow: Procedures using DB2-specific SQL PL features, IDENTITY columns, XML storage, partitioned tables
• Red: Procedures using DB2 OLAP functions, MDC tables, Java stored procedures, DB2 FederationServer, MQT-backed reporting

Phase 2: Schema Conversion

Data Type Mapping

Most DB2 data types map cleanly to PostgreSQL. The ones that don't are where migrations break:

The IDENTITY column conversion is the most frequent source of breakage. DB2 GENERATED ALWAYS AS IDENTITY becomes PostgreSQL GENERATED ALWAYS AS IDENTITY (PostgreSQL 10+) or SERIAL for older targets. The critical detail: if your application inserts explicit values into IDENTITY columns (common in DB2 with GENERATED BY DEFAULT), you need GENERATED BY DEFAULT AS IDENTITY in PostgreSQL, not GENERATED ALWAYS.

Schema-Level DDL Differences

-- DB2
CREATE TABLE orders (
    order_id  INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    customer  VARCHAR(100) NOT NULL,
    created   TIMESTAMP DEFAULT CURRENT TIMESTAMP
);

-- PostgreSQL equivalent
CREATE TABLE orders (
    order_id  INTEGER GENERATED ALWAYS AS IDENTITY,
    customer  VARCHAR(100) NOT NULL,
    created   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Notable DDL differences:

• CURRENT TIMESTAMP (DB2) → CURRENT_TIMESTAMP (PostgreSQL) — the space matters
• VARCHAR FOR BIT DATA (DB2 binary strings) → BYTEA
• WITH UR/CS/RS/RR isolation hints in SQL → not supported in PostgreSQL; remove and handle at application layer
• FETCH FIRST n ROWS ONLY → PostgreSQL uses LIMIT n (or FETCH FIRST n ROWS ONLY also works in PostgreSQL 8.4+)

Phase 3: Stored Procedure Conversion

This is where the timeline gets consumed. DB2 SQL PL and PostgreSQL PL/pgSQL are both procedural SQL extensions but have meaningful differences:

Syntax Differences

-- DB2 SQL PL procedure
CREATE PROCEDURE get_customer_orders (
    IN p_cust_id INTEGER,
    OUT p_order_count INTEGER
)
LANGUAGE SQL
BEGIN
    DECLARE v_status VARCHAR(20);

    SELECT COUNT(*) INTO p_order_count
    FROM orders
    WHERE customer_id = p_cust_id;

    IF p_order_count = 0 THEN
        SET v_status = 'NO_ORDERS';
    ELSE
        SET v_status = 'HAS_ORDERS';
    END IF;
END;

-- PostgreSQL PL/pgSQL equivalent
CREATE OR REPLACE PROCEDURE get_customer_orders (
    IN p_cust_id INTEGER,
    INOUT p_order_count INTEGER DEFAULT NULL
)
LANGUAGE plpgsql AS $$
DECLARE
    v_status VARCHAR(20);
BEGIN
    SELECT COUNT(*) INTO p_order_count
    FROM orders
    WHERE customer_id = p_cust_id;

    IF p_order_count = 0 THEN
        v_status := 'NO_ORDERS';
    ELSE
        v_status := 'HAS_ORDERS';
    END IF;
END;
$$;

Key differences:

• SET variable = value (DB2) → variable := value (PostgreSQL)
• OUT parameters work differently in PostgreSQL — calling convention changes
• SIGNAL SQLSTATE (DB2 error handling) → RAISE EXCEPTION (PostgreSQL)
• GET DIAGNOSTICS (DB2) → GET STACKED DIAGNOSTICS (PostgreSQL)
• DB2 ASSOCIATE RESULT SET LOCATOR patterns (for multi-result procedures) → PostgreSQL uses REFCURSOR

Phase 4: Data Migration Tooling

Three approaches, each with tradeoffs:

AWS Database Migration Service (DMS)

Best for: Initial bulk load + CDC (change data capture) during parallel run period.
DB2 LUW support: AWS DMS supports DB2 LUW as a source with CDC via the DB2 log reader.
Gotchas:

• DMS doesn't migrate stored procedures, views, or triggers — schema only
• LOB columns (CLOB/BLOB) require special DMS configuration and are slow
• DMS's type mapping won't catch all DB2 edge cases; validate row counts and spot-check data types after load

pg_dump via Foreign Data Wrapper

Best for: Smaller databases (<50 GB) or when DMS licensing is a concern.
Approach: Use db2_fdw or tds_fdw (for TDS-capable DB2 instances) to read DB2 tables from PostgreSQL, then INSERT INTO ... SELECT FROM fdw_table.
Gotchas: Performance is limited by the FDW throughput; not suitable for large tables without parallel partition-based extraction.

Custom ETL

Best for: Complex transformations, data cleansing requirements, or when source and target schemas diverge significantly.
Tooling: Python with ibm-db (DB2 connector) and psycopg2, or Apache Spark for parallel extraction of large tables.
Advantage: Full control over transformation logic; can handle DB2-specific quirks inline.

Phase 5: Testing and Parallel Run

Never cut over without a parallel run. Run both DB2 and Aurora PostgreSQL simultaneously for at least 2–4 weeks:

1. Dual-write pattern: Application writes go to both databases; reads come from DB2 until validation is complete
2. Row count reconciliation: Automated daily comparison of row counts per table
3. Data spot-checks: Sample 1,000 rows from 20 tables and compare field by field
4. Stored procedure output comparison: For every procedure call in production traffic, compare return values between DB2 and Aurora
5. Performance baseline: Confirm key queries run at acceptable performance on Aurora; don't assume schema equivalence means performance equivalence

The Cutover Decision

Cut over when:

• Row counts reconcile at 100% for 5 consecutive days
• All stored procedure output comparisons pass
• Aurora response times are within 20% of DB2 on p99 for key queries
• The team has rehearsed the rollback procedure (can re-point to DB2 within 15 minutes if something goes wrong)

Keep DB2 running (read-only) for 30 days post-cutover before decommissioning.