Case study

EDI Sync

Two metadata-driven code generators — one emitting T-SQL, one emitting DB2 SQL — that kept a legacy EDI system in lockstep with a new Oracle JD Edwards environment, so a stalled EDI migration could no longer hold the ERP upgrade hostage.

PythonpandasSQL ServerT-SQLIBM DB2 for iDB2 SQLSSISJD Edwards

Procedures generated: 76
SQL dialects: 2
Time to build: 2 weeks
ERP upgrade delay: None

The problem

At Belwith, I was migrating our Oracle JD Edwards ERP from 8.12 to 9.2 and moving the database from IBM DB2 to SQL Server at the same time. The IBM server also hosted our EDI architecture, which was being migrated in a parallel project run by a colleague and an external consulting group.

The two systems were tightly integrated and had to move together to work. My ERP migration was on track. The EDI migration was not, and there was no reliable completion date — which left my project in limbo. I needed to decouple the two so the upgrade could move forward.

The solution

The EDI translator itself didn't need to migrate — only the data did. The translator could stay on the old server indefinitely as long as its data was replicated into the new one.

Writing that replication by hand was a non-starter:

Nobody in house fully understood the EDI system, so it wasn't obvious which tables actually mattered.
The field list differed between the old system and the new one.
The procedures had to exist on both machines. SSIS only moved data as far as a staging table; the load into production tables was a stored procedure that ran locally on whichever server it landed on.
That meant two SQL dialects. The merge and update logic that worked on SQL Server wasn't valid on DB2, and vice versa.
There was no time to hand-write, test, fix, and retest dozens of scripts — let alone two dialects' worth — before the cutover date.

So instead of writing the scripts, I wrote the things that write the scripts. In two weeks I built a pair of Python generators over one shared metadata definition: for each table in our EDI library I described the field name, datatype, whether it's a key, and whether it exists in 8.12 — and each generator turned that same description into a complete merge procedure in its own dialect.

The semantics are identical on both sides. Match on the key fields; update rows the translator hasn't already flagged as processed (EDSP <> 'Y'); insert rows that don't exist yet; supply type-appropriate defaults for columns the source doesn't have. The syntax is not. The two platforms disagree on how a procedure is declared and wrapped, how objects are qualified across databases and libraries, and what's legal inside the statement body — so the emitted text diverges substantially even though the intent is line-for-line the same.

Keeping the divergence in the generators rather than in the SQL is what made this tractable. A change to the sync logic — a new default, a corrected key — was one edit to the metadata or the emitter, then a regeneration of both sides. There was never a moment where the SQL Server procedures and the DB2 procedures could drift apart by hand.

That covered 38 tables in each dialect — 76 stored procedures in total. The split of responsibility was deliberate: SSIS (today this would be Fabric Data Factory or similar) handled only transport, landing rows in staging tables on both machines. From there, a generated procedure running locally on each server merged staging into the production tables. Nothing crossed the machine boundary except the raw rows, and all the reconciling logic — key matching, the processed-flag guard, the defaults — lived on the side that owned the destination table.

Architecture

EDI coexistence during the JDE 8.12 to 9.2 and iSeries to SQL Server cutover: trading partners feed the OpenText translator on the legacy IBM i, SSIS captures PRODDTA changes into EDISYNC staging tables, and generated merge procedures load them into PRODDTA on the new SQL Server, with a reverse outbound path.

The translator stayed on the iSeries; SSIS bridged the two ERPs on a schedule, and the generated procedures handled the staging → production merge.

The generators

Both generators read the same metadata and share the same shape: walk the tables, split each table's fields into keys, mapped non-keys, and unmatched non-keys, then emit a procedure. What differs is the text each one prints — the procedure declaration, the object qualification, and the statement structure are dialect-specific.

The T-SQL core loop, abridged:

import pandas

data = pandas.read_csv(r'.\TABLEDATA F47X.CSV')
data['alias'] = data['field'].str[-4:]

dbenv = 'JDE_PRODUCTION.PRODDTA'
tablelist = data.table.unique()

for table in tablelist:
    print(r'CREATE or ALTER PROCEDURE Update' + table +
          r' AS MERGE ' + dbenv + r'.' + table +
          r' t USING EDISYNC.dbo.' + table + r' s ON (')

    fieldlist = data.loc[(data['table'] == table)].field.unique().tolist()
    keyfieldlist = data.loc[(data['table'] == table) &
                            (data['iskey'] == 1)].field.unique().tolist()
    nonkeyfieldlist = data.loc[(data['table'] == table) &
                               (data['iskey'] == 0) &
                               (data['is812'] == 1)].field.unique().tolist()
    nonkeyfieldunmatchedlist = data.loc[(data['table'] == table) &
                                        (data['iskey'] == 0) &
                                        (data['is812'] == 0)].field.unique().tolist()
    processedfield = data.loc[(data['table'] == table) &
                              (data['alias'] == 'EDSP')].field.unique().tolist()[0]

    # join on the key fields
    keystring = ' and '.join('t.' + k + '=s.' + k for k in keyfieldlist)
    print(keystring)

    # only update rows the translator has not already flagged as processed
    print(') WHEN MATCHED AND t.' + processedfield + "<>'Y' THEN UPDATE SET ")

    # 8.12 has the column -> copy it; 9.2-only column -> default by datatype
    ...

View the full T-SQL generator script →

Generated output

One of the SQL Server procedures, trimmed for length — it merges the EDISYNC.dbo staging table into the production PRODDTA table on the same server. The DB2 generator produced its counterpart for the staging → production load on the iSeries. The T-SQL set alone ran to roughly 175,000 characters.

----------------------------------------------------------------
---F4706 BELJDEPS STORED PROCEDURE SOURCE FOR EDISYNC
----------------------------------------------------------------

CREATE or ALTER PROCEDURE UpdateF4706 AS MERGE JDE_PRODUCTION.PRODDTA.F4706 t USING EDISYNC.dbo.F4706 s ON (
t.ZAEKCO=s.ZAEKCO and t.ZAEDOC=s.ZAEDOC and t.ZAEDCT=s.ZAEDCT and t.ZAEDLN=s.ZAEDLN and t.ZAFILE=s.ZAFILE and t.ZAANTY=s.ZAANTY
) WHEN MATCHED AND t.ZAEDSP<>'Y' THEN UPDATE SET
t.ZAEDTY=s.ZAEDTY, t.ZAEDSQ=s.ZAEDSQ, t.ZAEDSP=s.ZAEDSP, t.ZAEDBT=s.ZAEDBT, t.ZADOCO=s.ZADOCO,
t.ZADCTO=s.ZADCTO, t.ZAKCOO=s.ZAKCOO, t.ZAAN8=s.ZAAN8, t.ZAMLNM=s.ZAMLNM, t.ZAADD1=s.ZAADD1,
/* ... 20 more mapped columns ... */ t.ZAGAN8=0
 WHEN NOT MATCHED THEN INSERT (
ZAEDTY, ZAEDSQ, ZAEKCO, ZAEDOC, ZAEDCT, ZAEDLN, ZAEDSP, ZAEDBT, ZAFILE, ZADOCO, /* ... */ ZAGAN8
) VALUES (
s.ZAEDTY, s.ZAEDSQ, s.ZAEKCO, s.ZAEDOC, s.ZAEDCT, s.ZAEDLN, s.ZAEDSP, s.ZAEDBT, s.ZAFILE, s.ZADOCO, /* ... */ 0
);

The result

The JD Edwards migration and upgrade proceeded on schedule.

Our EDI business — more than half of our sales orders, plus dock reports and inventory reports for our largest customers — kept processing on the legacy system, with data flowing into the new Oracle environment without issue.

The EDI project ran for more than another year after that. Without this bridge, we couldn't have delivered the stability and functionality gains of the Oracle upgrade to the business on any predictable timeline.

← Back to projects