SQL Tools Overview

Learn how YAML-defined SQL tools enable rapid development of IBM i database operations without writing TypeScript code.

The IBM i MCP Server’s SQL Tools system allows you to create powerful database operations using simple YAML configurations. Define SQL queries in YAML files, and the server automatically converts them into AI-accessible tools with parameter validation, security controls, and response formatting.

No TypeScript required - SQL professionals can build sophisticated IBM i tools using familiar SQL syntax and simple YAML structure.

---

Pre-Built Tool Categories

The tools/ directory contains pre-built tool configurations organized by category:

Directory	Category	Description	Key Tools
sample/	Sample Data	Demonstration tools using IBM i SAMPLE schema (employee, department, project data)	Employee lookup, department analysis, project management
sys-admin/	System Administration	High-level system service discovery and metadata exploration	Service catalogs, schema browsing, example queries
security/	Security Analysis	Library list security assessment and vulnerability detection	Library list configuration, authority checks, security analysis
performance/	Performance Monitoring	System performance metrics and resource utilization	System status, active jobs, memory pools, HTTP server stats
developer/	Development Tools	Object statistics and dependency analysis for developers	Recently used objects, stale object detection, dependency tracking

List Available Toolsets:

npx -y @ibm/ibmi-mcp-server@latest --list-toolsets --tools tools

---

YAML File Format

SQL tools are defined in YAML files with three main sections:

Sources

Database connections

Define IBM i system connections with credentials, ports, and connection options

Sources Reference →

Tools

SQL operations

Individual queries with parameters, validation, and security controls

Tools Reference →

Toolsets

Logical groups

Organize related tools for discovery and selective loading

Toolsets Reference →

Basic Structure

# 1. Sources - Database connections
sources:
  ibmi-system:
    host: ${DB2i_HOST}
    user: ${DB2i_USER}
    password: ${DB2i_PASS}
    port: 8076

# 2. Tools - SQL operations
tools:
  system_status:
    source: ibmi-system
    description: "Get system performance metrics"
    statement: |
      SELECT * FROM TABLE(QSYS2.SYSTEM_STATUS()) X
    parameters: []

# 3. Toolsets - Logical groupings
toolsets:
  performance_monitoring:
    title: "Performance Monitoring"
    description: "System performance and resource tools"
    tools: [system_status]

Why YAML? Separating SQL definitions from application code enables SQL professionals to build and maintain tools without TypeScript knowledge while ensuring security through parameter validation and prepared statements.

Key Concepts

Sources - Database Connections

Define reusable connection configurations with environment variables for security:

sources:
  ibmi-system:
    host: ${DB2i_HOST}
    user: ${DB2i_USER}
    password: ${DB2i_PASS}
    port: 8076

Key features:

• Environment variable substitution for credentials
• Multiple environment support (dev, test, prod)
• Connection pooling and timeouts
• SSL/TLS configuration

Complete Sources Reference

Learn about all source configuration options, security best practices, and troubleshooting →

---

Tools - SQL Operations

Individual SQL queries with parameter validation and security controls:

tools:
  active_job_info:
    source: ibmi-system
    description: "Find top CPU consumers"
    parameters:
      - name: limit
        type: integer
        default: 10
        minimum: 1
        maximum: 100
    statement: |
      SELECT CPU_TIME, JOB_NAME FROM
      TABLE(QSYS2.ACTIVE_JOB_INFO()) A
      ORDER BY CPU_TIME DESC
      FETCH FIRST :limit ROWS ONLY

Parameter types:

• String - Pattern validation, length constraints, enums
• Integer - Range validation, defaults
• Float - Decimal numbers for calculations
• Boolean - True/false flags
• Array - Lists of values for IN clauses

Complete Tools Reference

Explore all tool configuration options, parameter types, security features, and examples →

---

Toolsets - Logical Organization

Group related tools for discovery and selective loading:

toolsets:
  performance_monitoring:
    title: "Performance Monitoring"
    description: "System performance and resource tools"
    tools:
      - system_status
      - active_job_info
      - memory_pools

Organization strategies:

• By functional domain (monitoring, security, reporting)
• By business process (onboarding, payroll, inventory)
• By user role (developer, DBA, auditor)
• By environment (production, development, testing)

Complete Toolsets Reference

Learn about toolset organization, selective loading, and best practices →

---

Security & Validation

Automatic Security

• SQL Injection Protection - All parameters use prepared statements
• Type Validation - Parameters validated against defined schemas
• IBM i Authorities - Queries respect object-level permissions
• Audit Logging - Optional detailed execution logs

Security Configuration

Mark sensitive tools for enhanced protection:

security:
  readOnly: true
  audit: true
  requiredAuthority: "*AUDIT"
  scopes: ["security:read"]
  warning: "Accesses sensitive data"

---

Loading and Configuration

Load Specific Toolsets

# Load single toolset
npx -y @ibm/ibmi-mcp-server@latest --tools tools/ --toolsets performance_monitoring

# Load multiple toolsets
npx -y @ibm/ibmi-mcp-server@latest --tools tools/ --toolsets performance_monitoring,security_audit

Environment Variables

# Development environment
DB2i_HOST=ibmi-dev.local
DB2i_USER=DEVUSER
DB2i_PASS=DevPassword

# Load specific toolsets
SELECTED_TOOLSETS=development_full,testing_validation

---

Complete Example

A simple but complete YAML configuration:

# 1. Define database connection
sources:
  ibmi-system:
    host: ${DB2i_HOST}
    user: ${DB2i_USER}
    password: ${DB2i_PASS}
    port: 8076

# 2. Create SQL tools
tools:
  system_status:
    source: ibmi-system
    description: "Overall system performance metrics"
    statement: |
      SELECT * FROM TABLE(QSYS2.SYSTEM_STATUS()) X
    parameters: []

  active_job_info:
    source: ibmi-system
    description: "Find top CPU consumers"
    parameters:
      - name: limit
        type: integer
        default: 10
        minimum: 1
        maximum: 100
    statement: |
      SELECT CPU_TIME, JOB_NAME, USER_NAME
      FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) A
      ORDER BY CPU_TIME DESC
      FETCH FIRST :limit ROWS ONLY

# 3. Organize into toolsets
toolsets:
  performance_monitoring:
    title: "Performance Monitoring"
    description: "System performance and resource tools"
    tools:
      - system_status
      - active_job_info

Complete Examples: See the Building SQL Tools guide for the comprehensive employee-info.yaml example that demonstrates all parameter types and SQL patterns in a production-ready configuration.

---

Next Steps

Sources Reference

Database connection configuration

Tools Reference

SQL tool definitions and parameters

Toolsets Reference

Organizing tools into groups

Building SQL Tools

Step-by-step guide with complete examples

Configuration Guide

Server configuration and environment variables

Design Philosophy: YAML SQL tools democratize AI agent development for IBM i environments. SQL professionals build sophisticated tools using familiar SQL syntax, while the MCP server handles AI integration, security, parameter validation, and protocol management automatically.