SQL Tools Overview

The IBM i MCP Server’s SQL Tools system allows you to create powerful database operations using simple YAML configurations. This approach enables rapid development of IBM i-specific tools without requiring TypeScript programming skills.

YAML SQL Tools vs. TypeScript Tools: While TypeScript tools offer maximum flexibility, YAML SQL tools provide a faster path to creating database operations with built-in security, parameter validation, and IBM i integration.

Why YAML SQL Tools?

For IBM i environments, most AI agent interactions involve querying database tables, views, and system services. YAML SQL tools are specifically designed for this use case:

Rapid Development

Create new tools in minutes using familiar SQL statements and simple parameter definitions

Built-in Security

Automatic SQL injection protection, parameter validation, and authority checking

IBM i Integration

Native support for QSYS2 services, system catalogs, and IBM i SQL features

Zero Code Required

No TypeScript knowledge needed - everything is configured in YAML

YAML Tool Structure

Every YAML configuration file follows a consistent structure that defines sources, tools, and toolsets:

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

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

# Logical groupings of related tools
toolsets:
  performance:
    tools: [system_status]

Architecture Decision: The YAML approach separates data access configuration from application code, making it easier for IBM i professionals to create and maintain database tools while ensuring security and consistency.

Component Breakdown

Sources

Sources define database connections with environment variable support for secure credential management:

Basic Connection
Multiple Environments
Advanced Options

sources:
  ibmi-system:
    host: ${DB2i_HOST}
    user: ${DB2i_USER}
    password: ${DB2i_PASS}
    port: 8076
    ignore-unauthorized: true  # For development only

Tools

Tools are individual SQL operations that AI agents can execute. Each tool includes metadata, parameters, and the SQL statement:

tools:
  active_job_info:
    source: ibmi-system
    description: "Find the top CPU consumers in specific subsystems"
    parameters:
      - name: limit
        type: integer
        default: 10
        description: "Number of top CPU consumers to return"
        minimum: 1
        maximum: 100
    statement: |
      SELECT CPU_TIME, A.* FROM
      TABLE(QSYS2.ACTIVE_JOB_INFO(SUBSYSTEM_LIST_FILTER => 'QUSRWRK,QSYSWRK')) A
      ORDER BY CPU_TIME DESC
      FETCH FIRST :limit ROWS ONLY

Tool Parameters

Parameters provide type-safe input validation and enable dynamic SQL execution:

String Parameters

parameters:
  - name: library_name
    type: string
    required: true
    pattern: "^[A-Z][A-Z0-9_]*$"
    maxLength: 10
    description: "IBM i library name (uppercase alphanumeric)"

Features:

Pattern validation with regex
Length constraints
Required/optional flags
Default values

Integer Parameters

parameters:
  - name: max_rows
    type: integer
    default: 50
    minimum: 1
    maximum: 1000
    description: "Maximum number of rows to return"

Features:

Range validation (min/max)
Default values
Automatic type conversion

Enum Parameters

parameters:
  - name: object_type
    type: string
    enum: ["*PGM", "*FILE", "*SRVPGM", "*DTAARA"]
    default: "*FILE"
    description: "IBM i object type filter"

Features:

Controlled value lists
Default selection
AI-friendly descriptions

Optional Parameters

parameters:
  - name: user_filter
    type: string
    required: false
    description: "Optional user name filter"

SQL Usage:

WHERE (:user_filter IS NULL OR user_name = :user_filter)

Features:

NULL handling in SQL
Conditional filtering
Optional validation

Toolsets

Toolsets organize related tools into logical groups that can be deployed together:

toolsets:
  performance_monitoring:
    title: "Performance Monitoring"
    description: "Tools for monitoring IBM i system performance"
    tools:
      - system_status
      - active_job_info
      - memory_pools
      - http_server

  system_administration:
    title: "System Administration"
    description: "Administrative tools for system management"
    tools:
      - describe_object
      - list_services_by_category
      - search_services_by_name

Security Features

YAML SQL tools include comprehensive security features designed for enterprise environments:

SQL Injection Protection

All parameters are bound using prepared statements, preventing SQL injection attacks:

# Safe parameter binding
statement: |
  SELECT * FROM library_list
  WHERE library_name = :library_name
  AND object_type = :object_type

# Parameters are automatically escaped and bound
parameters:
  - name: library_name
    type: string
    pattern: "^[A-Z][A-Z0-9_]*$"

Security Configuration

Tools can include security metadata for enhanced protection:

tools:
  execute_sql:
    # ... basic configuration
    security:
      readOnly: true
      audit: true
      maxQueryLength: 15000
      forbiddenKeywords: ["DROP", "DELETE", "UPDATE", "TRUNCATE"]
      scopes: ["system:read"]

Security Best Practice: Always mark potentially sensitive operations with security.audit: true to ensure comprehensive audit logging for compliance and security monitoring.

Authority Integration

The MCP server integrates with IBM i’s security model:

User Authentication: All queries execute with authenticated user credentials
Object Authority: Respects IBM i object-level permissions
Special Authority: Honors special authorities like *ALLOBJ and *AUDIT
Program Adoption: Supports adopted authority for stored procedures

Tool Discovery and Organization

The YAML system supports sophisticated tool organization for complex environments:

Domain Classification

tools:
  system_performance:
    domain: monitoring
    category: performance
    # ... rest of configuration

  user_management:
    domain: security
    category: administration
    # ... rest of configuration

Metadata Enhancement

tools:
  inventory_analysis:
    metadata:
      title: "Inventory Analysis Report"
      version: "2.1.0"
      author: "IBM i Development Team"
      keywords: ["inventory", "analysis", "reporting"]
      lastUpdated: "2024-01-15"

Environment-Specific Tools

tools:
  production_health_check:
    source: production-system
    environment: production
    security:
      scopes: ["production:read"]
      audit: true

  development_sandbox:
    source: development-system
    environment: development
    security:
      scopes: ["development:admin"]

Configuration Management

File Organization

prebuiltconfigs/
├── performance.yaml      # System performance tools
├── sys-admin.yaml        # Administrative tools
├── business-logic.yaml   # Custom business tools
└── security.yaml         # Security and audit tools

Environment Variables

All connection details and sensitive information use environment variables:

sources:
  primary:
    host: ${DB2i_HOST}
    user: ${DB2i_USER}
    password: ${DB2i_PASS}
    port: ${DB2i_PORT:8076}           # Default port
    ignore-unauthorized: ${DB2i_IGNORE_UNAUTHORIZED:false}

Toolset Selection

Control which tools are loaded using environment configuration:

# Load specific toolsets
SELECTED_TOOLSETS=performance,monitoring
TOOLS_YAML_PATH=prebuiltconfigs

# Load all toolsets from directory
TOOLS_YAML_PATH=prebuiltconfigs

# Load single file
TOOLS_YAML_PATH=prebuiltconfigs/performance.yaml

Complete YAML Configuration Example

Here’s a complete YAML configuration that demonstrates real-world usage patterns with tools from the prebuilt configurations:

# Connection configuration
sources:
  ibmi-system:
    host: ${DB2i_HOST}
    user: ${DB2i_USER}
    password: ${DB2i_PASS}
    port: 8076
    ignore-unauthorized: true

# SQL Tools with various parameter types and complexity
tools:
  # Simple tool with no parameters
  system_status:
    source: ibmi-system
    description: "Overall system performance statistics with CPU, memory, and I/O metrics"
    statement: |
      SELECT * FROM TABLE(QSYS2.SYSTEM_STATUS(
        RESET_STATISTICS=>'YES',
        DETAILED_INFO=>'ALL'
      )) X

  # Tool with integer parameter and validation
  active_job_info:
    source: ibmi-system
    description: "Find the top CPU consumers with filtering options"
    parameters:
      - name: limit
        type: integer
        default: 10
        minimum: 1
        maximum: 100
        description: "Number of top CPU consumers to return"
    statement: |
      SELECT CPU_TIME, JOB_NAME, SUBSYSTEM_NAME, JOB_TYPE, 
             AUTHORIZATION_NAME, FUNCTION, JOB_STATUS
      FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) A
      ORDER BY CPU_TIME DESC
      FETCH FIRST :limit ROWS ONLY

  # Tool with multiple parameters including enum and required fields
  describe_object:
    source: ibmi-system
    description: "Generate DDL definition for database objects"
    parameters:
      - name: name
        type: string
        required: true
        description: "The name of the object"
      - name: library
        type: string
        required: true
        default: "QSYS2"
        description: "The library containing the object"
      - name: type
        type: string
        default: "TABLE"
        enum: ["TABLE", "VIEW", "INDEX", "PROCEDURE", "FUNCTION", "ALIAS"]
        description: "The type of database object"
    statement: |
      CALL QSYS2.GENERATE_SQL(
        DATABASE_OBJECT_NAME => :name,
        DATABASE_OBJECT_LIBRARY_NAME => :library,
        DATABASE_OBJECT_TYPE => :type,
        CREATE_OR_REPLACE_OPTION => '1',
        STATEMENT_FORMATTING_OPTION => '0'
      )

  # Tool with string parameter and pattern matching
  list_services_by_category:
    source: ibmi-system
    description: "Browse IBM i services for a specific category"
    parameters:
      - name: category
        type: string
        required: true
        description: "Service category name (e.g., 'Performance', 'System Administration')"
    statement: |
      SELECT service_schema_name,
             service_name,
             sql_object_type,
             earliest_possible_release,
             latest_db2_group_level
      FROM qsys2.services_info
      WHERE service_category = :category
      ORDER BY service_schema_name, service_name

  # Tool with optional parameter handling
  search_services_by_name:
    source: ibmi-system
    description: "Search for services by name pattern with optional case-insensitive matching"
    parameters:
      - name: search_term
        type: string
        required: true
        description: "Search term to match against service names"
      - name: case_sensitive
        type: string
        default: "NO"
        enum: ["YES", "NO"]
        description: "Whether search should be case sensitive"
    statement: |
      SELECT service_schema_name,
             service_name,
             service_category,
             sql_object_type,
             system_object_name
      FROM qsys2.services_info
      WHERE CASE 
        WHEN :case_sensitive = 'YES' THEN service_name LIKE '%' CONCAT :search_term CONCAT '%'
        ELSE UPPER(service_name) LIKE '%' CONCAT UPPER(:search_term) CONCAT '%'
      END
      ORDER BY service_schema_name, service_name
      FETCH FIRST 50 ROWS ONLY

# Logical groupings of tools
toolsets:
  performance_monitoring:
    title: "Performance Monitoring"
    description: "Tools for monitoring IBM i system performance and resource utilization"
    tools:
      - system_status
      - active_job_info

  system_administration:
    title: "System Administration"  
    description: "Administrative tools for managing IBM i database objects and services"
    tools:
      - describe_object
      - list_services_by_category
      - search_services_by_name

Key Features Demonstrated:

Multiple Parameter Types: String, integer, enum parameters with validation
Parameter Validation: Required fields, defaults, min/max values, enum options
Complex SQL: Table functions, stored procedure calls, conditional logic
IBM i Integration: QSYS2 system services and proper IBM i SQL syntax
Toolset Organization: Logical grouping of related tools for agent filtering
Real-world Usage: Based on actual prebuilt configurations used in production

Best Practices

SQL Statement Guidelines

Use IBM i system services (QSYS2 views/procedures) when available
Include appropriate row limiting (FETCH FIRST n ROWS ONLY)
Handle NULL parameters with conditional logic
Use descriptive column aliases for better AI understanding
Consider performance impact on production systems

Parameter Design

Provide clear, AI-friendly parameter descriptions
Use appropriate validation (patterns, ranges, enums)
Set sensible defaults for optional parameters
Follow IBM i naming conventions
Include examples in descriptions when helpful

Security Considerations

Always use parameter binding (never string concatenation)
Mark sensitive operations with security.audit: true
Use read-only flags where appropriate
Include authority requirements in descriptions
Test with least-privileged users

Documentation Standards

Write descriptions for the AI agent audience
Include business context in tool descriptions
Specify units for numeric values
Document any special authority requirements
Provide usage examples and common scenarios

Integration with MCP Agents

YAML SQL tools seamlessly integrate with MCP agents, providing:

Automatic Schema Generation: Parameters become typed inputs for agents
Response Formatting: Results are automatically formatted for agent consumption
Error Handling: Comprehensive error messages help agents understand issues
Tool Discovery: Agents can enumerate and understand available tools

To understand how to build agents that effectively use SQL tools, see the Agent Development Guide. For server architecture details, review the Server Architecture documentation.

Next Steps

Ready to create your own SQL tools?

Building Tools

Step-by-step guide to creating custom YAML SQL tools

Testing & Development

Learn how to test and debug your SQL tools effectively

Examples & Use Cases

Explore real-world examples and common usage patterns

Configuration Reference

Complete reference for environment variables and settings

Design Philosophy: YAML SQL tools democratize AI agent development for IBM i environments by enabling database professionals to create sophisticated tools using their existing SQL expertise, while the MCP server handles all the complexity of agent integration, security, and protocol management.

Getting Started

Core Concepts

SQL Tools System

Agent Development

SQL Tools Overview

SQL Tools Overview

Why YAML SQL Tools?

Rapid Development

Built-in Security

IBM i Integration

Zero Code Required

YAML Tool Structure

Component Breakdown

Sources

Tools

Tool Parameters

Toolsets

Security Features

SQL Injection Protection

Security Configuration

Authority Integration

Tool Discovery and Organization

Domain Classification

Metadata Enhancement

Environment-Specific Tools

Configuration Management

File Organization

Environment Variables

Toolset Selection

Complete YAML Configuration Example

Best Practices

Integration with MCP Agents

Next Steps

Building Tools

Testing & Development

Examples & Use Cases

Configuration Reference

Getting Started

Core Concepts

SQL Tools System

Agent Development

​SQL Tools Overview

​Why YAML SQL Tools?

Rapid Development

Built-in Security

IBM i Integration

Zero Code Required

​YAML Tool Structure

​Component Breakdown

​Sources

​Tools

​Tool Parameters

​Toolsets

​Security Features

​SQL Injection Protection

​Security Configuration

​Authority Integration

​Tool Discovery and Organization

​Domain Classification

​Metadata Enhancement

​Environment-Specific Tools

​Configuration Management

​File Organization

​Environment Variables

​Toolset Selection

​Complete YAML Configuration Example

​Best Practices

​Integration with MCP Agents

​Next Steps

Building Tools

Testing & Development

Examples & Use Cases

Configuration Reference

SQL Tools Overview

Why YAML SQL Tools?

YAML Tool Structure

Component Breakdown

Sources

Tools

Tool Parameters

Toolsets

Security Features

SQL Injection Protection

Security Configuration

Authority Integration

Tool Discovery and Organization

Domain Classification

Metadata Enhancement

Environment-Specific Tools

Configuration Management

File Organization

Environment Variables

Toolset Selection

Complete YAML Configuration Example

Best Practices

Integration with MCP Agents

Next Steps