Db2 to Postgres Migration: A Guide to Schema, SQL PL, and Application Code

Keerthi Murali | Customer Delivery Manager

TL;DR

Size the migration by SQL PL and application coupling. Procedure complexity and the Db2-specific SQL in connecting applications set the effort more than table count or data volume.
Treat SQL PL as a review workload. Control flow and cursors convert predictably. Condition handlers, returned result sets, and Java stored procedures need an engineer’s judgment.
Map isolation levels by behavior. Db2’s Repeatable Read corresponds to PostgreSQL’s Serializable, and PostgreSQL reports concurrency conflicts as errors the application has to retry.
Convert the application code alongside the schema. JDBC drivers, ORM dialects, embedded Db2 SQL, and SQLCODE error handling all change, and schema converters read none of it.
Prove equivalence with output comparison. Row counts cover the data. Queries, procedures, and batch jobs need the same inputs run on both engines before cutover.

What a Db2 to Postgres Migration Involves

A Db2 to Postgres migration moves a Db2 LUW database’s schema, data, and SQL PL code onto PostgreSQL, along with the application code that connects to Db2. Converters handle most of the schema and the data copy. SQL PL procedures, transaction behavior, and the Java and .NET code that calls Db2 carry most of the effort and the risk.

This guide is for architects, DBAs, and engineering leaders planning a Db2 to PostgreSQL move, on self-managed PostgreSQL or a managed service on any cloud. The move is one form of database modernization. It shares its target engine and validation method with an Oracle to PostgreSQL migration and a SQL Server to PostgreSQL migration.

This guide covers Db2 for Linux, UNIX and Windows (LUW). Db2 for z/OS and Db2 for i migrations carry different constraints and aren’t covered here.

Why Teams Are Moving From Db2 to PostgreSQL

Three pressures put a Db2 to PostgreSQL move on the roadmap.

Version end of support. IBM lists 30 April 2027 as the end of support for Db2 11.5 in all editions except Base Edition, whose standard support ended 30 September 2025 [1]. Every 11.5 estate faces an upgrade to Db2 12.1 or an exit.
Licensing. Db2 carries commercial license and support fees. PostgreSQL has no license fee, so recurring cost moves to hosting and support.
Skills. In Stack Overflow’s 2025 Developer Survey, 58.2% of professional developers reported using PostgreSQL and 2.1% reported using Db2 [2].

A move to PostgreSQL changes the SQL PL and application estate along with the database, and the sizing work below measures how much.

How to Size a Db2 to Postgres Migration

Effort follows four sets of counts. Pull each from the Db2 catalog and the application repositories before estimating.

Database objects. Tables, views, MQTs, sequences, triggers, and routines from SYSCAT.TABLES, SYSCAT.VIEWS, SYSCAT.TRIGGERS, and SYSCAT.ROUTINES, with routines split by language (SQL, Java, C).
SQL PL complexity. Per procedure, count condition handlers, dynamic SQL, global temporary tables, and cursors returned to callers. These constructs predict review effort better than procedure count.
Application coupling. Connecting applications, embedded SQL statements, ORM versus hand-written data access, SQLCODE references, and isolation clauses such as WITH UR.
Data profile. Total volume, LOB values over 1 GB, DECFLOAT columns, timestamps with more than six fractional digits, and the longest acceptable downtime window.

A legacy system audit produces the same inventory across a wider estate, and these counts slot into it.

How Long a Db2 to PostgreSQL Migration Takes

Duration scales with the SQL PL and application counts above, so an estimate built from table count or data volume understates it. A pilot conversion of one representative application and the routines it calls turns those counts into a measured rate for the timeline.

How to Migrate Db2 to PostgreSQL Step by Step

To migrate Db2 to PostgreSQL, run seven steps in order. Each names the work most often skipped.

Inventory. Catalog objects, SQL PL, and every connecting application. Often skipped are application repositories and scripts that call the Db2 command-line processor.
Schema conversion. Map types, identity columns, defaults, and identifier case. Often skipped is timestamp precision above six digits.
SQL PL conversion. Convert procedures, triggers, and functions. Often skipped is a per-object test against Db2 output.
Application conversion. Change drivers, dialects, embedded SQL, and error handling. Often skipped is isolation level mapping.
Data movement. Bulk load, or bulk load plus change capture. Often skipped is the identity sequence reset after the load.
Validation. Compare data, queries, procedure outputs, and performance. Often skipped is business output comparison for the same period.
Cutover. Move applications in the planned order. Often skipped is a tested rollback path.

Where effort concentrates in a Db2 to PostgreSQL migration

Db2 Data Type and Function Mapping for PostgreSQL

Most Db2 types map directly. The rows that need a decision carry a note.

Db2 Data Types and Their PostgreSQL Equivalents

Db2 LUW Type	PostgreSQL Type	Conversion Note
`SMALLINT`, `INTEGER`, `BIGINT`	`smallint`, `integer`, `bigint`	Direct
`DECIMAL(p,s)`, `NUMERIC(p,s)`	`numeric(p,s)`	Direct
`DECFLOAT(16)`, `DECFLOAT(34)`	`numeric` or `double precision`	No decimal floating-point type. Choose per column by rounding needs
`CHAR(n)`, `VARCHAR(n)`	`char(n)`, `varchar(n)`	Db2 counts bytes by default and PostgreSQL counts characters
`CHAR(n) FOR BIT DATA`, `VARCHAR(n) FOR BIT DATA`	`bytea`	Comparison and sort behavior change
`CLOB`, `DBCLOB`, `LONG VARCHAR`	`text`	PostgreSQL caps a single value at 1 GB
`BLOB`	`bytea`, or large objects above 1 GB	Db2 BLOBs reach 2 GB, above the 1 GB value cap
`DATE`	`date`	Direct
`TIME`	`time(0)`	Db2 `TIME` holds no fractional seconds
`TIMESTAMP(p)`	`timestamp(p)`	Db2 allows up to 12 fractional digits and PostgreSQL stops at 6, so values truncate
`XML`	`xml`	PostgreSQL supports XPath 1.0 and no XQuery, so XQuery expressions need rewriting
`GENERATED ... AS IDENTITY`	`GENERATED ... AS IDENTITY`	Same syntax. Reset the sequence after loading data

Db2 NOT NULL WITH DEFAULT with no value supplies a type-based default such as zero, blanks, or the current date. PostgreSQL needs each default declared explicitly.

Db2 Functions and Their PostgreSQL Equivalents

Db2 LUW	PostgreSQL	Direct Equivalent
`VALUE(a, b)`, `NVL(a, b)`	`COALESCE(a, b)`	Yes
`LISTAGG`	`string_agg`	Yes
`NEXT VALUE FOR seq`	`nextval('seq')`	Yes
`IDENTITY_VAL_LOCAL()`	`INSERT ... RETURNING`, or `lastval()`	Yes
`LOCATE(search, source)`	`strpos(source, search)`	Yes, with arguments reversed
`VARCHAR_FORMAT`, `TIMESTAMP_FORMAT`	`to_char`, `to_timestamp`	Partial. Format elements differ
`DAYOFWEEK(d)`	`EXTRACT(DOW FROM d) + 1`	No. Db2 numbers Sunday as 1 and PostgreSQL as 0
`DAYS(d)`	`d - DATE '0001-01-01' + 1`	No. Rewrite as date arithmetic
`TIMESTAMPDIFF(n, CHAR(ts1 - ts2))`	`EXTRACT(EPOCH FROM ts1 - ts2)` or `age()`	No. Db2 estimates with 30-day months, so results differ
`GENERATE_UNIQUE()`	`gen_random_uuid()`	No. Output format and ordering differ

Materialized query tables map to PostgreSQL materialized views, and REFRESH IMMEDIATE has no equivalent. The PostgreSQL planner never reroutes a query to a materialized view, so queries that relied on MQT routing have to name the view.

Converting Db2 SQL PL Procedures, Triggers, and Functions to PL/pgSQL

SQL PL and PL/pgSQL share SQL/PSM ancestry. Variable declarations, IF and CASE logic, loops, and cursors convert predictably, often with syntax changes alone.

Triggers, functions, and views are where conversion stalls. The open-source db2topg converter writes views, triggers, and check constraints to a separate unsure.sql file, and its documentation states that triggers and functions “will definitely fail” to convert as-is [3].

Run SQL PL as its own workstream. Inventory every routine and trigger with its callers, classify each by the constructs it uses, convert by class starting with the most-called objects, and test each against Db2 with the same inputs.

Four constructs account for most of the review effort.

DYNAMIC RESULT SETS. PostgreSQL procedures don’t return result sets to callers. These become set-returning functions or refcursors, and every Java and .NET caller changes with them.
Global temporary tables. DECLARE GLOBAL TEMPORARY TABLE and the SESSION schema become CREATE TEMP TABLE.
SIGNAL and RESIGNAL. These become RAISE EXCEPTION with an explicit ERRCODE.
Triggers. A Db2 trigger defines its body inline. PostgreSQL splits it into a trigger function plus a CREATE TRIGGER statement, doubling the objects to test.

Db2 Condition Handlers and PL/pgSQL Exception Blocks

Db2 SQL PL declares handlers at the top of a block. A CONTINUE handler runs and resumes at the statement after the one that failed. An EXIT handler runs and leaves the block.

PL/pgSQL has one mechanism, an EXCEPTION clause at the end of a BEGIN ... END block. An error rolls back everything the block did, and control moves to the matching handler.

EXIT handlers map onto an EXCEPTION clause directly.
CONTINUE handlers have no equivalent. Each protected statement moves into its own nested block with its own EXCEPTION clause.
SQLCODE and SQLSTATE checks inside handlers become SQLSTATE tests or GET STACKED DIAGNOSTICS.

Exception blocks cost more to enter and exit than plain blocks. A converted procedure that wraps every statement in a loop this way needs a performance check against its Db2 original.

Options for Db2 Java Stored Procedures on PostgreSQL

Db2 routines defined with LANGUAGE JAVA have three paths on PostgreSQL.

PL/Java. An open-source extension that runs Java inside PostgreSQL. Many managed services limit extensions to a fixed list, so confirm support on the target first.
PL/pgSQL rewrite. Fits routines that are thin wrappers around SQL.
Application service. Fits routines that hold business logic, since the Java code already exists and a service is easier to test.

Db2 vs. PostgreSQL Differences That Change Query Results

Each difference below compiles, passes routine tests, and changes results after cutover.

Difference	Db2 LUW	PostgreSQL	Business Impact
Date subtraction	Returns a yyyymmdd duration, so 10215 means 1 year, 2 months, 15 days	Returns days as an integer	Aging, interest, and SLA calculations go wrong with no error
Timestamp subtraction	Returns a `DECIMAL(20,6)` duration	Returns an `interval`	Numeric math on the result fails or misreads it
`CURRENT TIMESTAMP` timing	Evaluated per statement	Fixed at transaction start	Audit rows written in one long transaction share one timestamp
Trailing blanks in comparisons	Shorter string padded with blanks, so `'abc' = 'abc '` is true	`varchar` and `text` compared as stored	Joins and lookups on padded keys return fewer rows
Implicit defaults	`WITH DEFAULT` supplies zero, blanks, or the current date	No default unless one is declared	Inserts that omit the column fail or store NULL
Sort order	Collation fixed at database creation, often binary `IDENTITY`	Default collation follows the server locale	`ORDER BY`, range predicates, and keyset pagination return rows in a different order
Partitioned table keys	Unique indexes can span partitions	Unique keys must include every partition key column	A redesigned key can admit duplicates Db2 blocked

Db2 Isolation Levels and Their PostgreSQL Equivalents

Db2 names its isolation levels differently from the SQL standard, and IBM’s own JDBC mapping shows the offset [4].

Cursor Stability (CS), the Db2 default, corresponds to JDBC Read Committed and maps to PostgreSQL Read Committed.
Read Stability (RS) corresponds to JDBC Repeatable Read and maps to PostgreSQL Repeatable Read.
Repeatable Read (RR) corresponds to JDBC Serializable and maps to PostgreSQL Serializable. A name-based mapping to PostgreSQL Repeatable Read drops it one level.
Uncommitted Read (UR) has no PostgreSQL equivalent. PostgreSQL runs Read Uncommitted as Read Committed [5], and WITH UR clauses fail to parse.

The locking model changes too. Db2 enforces RS and RR with locks, so conflicting writers wait. PostgreSQL uses MVCC and reports conflicts at these levels as serialization failures (SQLSTATE 40001) that applications must be prepared to retry [5]. Code written around Db2 lock waits usually has no retry loop.

Db2 Application Code Changes: Drivers, ORMs, Embedded SQL, and Error Codes

Application code changes in every Db2 to PostgreSQL migration. Schema converters don’t read application repositories, so this workstream needs its own inventory and owners.

JDBC, ODBC, and ORM Dialect Changes

Java. The IBM Data Server Driver (com.ibm.db2.jcc.DB2Driver, jdbc:db2:// URLs) becomes PgJDBC (org.postgresql.Driver, jdbc:postgresql://).
ODBC and CLI. Db2 CLI/ODBC applications move to psqlODBC, with DSNs and connection attributes rebuilt.
.NET. The IBM Db2 .NET provider becomes Npgsql, and Entity Framework applications switch to the Npgsql EF Core provider.
ORMs. Hibernate moves from DB2Dialect to PostgreSQLDialect. Native queries and sequence allocation settings need review.

Identifier case causes the most breakage. Db2 folds unquoted names to uppercase and PostgreSQL folds them to lowercase, so a converter that keeps uppercase quoted identifiers forces every query to quote them. Lowercase unquoted names keep application SQL working as written.

Db2-Specific SQL in Java and .NET Code

Embedded SQL carries Db2 syntax that the ORM never translates.

CURRENT TIMESTAMP and CURRENT DATE written with a space.
FROM SYSIBM.SYSDUMMY1 and standalone VALUES statements.
WITH UR, WITH CS, and WITH RS isolation clauses.
VALUES NEXT VALUE FOR sequence calls.
SELECT ... FROM FINAL TABLE (INSERT ...), which becomes INSERT ... RETURNING.

FETCH FIRST n ROWS ONLY is standard SQL and runs unchanged on PostgreSQL.

Mapping Db2 SQLCODE Error Handling to PostgreSQL SQLSTATE

The IBM JDBC driver returns the Db2 SQLCODE from getErrorCode(). PgJDBC exposes the SQLSTATE through getSQLState(), so code that branches on SQLCODE values stops matching.

-803 (duplicate key) becomes 23505.
-530 (foreign key violation) becomes 23503.
-911 (deadlock or lock timeout with rollback) splits into 40P01 for deadlocks and 55P03 for lock waits.
40001 (serialization failure) is new and needs a retry path.

This failure raises no error. An upsert or retry branch keyed on -803 or -911 never fires, and the application reports a generic error where it used to recover. Spring’s DataAccessException translation covers applications that use it, and custom handlers need a code search.

Start a $0 Modernization Assessment

Dependency Map

Risk Heatmap

Modernization Plan (3-5 Days)

+more

Moving Db2 Data to PostgreSQL and Planning Cutover

The data method follows the downtime the business accepts.

Bulk load. Export tables from Db2 in delimited format and load them with PostgreSQL COPY. Reset every identity sequence to the highest loaded key afterward, or the first application insert fails with a duplicate key error.
Bulk load plus change data capture. Keeps the cutover window short. Db2 LUW CDC needs archive logging and DATA CAPTURE CHANGES on each table, and rows written by the LOAD utility bypass capture.
Polling. Copies rows changed since the last run, keyed on a reliable last-updated column, with a separate method for deletes.

Log-based CDC for Db2 LUW carries its own licensing. Debezium’s Db2 connector depends on Db2 SQL Replication, which requires a separate IBM InfoSphere Data Replication license [6]. That cost is why many Db2 LUW migrations use polling when the tables support it.

Cutover runs as one big-bang window or in phases by application group, and a phased cutover needs the coexistence plan below. A zero-downtime migration needs a contractual availability requirement to justify the replication pipeline.

Running Db2 and PostgreSQL Side by Side During Migration

Coexistence works when each table has one writer at a time. Applications that share tables move together, so the connection inventory from sizing sets the cutover groups.

The system that owns a table replicates its changes to the other. Reporting reads can move to PostgreSQL early, and writes follow once the owning application cuts over.

How to Validate That PostgreSQL Returns the Same Results as Db2

Validation runs at four levels. The behavior table above supplies fixed test cases for the second and third.

Data. Row counts per table, then column checksums after normalizing both sides for timestamp precision, blank padding, DECFLOAT rounding, and boolean values.
Queries. Capture the production query set from Db2’s package cache with MON_GET_PKG_CACHE_STMT, run it on both engines, and compare result sets, including order where the query sorts.
Procedures and batch jobs. Run each on both engines with the same inputs and compare results, output parameters, and side effects.
Performance. Replay the top statements by elapsed time on PostgreSQL and check their plans, starting with exception-heavy procedures and queries that lost MQT routing.

Data-level checks follow the same techniques as any data migration validation. Query and procedure comparison needs Db2 available as the reference system until sign-off.

Find Where Behavioral Risk Sits Before Conversion

Behavioral equivalence across SQL PL, isolation handling, and application code takes the most evidence in a Db2 exit. A $0 Modernization Assessment shows where that risk concentrates in your codebase before conversion starts.

Tools to Convert Db2 to PostgreSQL and What Each One Covers

Every tool class that can convert Db2 to PostgreSQL covers schema and data. SQL PL, application code, and validation coverage varies by class.

Open-source converters. db2topg converts DDL from db2look output and generates export and load scripts. It leaves views, triggers, functions, and check constraints for manual work, and the project is marked inactive [3].
Commercial converters. Products such as SQLines, Ispirer, Full Convert, and Convert-in convert schema, data, and part of the SQL PL. Procedure and trigger coverage differs by product, and application code and validation stay with the team.
Cloud-provider tooling. AWS DMS Schema Conversion and AWS SCT convert schema and SQL PL, flagging what they can’t convert as action items, and AWS DMS moves the data. DMS Schema Conversion targets Amazon RDS and Aurora PostgreSQL.
Full-migration services. Consultancies run the whole program with their own tooling. Ask what validation evidence they deliver and who owns the application changes.

How Gen AI Converts Db2 SQL PL and Embedded SQL

Gen AI conversion performs well on three kinds of work that rule-based converters leave.

Translating whole procedures with their surrounding context, including handler restructuring.
Finding Db2 SQL embedded in Java and .NET repositories.
Mapping dependencies between routines, applications, and jobs.

Hyperscaler AI tooling for this pair is tied to a target. AWS DMS Schema Conversion offers generative AI conversion for Db2 LUW to Amazon RDS for PostgreSQL and Aurora PostgreSQL [7].

AI-converted code needs three controls before merge.

Human review of every diff, checking handler and transaction semantics.
Behavioral testing against Db2 outputs on the same inputs.
Performance checks against PostgreSQL’s planner.

Programs at scale formalize these controls as safety nets for Gen AI in legacy modernization.

How Legacyleap’s Gen AI Agents Run a Db2 to PostgreSQL Migration

Legacyleap is a Gen AI-powered legacy modernization platform built on multi-agent orchestration. For a Db2 exit, its five agents cover the Java and .NET code that connects to Db2 and the database logic it depends on.

The agents run the Assess, Comprehend, Modernize, Validate, and Deploy lifecycle in sequence.

Assessment Agent. Inventories the application code that calls Db2 and its dependencies, with a dependency map, modernization hotspots, and an effort and timeline estimate.
Documentation Agent. Reconstructs the business logic in procedures and triggers, with data flows and integration maps.
Recommendation Agent. Decides per module whether logic stays in the database or moves to Java or .NET services, and produces an ordered migration plan.
Modernization Agent. Executes the plan as diff-based pull requests covering data access code, embedded SQL, error handling, and logic moved into services, along with SQL PL routines converted to PL/pgSQL. Roughly 70% of the modernization work is automated, and engineers review and direct the rest.
QA Agent. Generates unit, integration, regression, and API tests, and produces behavior parity validation reports against the Db2 baseline before cutover.

No agent merges, deploys, or executes code autonomously. Full-codebase grounding reads application code and the routines it calls together, and all processing runs inside the organization’s own infrastructure. The same agents run broader data platform modernization programs when the Db2 exit is one part of a wider data estate change.

Next Steps for Planning a Db2 to Postgres Migration

A Db2 to Postgres migration is scoped by the SQL PL, transaction behavior, and application code around the database. Counting that estate and comparing outputs on both engines give the program a realistic plan and a defensible sign-off.

For teams scoping the move, the $0 Modernization Assessment produces a dependency and module map, a risk and complexity heatmap, and a modernization plan for one representative codebase. It takes 3 to 5 days, runs inside the client’s environment, and draws on more than 150 production-grade assessments completed to date.

A technical demo shows how the five agents take an approved plan through conversion and parity validation.

Size the SQL PL and Application Code Before Conversion

A $0 Modernization Assessment produces a dependency and module map, a risk and complexity heatmap, and a modernization plan for one representative codebase in 3 to 5 days, at no cost and inside your own environment.

FAQ

Q1. Can an application move from Db2 to PostgreSQL without code changes?

No. Beyond the driver and dialect, unqualified table names resolve through PostgreSQL’s search_path, so set it or PgJDBC’s currentSchema to the converted schema.

Q2. Does PostgreSQL support Db2 SQL PL?

No. SQL PL converts to PL/pgSQL, and Db2 modules created with CREATE MODULE have no equivalent, so their routines usually move into a dedicated PostgreSQL schema.

Q3. What is the best tool to convert Db2 to PostgreSQL?

No single tool covers schema, SQL PL, application code, and validation. Running db2look -e output through any converter gives a fast first-pass object inventory.

Q4. How are Db2 BLOB and CLOB values over 1 GB migrated to PostgreSQL?

They move to PostgreSQL large objects or external storage. Large objects need explicit cleanup, such as the lo_manage trigger, because deleting the row leaves the object behind.

Q5. What replaces Db2 materialized query tables in PostgreSQL?

Materialized views, refreshed with REFRESH MATERIALIZED VIEW. A CONCURRENTLY refresh keeps the view readable during refresh and requires a unique index on the view.

Q6. How long does a Db2 to Postgres migration take?

It depends on SQL PL volume, connecting applications, and data size. Keep Db2 available through one full business cycle after cutover, since month-end and quarter-end outputs match last.

Q7. Can Db2 and PostgreSQL run side by side during a migration?

Yes, with changes replicated from whichever system owns each table. Reverse replication from PostgreSQL back to Db2 after cutover keeps a rollback path open until sign-off.

References

[1] IBM Support. Db2 Distributed End of Support (EOS) Dates

[2] Stack Overflow. 2025 Developer Survey, Technology

[3] dalibo/db2topg on GitHub

[4] IBM Documentation. IBM Data Server Driver for JDBC and SQLJ isolation levels

[5] PostgreSQL Documentation. Transaction Isolation

[6] Debezium Documentation. Debezium connector for Db2

[7] AWS Database Migration Service User Guide. Converting database objects with generative AI

Book a $0 Assessment

We will scan a portion of your legacy codebase and share documentation, architecture maps, dependency graphs, in 3-5 days.

Book a Time →

Share the Blog