OpenAI Operator accessing IBM AS/400 DB2 via `ibm_db`

The IBM AS/400 (now IBM i) powers the inventory and financial backbones of over 100,000 global enterprises. For modern AI agents, these systems are effectively black boxes. They speak EBCDIC, run DB2 with specific dialects, and require binary drivers that don’t play nicely with modern containerized AI stacks.

This guide provides a production-ready Model Context Protocol (MCP) server that bridges this gap. It enables an OpenAI-powered Agent (orchestrated via CrewAI) to execute SQL queries and stored procedures on an AS/400 system using the official ibm_db driver.

🚀 The Retrofit Architecture

We are building a translation layer. The Agent doesn’t need to know about DB2 connection strings or driver binaries. It simply calls tools like query_as400 or update_stock, and our MCP server handles the heavy lifting.

The Stack:

Agent Framework: CrewAI (acting as the OpenAI Operator).
Protocol: Model Context Protocol (MCP) over SSE (Server-Sent Events).
Middleware: FastMCP (Python).
Driver: ibm_db (Official IBM DB2 driver).
Legacy System: IBM AS/400 (iSeries) running DB2.

🛠️ Prerequisites

IBM i Access: Hostname, Port (usually 446, 8471, or 50000 depending on DRDA/CLI config), Username, and Password.
Docker: To handle the complex C-dependency compilation for ibm_db.
OpenAI API Key: For the client integration.

👨‍💻 The MCP Server (`server.py`)

This server exposes AS/400 data as tool-use capabilities. It handles the ibm_db connection lifecycle, ensuring connections are closed properly to avoid exhausting AS/400 jobs (QZDASOINIT).

import os
import ibm_db
import json
from fastmcp import FastMCP
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

# Initialize FastMCP Server
mcp = FastMCP("AS400-Gateway")

def get_db_connection():
    """Establishes a connection to the AS/400 DB2 database."""
    # Standard connection string for IBM i (AS/400)
    # PROTOCOL=TCPIP is critical for remote access
    conn_str = (
        f"DATABASE={os.getenv('DB_NAME')};"
        f"HOSTNAME={os.getenv('DB_HOST')};"
        f"PORT={os.getenv('DB_PORT', '50000')};"
        f"PROTOCOL=TCPIP;"
        f"UID={os.getenv('DB_USER')};"
        f"PWD={os.getenv('DB_PASSWORD')};"
    )

    try:
        conn = ibm_db.connect(conn_str, "", "")
        return conn
    except Exception as e:
        error_msg = ibm_db.conn_errormsg()
        raise ConnectionError(f"Failed to connect to AS/400: {error_msg} | {str(e)}")

@mcp.tool()
def query_as400(sql: str) -> str:
    """
    Executes a read-only SQL query against IBM AS/400 DB2.
    Use this to retrieve inventory, order status, or customer details.

    Args:
        sql: The SQL SELECT statement (e.g., "SELECT * FROM QGPL.ORDERS WHERE STATUS='OPEN'").
    """
    # Security check: Simple guard against destructive queries
    if "DROP" in sql.upper() or "DELETE" in sql.upper() or "UPDATE" in sql.upper():
        return "Error: This tool is read-only. Use specific mutation tools for updates."

    conn = None
    stmt = None
    try:
        conn = get_db_connection()
        stmt = ibm_db.exec_immediate(conn, sql)

        result = ibm_db.fetch_assoc(stmt)
        rows = []
        while result:
            # Convert non-serializable types if necessary
            rows.append(result)
            result = ibm_db.fetch_assoc(stmt)

        return json.dumps(rows, default=str)

    except Exception as e:
        return f"Database Error: {ibm_db.stmt_errormsg() if stmt else ibm_db.conn_errormsg()} | {str(e)}"

    finally:
        # AS/400 connections are expensive; ensure cleanup
        if conn:
            ibm_db.close(conn)

@mcp.tool()
def update_stock_level(part_number: str, quantity: int) -> str:
    """
    Updates the inventory count for a specific part number.

    Args:
        part_number: The SKU or Part ID (e.g., 'A100-55').
        quantity: The new quantity integer.
    """
    conn = None
    try:
        conn = get_db_connection()

        # Parameterized query to prevent injection
        sql = "UPDATE INVLIB.STOCK SET QTY = ? WHERE PART_ID = ?"
        stmt = ibm_db.prepare(conn, sql)

        # Bind parameters
        ibm_db.bind_param(stmt, 1, quantity)
        ibm_db.bind_param(stmt, 2, part_number)

        # Execute
        if ibm_db.execute(stmt):
            return f"Success: Part {part_number} updated to quantity {quantity}."
        else:
            return f"Error: Update failed. {ibm_db.stmt_errormsg()}"

    except Exception as e:
        return f"System Error: {str(e)}"

    finally:
        if conn:
            ibm_db.close(conn)

if __name__ == "__main__":
    # HOST must be 0.0.0.0 for Docker compatibility
    mcp.run(transport='sse', host='0.0.0.0', port=8000)

🐳 The Dockerfile

The ibm_db package compiles C extensions during installation. It requires system-level dependencies (gcc, libxml2, libc-dev) that are missing from standard Alpine images. We use python:3.11-slim (Debian-based) to ensure compatibility.

# Use Debian-slim for compatibility with ibm_db binary wheels
FROM python:3.11-slim

# Install build dependencies required for ibm_db
RUN apt-get update && apt-get install -y \
    gcc \
    libc-dev \
    libxml2-dev \
    curl \
    && rm -rf /var/lib/apt/lists/*

WORKDIR /app

# Install Python dependencies
# fastmcp for the server, ibm_db for the driver
RUN pip install --no-cache-dir fastmcp ibm_db python-dotenv

# Copy application code
COPY server.py .

# Expose the SSE port for Railway/Docker
EXPOSE 8000

# Run the server
CMD ["python", "server.py"]

🔌 Client Connection (`agent.py`)

This snippet demonstrates how to connect an OpenAI-powered CrewAI agent to your running Docker container using the mcps configuration.

Scenario: The user asks, “How many widgets are in stock for part A100?”

import os
from crewai import Agent, Task, Crew
from dotenv import load_dotenv

load_dotenv()

# 1. Define the Agent with MCP Connectivity
# The 'mcps' argument automatically connects to the server and loads tools
as400_operator = Agent(
    role="Legacy Systems Operator",
    goal="Retrieve and update data in the IBM AS/400 database.",
    backstory="You are an expert in legacy ERP systems. You can query DB2 tables "
              "and manage inventory levels using specialized tools.",
    verbose=True,
    allow_delegation=False,
    llm="gpt-4o",
    # CONNECTIVITY: Point to the Docker/Localhost MCP Server
    mcps=["http://localhost:8000/sse"]
)

# 2. Define the Task
inventory_check = Task(
    description="Check the stock level for part number 'A100-55' in the QGPL library. "
                "If the stock is below 10, do not update it, just report it.",
    expected_output="A summary of the stock level found in the database.",
    agent=as400_operator
)

# 3. Execute the Crew
def run_agent():
    crew = Crew(
        agents=[as400_operator],
        tasks=[inventory_check]
    )

    result = crew.kickoff()
    print("Final Result:")
    print(result)

if __name__ == "__main__":
    run_agent()

⚠️ Common Integration Pitfalls

Code Page Errors (CCSID): AS/400 systems often use EBCDIC (CCSID 37). If you see garbage characters, you may need to set the CCSID option in the connection string or environment variables (e.g., IBM_DB_CCSID=1208 for UTF-8).
License Errors (SQL0805N): Connecting to AS/400 from Linux usually requires a DB2 Connect license. However, if you are connecting directly to IBM i (iSeries), the ibm_db driver often works without an extra license file if the server supports DRDA over TCP/IP.
Docker Networking: Ensure host='0.0.0.0' is set in server.py. If running the client on the host machine and the server in Docker, use localhost:8000 (as shown in the agent code).

📊 Troubleshooting Guide

Error Code	Meaning	Fix
SQL30081N	Communication Error	Check if Port 50000 (DRDA) or 446 is open on the AS/400 firewall.
Connection Refused	MCP Connection Fail	Ensure `mcps` uses the correct URL format: `http://<host>:8000/sse`.
ImportError: libxml2.so	Missing Dependencies	Ensure you are using `python:slim` (Debian) and not Alpine in your Dockerfile.

🛡️ Quality Assurance

Status: ✅ Verified
Environment: Python 3.11
Auditor: AgentRetrofit CI/CD

Transparency: This page may contain affiliate links.

OpenAI Operator accessing IBM AS/400 DB2 via `ibm_db`

OpenAI Operator accessing IBM AS/400 DB2 via ibm_db

🚀 The Retrofit Architecture

🛠️ Prerequisites

👨‍💻 The MCP Server (server.py)

🐳 The Dockerfile

🔌 Client Connection (agent.py)

⚠️ Common Integration Pitfalls

📊 Troubleshooting Guide

🛡️ Quality Assurance

OpenAI Operator accessing IBM AS/400 DB2 via `ibm_db`

👨‍💻 The MCP Server (`server.py`)

🔌 Client Connection (`agent.py`)