mcp-server-db2i

A Model Context Protocol (MCP) server for IBM DB2 for i (DB2i). This server enables AI assistants like Claude and Cursor to query and inspect IBM i databases using the JT400 JDBC driver.

Features

• Read-only SQL queries - Execute SELECT statements safely with automatic result limiting
• Schema inspection - List all schemas/libraries with optional filtering
• Table metadata - List tables, describe columns, view indexes and constraints
• View inspection - List and explore database views
• Secure by design - Only SELECT queries allowed, credentials via environment variables
• Docker support - Run as a container for easy deployment

Available Tools

| Tool | Description | |------|-------------| | execute_query | Execute read-only SELECT queries | | list_schemas | List schemas/libraries (with optional filter) | | list_tables | List tables in a schema (with optional filter) | | describe_table | Get detailed column information | | list_views | List views in a schema (with optional filter) | | list_indexes | List SQL indexes for a table | | get_table_constraints | Get primary keys, foreign keys, unique constraints |

Note: list_indexes and get_table_constraints query the QSYS2 SQL catalog views and only return SQL-defined objects. Legacy DDS Logical Files and Physical File constraints are not included. This is standard DB2 for i behavior.

Filter Syntax

The list tools support pattern matching:

• CUST - Contains "CUST"
• CUST* - Starts with "CUST"
• *LOG - Ends with "LOG"
• ORD*FILE - Starts with "ORD", ends with "FILE"

Installation

Prerequisites

• Node.js 18 or higher
• Java Runtime Environment (JRE) 11 or higher (for JDBC)
• Access to an IBM i system

Option 1: npm (recommended)

npm install -g mcp-server-db2i

Option 2: From source

git clone https://github.com/Strom-Capital/mcp-server-db2i.git
cd mcp-server-db2i
npm install
npm run build

Option 3: Docker

docker build -t mcp-server-db2i .

Configuration

Create a .env file or set environment variables:

# Required
DB2I_HOSTNAME=your-ibm-i-host.com
DB2I_USERNAME=your-username
DB2I_PASSWORD=your-password

# Optional - Database
DB2I_PORT=446                              # Default: 446
DB2I_DATABASE=*LOCAL                       # Default: *LOCAL
DB2I_SCHEMA=your-default-schema            # Default schema for all tools (can be overridden per-call)
DB2I_JDBC_OPTIONS=naming=system;date format=iso

# Optional - Logging
LOG_LEVEL=info                             # debug, info, warn, error, fatal (default: info)
NODE_ENV=production                        # production = JSON logs, development = pretty logs
LOG_PRETTY=true                            # Override: force pretty (true) or JSON (false) logs
LOG_COLORS=true                            # Override: force colors on/off (auto-detected by default)

# Optional - Rate Limiting
RATE_LIMIT_WINDOW_MS=900000                # Time window in ms (default: 900000 = 15 minutes)
RATE_LIMIT_MAX_REQUESTS=100                # Max requests per window (default: 100)
RATE_LIMIT_ENABLED=true                    # Set to 'false' to disable (default: true)

# Optional - Query Limits
QUERY_DEFAULT_LIMIT=1000                   # Default rows returned (default: 1000)
QUERY_MAX_LIMIT=10000                      # Maximum rows allowed (default: 10000)

Environment Variables

| Variable | Required | Default | Description | |----------|----------|---------|-------------| | DB2I_HOSTNAME | Yes | - | IBM i hostname or IP address | | DB2I_USERNAME | Yes* | - | IBM i user profile | | DB2I_PASSWORD | Yes* | - | User password | | DB2I_USERNAME_FILE | No | - | Path to file containing username (overrides DB2I_USERNAME) | | DB2I_PASSWORD_FILE | No | - | Path to file containing password (overrides DB2I_PASSWORD) | | DB2I_PORT | No | 446 | JDBC port (446 is standard for IBM i) | | DB2I_DATABASE | No | *LOCAL | Database name | | DB2I_SCHEMA | No | - | Default schema/library for tools. If set, you don't need to specify schema in each tool call. | | DB2I_JDBC_OPTIONS | No | - | Additional JDBC options (semicolon-separated) | | LOG_LEVEL | No | info | Log level: debug, info, warn, error, fatal | | NODE_ENV | No | - | Set to production for JSON logs, otherwise pretty-printed | | LOG_PRETTY | No | - | Override log format: true = pretty, false = JSON | | LOG_COLORS | No | auto | Override colors: true/false (auto-detects TTY by default) | | RATE_LIMIT_WINDOW_MS | No | 900000 | Rate limit time window in milliseconds (15 min) | | RATE_LIMIT_MAX_REQUESTS | No | 100 | Maximum requests allowed per window | | RATE_LIMIT_ENABLED | No | true | Set to false or 0 to disable rate limiting | | QUERY_DEFAULT_LIMIT | No | 1000 | Default number of rows returned by queries | | QUERY_MAX_LIMIT | No | 10000 | Maximum rows allowed (caps user-provided limits) |

*Either the environment variable or the corresponding *_FILE variable must be set. File-based secrets take priority when both are provided.

JDBC Options

Common JDBC options for IBM i (JT400/JTOpen driver):

| Option | Values | Description | |--------|--------|-------------| | naming | system, sql | system uses / for library separator, sql uses . for schema separator | | libraries | LIB1,LIB2,... | Library list for resolving unqualified names | | date format | iso, usa, eur, jis, mdy, dmy, ymd | Date format for date fields | | time format | iso, usa, eur, jis, hms | Time format for time fields | | errors | full, basic | Level of detail in error messages (full helps debugging) | | translate binary | true, false | Whether to translate binary/CCSID data | | secure | true, false | Enable SSL/TLS encryption |

Example: naming=system;date format=iso;errors=full

Usage with Cursor

Add to your Cursor MCP settings (~/.cursor/mcp.json):

Using Docker (recommended)

{
  "mcpServers": {
    "db2i": {
      "command": "docker",
      "args": [
        "run", "-i", "--rm",
        "-e", "DB2I_HOSTNAME=your-host",
        "-e", "DB2I_USERNAME=your-user",
        "-e", "DB2I_PASSWORD=your-password",
        "mcp-server-db2i:latest"
      ]
    }
  }
}

Using Docker with env file

{
  "mcpServers": {
    "db2i": {
      "command": "docker",
      "args": [
        "run", "-i", "--rm",
        "--env-file", "/path/to/your/.env",
        "mcp-server-db2i:latest"
      ]
    }
  }
}

Using docker-compose

Create a .env file in the project root, then:

{
  "mcpServers": {
    "db2i": {
      "command": "docker-compose",
      "args": ["run", "--rm", "mcp-server-db2i"],
      "cwd": "/path/to/mcp-server-db2i"
    }
  }
}

The docker-compose.yml automatically reads from .env in the same directory.

Using npx (after npm install)

{
  "mcpServers": {
    "db2i": {
      "command": "npx",
      "args": ["mcp-server-db2i"],
      "env": {
        "DB2I_HOSTNAME": "your-host",
        "DB2I_USERNAME": "your-user",
        "DB2I_PASSWORD": "your-password"
      }
    }
  }
}

Local development

{
  "mcpServers": {
    "db2i": {
      "command": "npx",
      "args": ["tsx", "/path/to/mcp-server-db2i/src/index.ts"],
      "env": {
        "DB2I_HOSTNAME": "your-host",
        "DB2I_USERNAME": "your-user",
        "DB2I_PASSWORD": "your-password"
      }
    }
  }
}

Example Queries

Once connected, you can ask the AI assistant:

• "List all schemas that contain 'PROD'"
• "Show me the tables in schema MYLIB"
• "Describe the columns in MYLIB/CUSTOMERS"
• "What indexes exist on the ORDERS table?"
• "Run this query: SELECT * FROM MYLIB.CUSTOMERS WHERE STATUS = 'A'"

Development

# Install dependencies
npm install

# Run in development mode
npm run dev

# Build for production
npm run build

# Run production build
npm start

# Run tests
npm test

# Run tests in watch mode
npm run test:watch

# Lint code
npm run lint

# Lint and fix
npm run lint:fix

# Type check
npm run typecheck

Security

• Read-only access: Only SELECT statements are permitted
• No credentials in code: All sensitive data via environment variables or file-based secrets
• Query validation: AST-based SQL parsing plus regex validation blocks dangerous operations
• Result limiting: Default limit of 1000 rows, configurable max limit (default: 10000)
• Rate limiting: Configurable request throttling to prevent abuse (100 req/15 min default)
• Structured logging: Automatic redaction of sensitive fields like passwords

Rate Limiting

The server includes built-in rate limiting to protect the IBM i database from excessive queries:

• Default: 100 requests per 15-minute window
• Scope: Per server instance (for stdio transport, this effectively means per-client since each MCP client spawns its own server process)
• Configurable: Adjust via RATE_LIMIT_* environment variables or disable entirely

When the rate limit is exceeded, queries return an error with waitTimeSeconds indicating when to retry.

Credential Management

The server supports multiple methods for providing credentials, listed from most to least secure:

Option 1: Docker Secrets (Recommended for Production)

Docker secrets provide the most secure credential management. Secrets are mounted as files and never exposed in environment variables or process listings.

1. Create secret files:

mkdir -p ./secrets
echo "your-username" > ./secrets/db2i_username.txt
echo "your-password" > ./secrets/db2i_password.txt
chmod 600 ./secrets/*.txt

2. Configure docker-compose.yml to use secrets:

services:
  mcp-server-db2i:
    # ... other config ...
    environment:
      - DB2I_HOSTNAME=${DB2I_HOSTNAME}
      - DB2I_USERNAME_FILE=/run/secrets/db2i_username
      - DB2I_PASSWORD_FILE=/run/secrets/db2i_password
    secrets:
      - db2i_username
      - db2i_password

secrets:
  db2i_username:
    file: ./secrets/db2i_username.txt
  db2i_password:
    file: ./secrets/db2i_password.txt

For Docker Swarm or Kubernetes, use their native secret management instead of file-based secrets.

Option 2: External Secret Management

For enterprise deployments, integrate with secret management systems:

• HashiCorp Vault: Inject secrets at runtime
• AWS Secrets Manager: Use IAM roles for access
• Azure Key Vault: Integrate with managed identities

These systems can populate the *_FILE environment variables or inject secrets directly.

Option 3: Environment Variables (Development Only)

Plain environment variables are convenient for development but expose credentials through:

• docker inspect output
• Process listings (ps aux)
• Shell history
• Log files

# .env file (ensure it's in .gitignore)
DB2I_USERNAME=your-username
DB2I_PASSWORD=your-password

Warning: Never commit .env files or credentials to version control.

File-Based Secret Variables

| Variable | Description | |----------|-------------| | DB2I_USERNAME_FILE | Path to file containing username (takes priority over DB2I_USERNAME) | | DB2I_PASSWORD_FILE | Path to file containing password (takes priority over DB2I_PASSWORD) |

Compatibility

• IBM i V7R3 and later (V7R5 recommended)
• Works with any IBM i system accessible via JDBC over TCP/IP

Related Projects

• IBM ibmi-mcp-server - IBM's official MCP server for IBM i systems. Offers YAML-based SQL tool definitions, AI agent frameworks, and production deployment options. Requires Mapepire to be installed on your IBM i system, but if you can manage that prerequisite, it's worth checking out for more advanced use cases.

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

MIT License - see LICENSE for details.

Acknowledgments

• node-jt400 - JT400 JDBC driver wrapper for Node.js
• Model Context Protocol - The protocol specification
• @modelcontextprotocol/sdk - Official TypeScript SDK
• IBM ibmi-mcp-server - SQL security validation patterns inspired by their approach to AST-based query validation