DemandTec Cuts SQL Migration Time 7x, Moving from IBM DB2 to PostgreSQL on Azure

Client profile

DemandTec is a global leader in AI-powered retail merchandising technology, headquartered in Chicago, Illinois. For more than 25 years, DemandTec has helped the world's leading retailers transform how they price, promote, and collaborate with suppliers. Today, DemandTec is the only platform that combines AI-powered lifecycle pricing — spanning everyday pricing, promotions, and markdowns — with native trade collaboration, all grounded in consumer demand science. The result: measurable, profitable growth for retailers and suppliers alike.

Objective

DemandTec set out to modernize its data infrastructure by consolidating multiple IBM DB2 LUW 11.05 databases hosted on Microsoft Azure into a single PostgreSQL 16 instance hosted on Microsoft Azure. The primary objectives included:

• Cost optimization. Eliminate proprietary IBM licensing costs and reduce the total cost of database ownership.
• Performance improvement. Leverage PostgreSQL's architecture on Azure to improve query performance and system responsiveness.
• Infrastructure consolidation. Unify disparate DB2 databases into a single, manageable PostgreSQL instance to reduce operational complexity.

Challenge

Migrating a high-throughput retail AI platform from IBM DB2 to PostgreSQL on Azure presented a set of compounding technical and operational challenges:

• Heterogeneous SQL dialects. The source environment relied on DB2-specific SQL constructs — including dynamic statements, computed columns, sequences with negative values, and complex trigger logic — none of which map directly to PostgreSQL equivalents.
• Java stored procedures. The scope included ~60 external SQLJ (Java-embedded SQL) procedures — a migration direction with no established automation baseline. Each procedure required full manual conversion to PL/pgSQL, demanding engineers fluent in both Java and PostgreSQL internals.
• Complex business logic. Critical logic governing transactions, result set handling, datetime arithmetic, and handler mapping was embedded deep in stored procedures, requiring careful analysis and coordinated decision-making before any code could be converted.
• Live scope management. As a multi-month engagement, the source codebase continued to evolve during migration, requiring the team to accommodate incremental additions without disrupting the conversion pipeline.
• Strict scheduling constraints. While the project faced no regulatory or compliance hurdles, the timeline was highly demanding. Scheduling was driven by the desire to align the migration with impending DB2 license renewal dates, and execution required careful coordination to ensure internal teams remained fully responsive to ongoing customer needs throughout the transition.

Solution

POC Phase to prove the approach

The project began with a structured Proof of Concept phase running for ~15 weeks and covering migration of ~ 320 SQL objects and 1.4k tables from the two source DB2 LUW databases. Ispirer engineers used their free assessment tool InsightWays to analyze the existing schema, identify duplicate objects across source databases, and flag complex conversion cases requiring early design decisions.

The POC served a dual purpose: validating the technical approach before committing to the full migration and allowing both teams to align on conversion strategies for the most challenging constructs. The assessment informed the sequencing of work across the main project, with conversion batches ordered to respect object dependencies — a critical factor given the scale of the codebase.

Notably, DemandTec came to the project with a clear internal plan and well-defined delivery expectations. Ispirer adapted its own project structure to align with the client's sequencing requirements, ensuring a smooth and predictable engagement from the outset.

Customization & Migration

To handle the full breadth of DB2-specific patterns at scale, Ispirer's engineering team implemented over 30 improvements to the conversion pipeline prior to and during the main migration phase. These addressed previously unhandled conversion cases, including:

• Parser error fixes and temporary table creation/usage patterns
• Autogenerated timestamp columns and computed columns
• Deterministic functions and dynamic SQL statements
• Sequences with negative values, triggers, and DML-statement handling

These improvements were implemented via scripts, auto-replacements, and custom logic rather than client-specific customization. The project directly drove significant tooling advances that reduced manual effort, improved code quality, and raised output consistency across the entire engagement.

The most technically demanding element of the project was the conversion of SQLJ procedures — external Java classes with embedded SQL — into native PL/pgSQL. This migration direction had no prior automation baseline; every procedure required hand-crafted conversion by engineers capable of reading and writing both Java and PostgreSQL procedural code.

Key technical decisions included approaches to transaction handling, dynamic result sets, return cursors, Java out-parameters, and handler mapping — all resolved through structured, collaborative review sessions with the DemandTec team.

Execution

The main migration phase ran for~5.5 months, followed by a 5-month acceptance phase. The acceptance phase progressed efficiently, driven largely by client-side questions and clarifications rather than rework — a reflection of the code quality delivered. Issues that did arise were typically resolved within one business day, enabling DemandTec's engineering team to maintain testing momentum without disruption.

DemandTec validated converted objects using Python-based test scripts (AI-generated), running parallel calls against both the source DB2 and target PostgreSQL environments to verify functional equivalence. Despite incomplete unit test coverage across the full object set, the project was assessed as successful based on a defect rate below 10% and collaborative resolution of all major conversion issues.

Technologies

• Source: IBM DB2 LUW 11.05
• Target: PostgreSQL 16 on Microsoft Azure
• Special scope: ~60 SQLJ (Java) procedures — converted to PL/pgSQL
• Tools: InsightWays (assessment), SQLWays (conversion)

Benefits and Results

The migration delivered measurable gains in both speed and quality, with broader infrastructure and cost benefits accruing over the project lifetime:

• 7x faster than manual conversion. During the project, the average code conversion speed per developer reached approximately 2,800–3,000 lines of code per day – roughly 7 times faster than manual conversion. This efficiency was enabled by Ispirer's automated solutions for heterogeneous database migration, with a primary emphasis on advanced SQL code transformation.
• Eliminated IBM licensing dependency. Migration to open-source PostgreSQL removed the customer's reliance on costly proprietary IBM database licensing.
• High and stable code quality. Unit test results validated consistent output quality across thousands of converted objects throughout the project lifecycle. The defect rate remained below 10%, and virtually all issues were resolved within one business day of being raised.
• First-of-kind Java migration. The successful conversion of SQLJ procedures to PL/pgSQL represents a technically novel accomplishment — a migration direction with no established automation path, completed entirely through expert engineering.
• Ongoing performance optimization. Performance testing is currently in progress. While architectural differences between DB2 and PostgreSQL initially required targeted code refactoring, these optimizations have successfully matched legacy performance levels. In several scenarios, the migrated system is now demonstrating measurable performance improvements over the original environment.

Customer feedback

"The DemandTec team views the PostgreSQL migration project as successful despite incomplete unit testing by the DE team, justified by a defect rate below 10% and collaborative resolution of all major conversion issues.

Ispirer is consistently praised for responsive, knowledgeable, and efficient support, with high and stable code quality maintained throughout the project (validated by unit test results). Most identified issues were fixed within one day, enabling smooth project integration. While specific technical challenges arose—including Temporary Table Naming Strategy, Handler Mapping, Dynamic Result Sets, Transaction Logic, Java return out parameters, and return cursors—all were discussed, finalized, and resolved collaboratively by both teams. The team also commended Ispirer's rapid issue remediation and efficient delivery of fixes during testing."

Planning a move from DB2 to the cloud?

Let Ispirer handle the heavy lifting. Book a demo today or try our toolkit for free and take the first step toward a faster, smarter, and more scalable data infrastructure.