Semantic Kernel with IBM AS/400 DB2 via `ibm_db` (Python)

As organizations adopt Microsoft Semantic Kernel to orchestrate complex AI workflows, they often hit a hard wall when trying to access data stored in IBM i (AS/400) systems. While Semantic Kernel excels at chaining prompts and functions, it has no native understanding of the proprietary DB2 protocols used by legacy “Big Iron.”

This guide provides a production-ready Model Context Protocol (MCP) server that bridges this gap. By deploying this lightweight container, you give your Semantic Kernel agents the ability to run SQL queries against IBM DB2 without needing to embed legacy drivers directly into your agent’s codebase.

🛠️ The Architecture

We will use the FastMCP framework to create a dedicated microservice. This service manages the heavy lifting of the ibm_db driver (which requires specific system libraries) and exposes clean, JSON-ready tools to your AI agents.

Why this approach?

Dependency Isolation: ibm_db relies on C-based extensions that can conflict with modern AI libraries. Running it in its own Docker container prevents “DLL hell.”
Protocol Abstraction: Your agent speaks standard MCP (JSON-RPC); the server speaks IBM DRDA (Distributed Relational Database Architecture).
Security: You can restrict the DB2 connection credentials to this specific container, rather than distributing them across every agent script.

🚀 Step 1: The Container Strategy (`Dockerfile`)

The most common failure point with ibm_db is the operating system environment. The driver expects specific glibc versions and XML libraries. We will use a Debian-based Python image to ensure compatibility.

File: Dockerfile

# Use a Debian-based slim image (Alpine often fails with IBM drivers)
FROM python:3.11-slim

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

# Set working directory
WORKDIR /app

# Install Python dependencies
# fastmcp: The MCP server framework
# ibm_db: The native IBM DB2 driver
RUN pip install --no-cache-dir fastmcp ibm_db

# Copy the application code
COPY server.py .

# Expose the standard port
EXPOSE 8000

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

💻 Step 2: The Glue Code (`server.py`)

This script establishes the connection to the AS/400 and exposes a query_db2 tool. Note that ibm_db returns results as tuples or dictionaries depending on the fetch mode; we force dictionary mode to make the data easy for the LLM to understand.

File: server.py

import os
import ibm_db
from fastmcp import FastMCP
from typing import List, Dict, Any

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

def get_db_connection():
    """
    Establishes a connection to the IBM i (AS/400) DB2 database.
    Retries or pooling could be added here for production.
    """
    db_name = os.getenv("DB2_NAME", "BLUEDB")
    hostname = os.getenv("DB2_HOST", "192.168.1.100")
    port = os.getenv("DB2_PORT", "50000")
    uid = os.getenv("DB2_USER", "QSECOFR")
    pwd = os.getenv("DB2_PASSWORD", "password")

    # Construct the standard IBM DB2 connection string
    conn_str = (
        f"DATABASE={db_name};"
        f"HOSTNAME={hostname};"
        f"PORT={port};"
        f"PROTOCOL=TCPIP;"
        f"UID={uid};"
        f"PWD={pwd};"
    )

    try:
        # ibm_db.connect creates a non-persistent connection
        conn = ibm_db.connect(conn_str, "", "")
        return conn
    except Exception as e:
        error_msg = ibm_db.conn_errormsg()
        raise RuntimeError(f"Failed to connect to DB2: {e} - {error_msg}")

@mcp.tool()
def query_db2(sql_query: str) -> List[Dict[str, Any]]:
    """
    Executes a read-only SQL query against the AS/400 DB2 database.
    Use this to fetch inventory, order status, or customer details.

    Args:
        sql_query: The SQL statement to execute (e.g., "SELECT * FROM LIB.TABLE FETCH FIRST 5 ROWS ONLY")
    """
    conn = None
    stmt = None
    results = []

    try:
        conn = get_db_connection()

        # Prepare and execute the query
        stmt = ibm_db.exec_immediate(conn, sql_query)

        # Fetch results as a dictionary (assoc)
        dictionary = ibm_db.fetch_assoc(stmt)
        while dictionary:
            # Clean up keys if necessary (trim whitespace common in legacy DBs)
            clean_dict = {k: v.strip() if isinstance(v, str) else v for k, v in dictionary.items()}
            results.append(clean_dict)
            dictionary = ibm_db.fetch_assoc(stmt)

        return results

    except Exception as e:
        # Capture specific IBM driver errors
        error_msg = ibm_db.stmt_errormsg() if stmt else ibm_db.conn_errormsg()
        return [{"error": f"DB2 Execution Failed: {str(e)}", "details": str(error_msg)}]

    finally:
        # Always close resources to prevent locking on the AS/400
        if conn:
            ibm_db.close(conn)

if __name__ == "__main__":
    mcp.run()

🔌 Integration with Semantic Kernel

Once the Docker container is running, your Semantic Kernel agent connects to it via the standard MCP client protocol.

1. Run the Docker Container

docker build -t as400-mcp .
docker run -p 8000:8000 \
  -e DB2_HOST=as400.corp.local \
  -e DB2_USER=MYAGENT \
  -e DB2_PASSWORD=securepass \
  as400-mcp

2. Configure Your Agent

When initializing your Semantic Kernel agent, register the MCP client pointing to http://localhost:8000/sse. The agent will automatically discover the query_db2 tool.

Prompting Example:

“Check the inventory level for part number XJ-900 in the IMMASTER table on the AS/400.”

The agent will translate this into:

SELECT ONHAND, ALLOCATED FROM INVLIB.IMMASTER WHERE PARTNO = 'XJ-900'

And execute it via the tool.

⚠️ Common IBM DB2 Issues

Error Code	Meaning	Fix
SQL30081N	Communication Error	Check if port `50000` (standard DRDA) or `446` is open. Ensure the `PROTOCOL=TCPIP` is set in connection string.
CLI0112E	Assignment Error	Often happens when fetching huge BLOBs or Clob data without proper buffer size. Limit your queries to standard columns.
[IBM][CLI Driver] SQL0551N	Privilege Error	The user `DB2_USER` does not have `SELECT` authority on the specific library/table. Ask your AS/400 Admin to grant object authority.
Encoding/EBCDIC	Garbled Text	`ibm_db` usually handles EBCDIC to ASCII conversion automatically. If text looks wrong, check the CCSID setting on the user profile in the AS/400.

🛡️ Quality Assurance

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

Transparency: This page may contain affiliate links.

Semantic Kernel with IBM AS/400 DB2 via `ibm_db` (Python)

Semantic Kernel with IBM AS/400 DB2 via ibm_db (Python)

🛠️ The Architecture

Why this approach?

🚀 Step 1: The Container Strategy (Dockerfile)

💻 Step 2: The Glue Code (server.py)

🔌 Integration with Semantic Kernel

1. Run the Docker Container

2. Configure Your Agent

⚠️ Common IBM DB2 Issues

🛡️ Quality Assurance

Semantic Kernel with IBM AS/400 DB2 via `ibm_db` (Python)

🚀 Step 1: The Container Strategy (`Dockerfile`)

💻 Step 2: The Glue Code (`server.py`)