Use Python and SQL to Active Sync Legacy IBM Data with NoSQL Databases

An order enters an IBM-backed system, but the web application still shows yesterday’s status. A nightly export eventually closes the gap, until someone needs current inventory, a faster customer portal, or an API that should not query the transaction system on every request.

The answer does not have to be a full database migration. A small synchronization service can keep the IBM system authoritative while maintaining a read-oriented copy in MongoDB or Redis. Python connects the pieces; SQL selects the records and tracks delivery.

The difficult part is not inserting a document. It is making sure updates and deletions arrive, retries do not corrupt data, and a restart does not silently lose changes. This tutorial builds an active-sync design around those requirements.

This guide uses tools and approaches available in 2019; driver installation, interfaces, and product capabilities may have changed since then.

First, identify the IBM data source

“Legacy IBM data” describes several different environments, not one database interface.

IBM i commonly stores business data in Db2 for i, which can be accessed with SQL through an appropriate ODBC driver.
IMS is a hierarchical database and transaction-management environment associated with IBM mainframes.
VSAM provides record-oriented datasets, not a general-purpose SQL database.
TPF is a transaction-processing platform, not another name for Db2.

These technologies are not interchangeable, nor does their age mean they are unsupported. SQLAlchemy cannot automatically turn an IMS database or a VSAM file into a relational source. Those environments require an appropriate connector, application interface, replication product, or staged relational representation.

The implementation below assumes Db2 for i exposed through ODBC. For another IBM source, retain the delivery design but replace the extraction mechanism.

What active synchronization should deliver

Here, active sync means continuous, one-way propagation with a short polling interval, not instantaneous replication and not bidirectional conflict resolution.

The architecture is:

IBM application transaction
    ├── changes a business record
    └── inserts an outbox event
                  │
             Python worker
                  │
          MongoDB read model
                  │
        acknowledge source event

MongoDB is useful when applications need queryable documents with durable storage. Redis is useful when the destination is a cache or a narrowly designed low-latency read model. Neither automatically makes every workload faster, and both introduce operational responsibilities.

Keep writes to the business entity on the IBM side. Let the synchronized database serve explicitly eventual-consistency-tolerant reads. For workflows that require the latest committed value, query the authoritative system or use a separate confirmation path.

1. Define the synchronization contract

Before installing packages, agree on five things:

Identity: Which immutable source key identifies each destination document?
Scope: Which entities and fields may leave the source system?
Change capture: How are inserts, updates, and deletes recorded?
Ordering: What prevents an older update from replacing a newer one?
Recovery: How will you rebuild or reconcile the destination?

Use a stable identifier such as customer:1042, rather than an array position or a mutable email address. If identifiers are unique only within a company, include the company identifier too.

Define field mappings explicitly. Preserve account numbers as strings when leading zeros matter. Specify timezone handling, character conversion, null behavior, and whether fixed-width character fields should be trimmed. For money, choose a decimal-safe representation rather than silently converting to binary floating point.

2. Capture changes transactionally

Polling a business table by updated_at looks simple, but it misses hard deletes and can lose updates around timestamp boundaries. An incrementing identifier is also not automatically a safe checkpoint: identifier allocation order is not necessarily transaction commit order.

A transactional outbox provides a clearer delivery contract. The application changes its business row and inserts an event in the same Db2 transaction. A database trigger can be an alternative when application changes are impractical, but it needs equivalent payload, transaction, and performance testing.

A minimal illustrative table is:

CREATE TABLE APP.SYNC_OUTBOX (
    EVENT_ID BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ENTITY_KEY VARCHAR(128) NOT NULL,
    OPERATION CHAR(1) NOT NULL,
    PAYLOAD CLOB(1M),
    CREATED_AT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    DELIVERED_AT TIMESTAMP,
    CONSTRAINT SYNC_OP CHECK (OPERATION IN ('U', 'D'))
);

CREATE INDEX APP.SYNC_PENDING
    ON APP.SYNC_OUTBOX (DELIVERED_AT, EVENT_ID);

An event uses U for a complete replacement document, covering both creation and update, or D for deletion. Store a JSON snapshot in PAYLOAD; a delete needs only its entity key.

For example, this application operation must be atomic:

UPDATE APP.CUSTOMER
SET DISPLAY_NAME = ?
WHERE CUSTOMER_ID = ?;

INSERT INTO APP.SYNC_OUTBOX
    (ENTITY_KEY, OPERATION, PAYLOAD)
VALUES (?, 'U', ?);

COMMIT;

Disable autocommit in the application transaction that performs these statements. Every writer, including batch jobs, must participate in capture.

Establish per-entity ordering

The example worker assumes one consumer and source mutations serialized per entity through transaction-held locks. Create each outbox event while holding that serialization lock, and retain the lock through commit. Delete-and-recreate workflows need the same protection, potentially through a persistent key-lock record.

Without this guarantee, event IDs alone cannot establish business order. Use source revisions and destination-side conditional writes, or a supported change-data-capture stream with defined ordering semantics.

For a production IBM i installation, journal-based CDC may be preferable to application-managed capture. Evaluate it when you cannot change every writer or need lower source overhead.

3. Prepare Python and the destination

Install Python 3, an IBM i-compatible ODBC driver, and client libraries available in your deployment environment:

python -m pip install pyodbc pymongo

Use a tested, pinned dependency set for deployment. Configure an ODBC data source named IBM_I_SOURCE; do not assume that installing pyodbc installs the IBM driver.

The examples read credentials from environment variables:

IBM_I_USER
IBM_I_PASSWORD
MONGO_URI

Provision accounts with narrow permissions. The worker needs to read pending events and update their delivery status, not administer business tables. Configure encrypted connections and certificate validation on both sides; an environment variable does not itself secure network traffic.

SQLAlchemy can help when an application already uses a compatible Db2 dialect. Direct ODBC is sufficient here and keeps driver-specific assumptions visible.

4. Load a consistent baseline

Continuous changes do not replace the initial load. Existing records need a destination representation before synchronization can be considered complete.

For a manageable dataset, use a maintenance window:

Pause all relevant business writes and allow active transactions to finish.
Install or enable transactional capture without resuming writers.
Stream the current business records into an empty destination collection.
Validate counts and sampled records.
Resume writes and start the outbox worker.

Read the baseline with fetchmany() rather than fetchall(), and apply the same mapping used for event payloads. Use bounded MongoDB bulk writes for efficiency.

Do not clear pending events indiscriminately on a restart. Track which bootstrap procedure completed and whether the destination is safe to serve.

For large systems that cannot pause writes, use a consistent snapshot paired with a known CDC position. A long-running ordinary query plus an arbitrary timestamp is not an equivalent substitute.

5. Apply changes and acknowledge only after success

This worker intentionally uses synchronous I/O. Both pyodbc and ordinary PyMongo calls are blocking; wrapping them in async def would not make them asynchronous.

import json
import logging
import os
import time

import pyodbc
from pymongo import MongoClient
from pymongo.write_concern import WriteConcern

logging.basicConfig(level=logging.INFO)

source = pyodbc.connect(
    'DSN=IBM_I_SOURCE;',
    user=os.environ['IBM_I_USER'],
    password=os.environ['IBM_I_PASSWORD'],
    autocommit=True,
)

mongo = MongoClient(os.environ['MONGO_URI'])
customers = mongo['read_models'].get_collection(
    'customers',
    write_concern=WriteConcern(w='majority', j=True),
)


def pending_events():
    cursor = source.cursor()
    try:
        cursor.execute('''
            SELECT EVENT_ID, ENTITY_KEY, OPERATION, PAYLOAD
            FROM APP.SYNC_OUTBOX
            WHERE DELIVERED_AT IS NULL
            ORDER BY EVENT_ID
            FETCH FIRST 200 ROWS ONLY
        ''')
        return cursor.fetchall()
    finally:
        cursor.close()


def deliver(event):
    event_id, entity_key, operation, payload = event

    if operation == 'D':
        customers.delete_one({'_id': entity_key})
    elif operation == 'U':
        document = json.loads(payload)
        document['_id'] = entity_key
        customers.replace_one(
            {'_id': entity_key}, document, upsert=True
        )
    else:
        raise ValueError('Unsupported operation')

    cursor = source.cursor()
    try:
        cursor.execute('''
            UPDATE APP.SYNC_OUTBOX
            SET DELIVERED_AT = CURRENT_TIMESTAMP
            WHERE EVENT_ID = ? AND DELIVERED_AT IS NULL
        ''', event_id)
    finally:
        cursor.close()


while True:
    try:
        events = pending_events()
        for event in events:
            deliver(event)
        if not events:
            time.sleep(1)
    except Exception:
        logging.exception('Sync stopped; unacknowledged event retained')
        raise

Run this as a single supervised process, with delayed restarts and alerts. Do not start overlapping instances. Configure connection and operation timeouts appropriate to the installed drivers.

Why retries are safe here

The destination write happens before source acknowledgment. If the process crashes between them, the event is delivered again. Replacing a document with the same snapshot or deleting an already absent document is idempotent.

This is at-least-once delivery, not a distributed exactly-once transaction. Safety depends on the ordering and single-worker assumptions above. The worker stops on failure instead of skipping a bad event and allowing later events to overtake it.

Acknowledged MongoDB writes also need an appropriately configured deployment. Write concern cannot compensate for an untested backup or recovery plan.

6. Adapt the destination to Redis when appropriate

For Redis, store a complete JSON document under a stable key:

key = 'readmodel:' + entity_key
if operation == 'D':
    redis_client.delete(key)
else:
    redis_client.set(key, payload)

These operations are individually atomic and repeatable, but acknowledgment does not necessarily mean durable replication. Choose persistence, replication, eviction, and recovery settings deliberately.

If Redis is disposable cache storage, keep a tested rebuild path. Do not automatically expire synchronized keys unless cache misses can fetch authoritative data; an unchanged record will not generate an event to restore an expired key.

Operational pitfalls to address before launch

Unbounded outbox growth: Archive or delete acknowledged events under a documented retention policy. Never purge undelivered events merely because they are old.
Poison records: Alert on malformed payloads and mapping failures. Repair or quarantine through an explicit procedure rather than silently skipping them.
Source load: Poll in bounded batches, inspect query plans, and measure transaction overhead from capture.
Sensitive data leakage: Allowlist exported fields and keep payloads out of routine logs.
Silent divergence: Schedule reconciliation using keys, counts, and selected field comparisons. Counts alone cannot prove correctness.
Unsafe parallelism: Scaling requires per-key ordering, worker ownership, and replay-safe version handling, not simply more processes.

Monitor pending-event count, age of the oldest pending event, delivery errors, and reconciliation mismatches. Test destination outages, worker crashes after writes, source reconnects, and delete/recreate sequences before trusting the pipeline.

Takeaway

Python and SQL can turn Db2 for i data into a continuously refreshed NoSQL read model without replacing the system of record. The reliable design starts with transactional capture, stable identities, explicit ordering, and replay-safe delivery. Once those foundations work, optimize throughput, and keep reconciliation as part of normal operations.