DB2 → PostgreSQL: A Worked Example
A worked DB2 to PostgreSQL example: FETCH FIRST n ROWS ONLY, SYSIBM.SYSDUMMY1, VALUES quirks, identity columns, and SQL/PL to PL/pgSQL patterns.
DB2 (LUW) is, in some ways, the gentlest of these migrations: it hews closer to the SQL standard than Oracle or T-SQL, so several constructs that look like work are nearly no-ops in PostgreSQL. The catch is a handful of behavioral differences in comparison and arithmetic that are very easy to miss. Swordfish carries 83 DB2 rules. The shared workflow is in the Oracle post; below are the DB2-specific findings.
The “good news” conversions — often no-ops
FETCH FIRST n ROWS ONLY (DB2-001) needs no change. PostgreSQL supports the same standard syntax, so LIMIT is optional, not required:
SELECT * FROM orders ORDER BY created_at FETCH FIRST 10 ROWS ONLY; -- valid in bothThe || concatenation operator (DB2-006) works identically in PostgreSQL. CURRENT DATE / CURRENT TIMESTAMP without parentheses (DB2-008) map straight to CURRENT_DATE / CURRENT_TIMESTAMP. SYSIBM.SYSDUMMY1 (DB2’s DUAL) just goes away; PostgreSQL SELECT needs no FROM. Swordfish flags these so you can verify them, but most are confirm-and-move-on.
The conversions that need real work
VALUES INTO (DB2-007), DB2’s single-row assignment, becomes SELECT ... INTO in PL/pgSQL. MERGE (DB2-024) maps to PostgreSQL’s INSERT ... ON CONFLICT (or native MERGE on PG 15+, with semantics worth checking). DB2 SQL PL procedures (the DB2-P* rules: BEGIN ATOMIC, DECLARE HANDLER, SIGNAL/RESIGNAL, cursors WITH RETURN) rewrite to PL/pgSQL: the labor-heavy part, and the place business logic and inferred knowledge live.
The behavioral traps
CHAR(n) right-pad comparison: DB2-BEH-003 (high). This is the DB2 trap that ships silently wrong. DB2 pads CHAR(n) values and, in comparisons, effectively ignores trailing-space differences — 'abc' and 'abc ' compare equal. PostgreSQL’s char(n) is closer but the application code, especially through drivers and varchar comparisons, often does not preserve that blank-padded equality. Joins and lookups on CHAR columns can start missing rows. The fix is usually to rtrim() both sides or migrate CHAR(n) to varchar deliberately — but only after confirming the padding equality was load-bearing.
DECIMAL division returns DECIMAL: DB2-BEH-004 (medium/high). DB2 and PostgreSQL apply different scale/precision rules to DECIMAL division, so a calculation that produced a specific number of decimal places in DB2 can produce a different scale in PostgreSQL. In financial code, that’s a rounding difference that compounds. Review division on DECIMAL/NUMERIC columns and pin the scale explicitly with round(... , n) where the result feeds money math.
WITH UR (Uncommitted Read): DB2-BEH-001 (medium). DB2’s dirty-read isolation hint, the cousin of SQL Server’s NOLOCK. PostgreSQL’s MVCC makes it unnecessary; the hint is removed. Flag it for the same reason: it often marks a spot where someone was working around lock contention.
FETCH FIRST in dynamic SQL: DB2-BEH-002 (low/medium). When the row-limit clause is assembled into a dynamic SQL string, the extraction tiers have to find it before it can be checked. Mostly a detection note: confirm the dynamically-built variants were caught.
The shape of a DB2 migration: more of it ports clean than you’d guess, the stored-procedure rewrite is the bulk of the effort, and the CHAR-padding and DECIMAL-division behaviors are the two findings worth slowing down for. Last in the per-database series: Sybase.
Reproduce this walkthrough against the bundled sample apps: github.com/EnterpriseDB/swordfish-sample-apps. Swordfish itself (Apache-2.0): github.com/EnterpriseDB/swordfish-migrations