IBM i

IBM i 

A space for professionals working with IBM’s integrated OS for Power systems to exchange ideas, ask questions, and share expertise on topics like RPG and COBOL development, application modernization, open source integration, system administration, and business continuity.


#Power


#IBMi
#Power
#Operatingsystems
#Servers
 View Only

Asking Db2 for i questions from an AI assistant, read-only

By Roni Ström posted 09/27/26 02:32 AM

  
The Claude sidebar in Excel fills a sheet with the work management services from QSYS2.SERVICES_INFO through mcp-server-db2i.

Claude for Excel filling a sheet from QSYS2.SERVICES_INFO through the MCP server.

A lot of IBM i data ends up in Excel. Someone runs a query over ODBC, pastes the result into a workbook, and sorts and filters from there. The screenshot above shows the same thing, one step shorter. I typed a question into the Claude sidebar in Excel, and the sheet filled with the work management services from QSYS2.SERVICES_INFO.

Between the sidebar and Db2 for i sits the Db2 for i MCP Server (mcp-server-db2i, MIT license). It's an open-source Model Context Protocol server I maintain, and it works with Claude, Cursor, VS Code, IBM Bob and other MCP clients. This post covers what it lets the assistant do, what it enforces, and the trade-offs I ran into along the way.

What it does

It gives the assistant tools for Db2 for i: list libraries (schemas) and tables, describe columns, indexes and constraints, search the catalog, and run a query. Ask "which customers ordered less this quarter than last?" and the assistant finds the tables, writes the SQL, runs it and explains the result.

Developers can use it from the editor they already work in. Add it to IBM Bob, or to Copilot or Claude Code in VS Code next to Code for i, and ask about a table while you're working on the program that uses it.

How it fits together

AI clients connect locally or over HTTPS with OAuth. The server checks every call, then reaches Db2 for i through ODBC, JT400 or Mapepire over SSH on a read-only connection.

AI clients reach the server locally or over HTTPS. The server checks every call and reaches Db2 for i through one of three drivers.

The server runs on a PC, a server or in a container, never on the IBM i itself. Local clients start it as a process. Remote clients such as claude.ai and Claude for Excel reach it over HTTPS. Every call passes the same checks before any SQL reaches the database. It connects in one of three ways:

  • IBM i Access ODBC Driver (the default). No Java needed. It uses the database host server your IBM i already runs.
  • JT400 JDBC, if you already run Java.
  • Mapepire over SSH, for systems where only SSH is reachable. There's no server install and no daemon. On the first connection, the Mapepire JAR (about 10 MB) is uploaded to the user's home directory and runs inside the SSH session, so the IBM i needs SSH access and Java. If Code for i has already put a Mapepire JAR in your home directory, that one is reused. The first query takes a few seconds while the JVM starts.

One server can reach several systems (production, test, development). Each has its own driver, credentials and allowlist.

Security: what is enforced where

This is the part IBM i people ask about first, so here it is in detail. The rule I built to: "the model promised not to" isn't a control. Everything below is enforced by the server or by the IBM i, never by the prompt.

Reads only, in three layers

  1. A read-only connection. ODBC connects with CONNTYPE=2 and JDBC with access=read only, so Db2 for i itself refuses writes.
  2. A SQL validator. Every statement is parsed before it reaches the database. Anything other than a query is rejected, including statements hidden in comments or in a second statement.
  3. A library allowlist. QUERY_ALLOWED_SCHEMAS limits which libraries the assistant can read, for example MYLIB and QSYS2.

If you want to start more carefully, turn off free-form SQL completely (MCP_TOOLS_DISABLED=execute_query). The assistant can then browse the catalog and run only the business SQL tools you wrote.

Everyone signs in as themselves

For a team, the server runs over HTTP with a built-in OAuth 2.1 sign-in. People add it as a connector in claude.ai or Claude for Excel and sign in with their own IBM i user profile. Queries run with that profile's object authority, so the IBM i security you already have still applies. Nobody shares a service account, and the audit log shows who ran what.

The built-in OAuth sign-in page. Each person signs in with their own IBM i user profile and chooses the system.

The built-in sign-in page. People sign in with their own IBM i user profile.

Where the data goes

The server connects only to your IBM i and doesn't copy data anywhere. But the rows a query returns go back to the AI client, and a hosted assistant sends them to its provider to process. So use an assistant your organization has approved for this data, and decide what can come back:

  • Column masking hides a column or shows only its last four characters.
  • Result limits: 1,000 rows by default, never more than 10,000.
  • The allowlist keeps whole libraries out of reach.

Exports are the exception. The file goes straight from your server to the user, and the model only sees the row count and a few sample rows.

Load on the IBM i

A statement that runs longer than the timeout (120 seconds by default) is cancelled on the IBM i. Requests are rate limited, and exports have their own row, size and concurrency limits. For production, keep those limits low.

Credentials, network and audit

  • Passwords come from environment variables or secret files, never from the AI client's config.
  • Remote clients reach the server over HTTPS, limited to the client's IP ranges. The database host server ports and SSH stay on your own network.
  • The Mapepire driver checks the IBM i's SSH host key before it sends a password.
  • The audit log writes one line per tool call: who, which tool, row count and duration. It stores a hash of the SQL, not the text, so the log doesn't become a second copy of your data.

One more honest point: text stored in the data can try to steer the model. Read-only access, the allowlist and your own authorities are what limit the damage if that happens.

Teaching it your data

Catalog metadata only gets the model part of the way. Column names like ORDERNO are clear. Status codes, flags and relations that nobody declared as foreign keys aren't. What helped most was simple:

  • Table notes. A few lines of YAML per table: what the status codes mean, which tables join and how, which column holds the real date. The model reads these before it writes SQL.
  • Business SQL tools. The queries you already trust (from Query/400, a workbook, a report) become named tools with parameters, so the assistant runs your SQL instead of writing its own. IBM's server is built around the same idea, and it's a good one.
version: 1
tools:
  - name: search_sales_orders
    description: Open sales orders for a customer, newest first.
    parameters:
      customer: { type: string, required: true, maxLength: 10 }
    sql: |
      SELECT ORDERNO, CUSTNO, ORDERDATE
      FROM MYLIB.ORDERHDR
      WHERE CUSTNO = :customer AND STATUS = 'O'
      ORDER BY ORDERDATE DESC
annotations:
  MYLIB.ORDERHDR:
    description: Sales order header
    columns:
      STATUS: "O = open, C = closed"

These add to your existing reports and warehouse. The server is for the one-off question that comes up before anyone has built a report for it.

Whole lists as files

A summary in the chat isn't enough when someone needs every row. The export tool writes a CSV or Excel file and gives back a short-lived download link. The assistant only sees the row count and a few sample rows. The link expires after 15 minutes or a few downloads.

How it compares to IBM's MCP server

IBM maintains its own open-source MCP server for IBM i (IBM/ibmi-mcp-server), and it's worth a look. Both are free. They start from different places:

  • IBM's server is a good fit if the Mapepire server already runs on your IBM i, or you're happy to install it. It comes with ready-made SQL tool sets for performance, jobs, security and storage, examples for building agents with Agno, LangChain or Google ADK, and an ibmi command-line tool.
  • This server is a good fit if you'd rather not install or run a server on the IBM i, you want reads enforced by the server itself, or several people will share one server and sign in with their own profiles.

Some teams may well use both.

Limits worth knowing

  • It doesn't replace governed financial reporting. Check the SQL behind any number that matters. The assistant shows it.
  • Someone on the IBM i team sets it up once: the allowlist, the table notes, the profiles.

Try it

npm install -g mcp-server-db2i

I'd like to hear from this community: which question about your IBM i data would you ask first? Replies here and issues on GitHub both shape what I build next.

0 comments
19 views

Permalink