CrewAI integration with IBM AS/400 DB2 using `ibm_db` (Python)

Deployment Tips

• Infrastructure: Deploy this MCP server on Railway
• Observability: Wrap this server with Helicone for production tracing.

Connecting CrewAI to IBM AS/400 (DB2)

Slug: crewai-ibm-as400-db2-python
Tags: CrewAI, IBM i, AS/400, DB2, Python, FastMCP, Legacy Integration

---

The “Green Screen” Knowledge Gap

For decades, the IBM AS/400 (now IBM i) has been the bedrock of supply chain, manufacturing, and banking logistics. While modern interfaces exist, the core data often lives in DB2 databases optimized for green-screen terminal access.

When building autonomous agents with CrewAI, you face a distinct challenge: how does a Python-based agent query a system designed in the 1980s?

This guide provides a production-ready Model Context Protocol (MCP) server that acts as a bridge. Using the official ibm_db driver, we expose safe, tool-based access to your AS/400 data, allowing agents to execute SQL queries, inspect schemas, and retrieve records without needing direct terminal emulation.

---

Prerequisites

1. IBM i Access: Hostname, Port (usually 446 or 50000), Username, and Password.
2. Python 3.10+: Required for FastMCP.
3. Docker: For containerized deployment.

---

1. The Bridge Code (server.py)

We use FastMCP to create a lightweight server. This server exposes a specific tool (query_as400) that your CrewAI agents can invoke.

Why ibm_db? It is the official Python driver for IBM DB2. It handles the specific DRDA protocol nuances required to talk to IBM i systems, which generic ODBC drivers often mishandle.

import os
import ibm_db
import json
from fastmcp import FastMCP

# Initialize the MCP Server
mcp = FastMCP("AS400-DB2-Bridge")

def get_db_connection():
    """
    Establishes a connection to the IBM i (AS/400) DB2 database.
    Relies on environment variables for credentials.
    """
    conn_str = (
        f"DATABASE={os.getenv('AS400_DATABASE', '*LOCAL')};"
        f"HOSTNAME={os.getenv('AS400_HOST')};"
        f"PORT={os.getenv('AS400_PORT', '446')};"
        f"PROTOCOL=TCPIP;"
        f"UID={os.getenv('AS400_USER')};"
        f"PWD={os.getenv('AS400_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}")

@mcp.tool()
def query_as400(sql_query: str) -> str:
    """
    Executes a read-only SQL query against the IBM i DB2 database.
    Useful for retrieving customer records, inventory levels, or order status.

    Args:
        sql_query: A valid SQL SELECT statement (e.g., "SELECT * FROM QGPL.CUSTOMERS FETCH FIRST 5 ROWS ONLY")
    """
    # Security: In production, add logic here to reject DROP, DELETE, or UPDATE commands
    if not sql_query.strip().upper().startswith("SELECT"):
        return "Error: Only SELECT statements are permitted by this agent tool."

    conn = None
    stmt = None
    results = []

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

        dictionary = ibm_db.fetch_assoc(stmt)
        while dictionary:
            # Clean up types for JSON serialization (e.g., decimals to floats/strings)
            clean_dict = {k: str(v) if v is not None else None for k, v in dictionary.items()}
            results.append(clean_dict)
            dictionary = ibm_db.fetch_assoc(stmt)

        return json.dumps(results, indent=2)

    except Exception as e:
        # Capture IBM specific driver errors
        db_error = ibm_db.stmt_errormsg() if stmt else ibm_db.conn_errormsg()
        return f"Database Error: {str(e)} - {db_error}"

    finally:
        if conn:
            ibm_db.close(conn)

if __name__ == "__main__":
    # FastMCP CLI handles 'sse' or 'stdio' modes based on arguments
    mcp.run()

---

2. Containerization (Dockerfile)

IBM’s ibm_db driver requires system-level C dependencies (XML libraries) to compile or link correctly on Linux. We use a slim Python image and install the necessary build tools.

Important: We expose port 8000 to allow CrewAI to communicate via Server-Sent Events (SSE) when deployed on platforms like Railway or AWS ECS.

# Use an official Python runtime as a parent image
FROM python:3.11-slim-bookworm

# Set work directory
WORKDIR /app

# Install system dependencies required for ibm_db
# libxml2 is often required by the underlying CLI drivers
RUN apt-get update && apt-get install -y \
    gcc \
    libc6-dev \
    libxml2 \
    && rm -rf /var/lib/apt/lists/*

# Install Python dependencies
# fastmcp for the server, ibm_db for the database connection
RUN pip install --no-cache-dir fastmcp ibm_db

# Copy the server code
COPY server.py .

# Expose the port for SSE (Server-Sent Events)
EXPOSE 8000

# Run the MCP server in SSE mode
# 0.0.0.0 is required for external access in Docker
CMD ["python", "server.py", "sse", "--host", "0.0.0.0", "--port", "8000"]

---

3. CrewAI Configuration

Once your Docker container is running (e.g., at http://localhost:8000 or a remote URL), you can connect your CrewAI agent to it using the MCPServer configuration.

Example agents.yaml or Python Config

from crewai import Agent, Task, Crew
from crewai_tools import MCPServerTool

# Connect to the AS/400 MCP Bridge
as400_tool = MCPServerTool(
    server_url="http://localhost:8000/sse",
    collection_name="as400_tools"
)

supply_chain_agent = Agent(
    role='Logistics Coordinator',
    goal='Check inventory levels in the legacy IBM system',
    backstory='You are an expert at navigating legacy green-screen data to find parts availability.',
    tools=[as400_tool],
    verbose=True
)

task = Task(
    description='Find the current stock level for Part Number "XJ-900" in the MASTER_INVENTORY table.',
    expected_output='The current stock quantity.',
    agent=supply_chain_agent
)

# Execution
crew = Crew(agents=[supply_chain_agent], tasks=[task])
result = crew.kickoff()

---

4. Troubleshooting & Common Errors

Error Code	Context	Solution
SQL0204	Object not found	The table might be in a specific library. Try SELECT * FROM LIBRARYNAME.TABLENAME instead of just the table name.
SQL0805	Package not found	This often indicates a binding issue. Ensure the user has BINDADD authority or that the driver has auto-bound the packages.
08001 / -30081	Connection Refused	Check firewall rules for port 446 (TCP/IP) or 50000 (DRDA). Ensure the subsystem QSERVER or QRWTSRVR is active on the AS/400.

Environment Variables

Ensure these are set in your Docker container or .env file:

• AS400_HOST: IP address or domain of the IBM i.
• AS400_USER: User profile name.
• AS400_PASSWORD: User password.
• AS400_DATABASE: Usually defaults to system name or *LOCAL.

---

🛡️ Quality Assurance

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

Transparency: This page may contain affiliate links.