Building SQL Tools - IBM i MCP Server

This guide walks you through creating custom YAML SQL tools from basic queries to advanced enterprise operations. You’ll learn to build tools that integrate seamlessly with AI agents while maintaining IBM i security and performance standards.

Prerequisites: Basic SQL knowledge and familiarity with IBM i system services (QSYS2 schema). For YAML syntax, any text editor will work, but one with YAML syntax highlighting is recommended.

Your First SQL Tool

Let’s start with a simple tool that lists active jobs. This example introduces the core concepts you’ll use in all YAML SQL tools.

Step 1: Create the Configuration File

Create a new file called my-first-tools.yaml:

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

# Define your SQL tools
tools:
  list_active_jobs:
    source: ibmi-system
    description: "List currently active jobs on the IBM i system"
    statement: |
      SELECT job_name, user_name, job_status, cpu_used, elapsed_time
      FROM qsys2.active_job_info
      WHERE job_status = 'ACTIVE'
      ORDER BY cpu_used DESC
      FETCH FIRST 20 ROWS ONLY
    parameters: []

# Organize tools into logical groups
toolsets:
  basic_monitoring:
    title: "Basic System Monitoring"
    description: "Simple tools for monitoring system activity"
    tools:
      - list_active_jobs

Step 2: Test Your Tool

Start the MCP server with your new configuration:

TOOLS_YAML_PATH=my-first-tools.yaml npm run start:http

Use the MCP Inspector to test your tool:

npm run mcp-inspector

Development Tip: Always start with simple, parameter-free tools to verify your connection and SQL syntax. You can add complexity incrementally as you gain confidence with the YAML structure.

Adding Parameters

Parameters make tools dynamic and reusable. Let’s enhance our job listing tool with filtering capabilities:

Parameter Examples Guide

Need comprehensive examples? See the Parameter Guide for detailed examples of all parameter types (string, integer, float, boolean, array) with validation patterns and best practices.

Basic Parameters
Optional Parameters
Enum Parameters

tools:
  list_jobs_by_user:
    source: ibmi-system
    description: "List active jobs for a specific user"
    parameters:
      - name: user_name
        type: string
        required: true
        description: "IBM i user profile name (uppercase)"
        pattern: "^[A-Z][A-Z0-9_]*$"
        maxLength: 10
    statement: |
      SELECT job_name, job_status, cpu_used, elapsed_time
      FROM qsys2.active_job_info
      WHERE user_name = :user_name
      AND job_status = 'ACTIVE'
      ORDER BY cpu_used DESC
      FETCH FIRST 50 ROWS ONLY

tools:
  flexible_job_search:
    source: ibmi-system
    description: "Search jobs with optional filtering"
    parameters:
      - name: user_filter
        type: string
        required: false
        description: "Optional user name filter"
      - name: max_rows
        type: integer
        default: 25
        minimum: 1
        maximum: 100
        description: "Maximum number of jobs to return"
    statement: |
      SELECT job_name, user_name, job_status, cpu_used
      FROM qsys2.active_job_info
      WHERE (:user_filter IS NULL OR user_name = :user_filter)
      AND job_status = 'ACTIVE'
      ORDER BY cpu_used DESC
      FETCH FIRST :max_rows ROWS ONLY

tools:
  jobs_by_status:
    source: ibmi-system
    description: "List jobs filtered by job status"
    parameters:
      - name: status
        type: string
        enum: ["ACTIVE", "ENDED", "OUTQ", "MSGW"]
        default: "ACTIVE"
        description: "Job status to filter by"
    statement: |
      SELECT job_name, user_name, job_status,
             job_type, subsystem, elapsed_time
      FROM qsys2.active_job_info
      WHERE job_status = :status
      ORDER BY elapsed_time DESC
      FETCH FIRST 30 ROWS ONLY

Parameter Validation

The MCP server automatically validates parameters before executing SQL:

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

  - name: row_limit
    type: integer
    minimum: 1
    maximum: 1000
    default: 50
    description: "Number of rows to return (1-1000)"

Security Note: Parameter validation is your first line of defense against invalid input. Always include appropriate constraints, especially for string parameters that could be used in injection attacks.

Complete Example: Employee Information Tools

The tools/sample/employee-info.yaml file demonstrates a comprehensive set of tools using the IBM i SAMPLE schema. This example showcases all five parameter types, validation patterns, and SQL techniques in a single, production-ready configuration.

Why This Example Matters: Rather than showing isolated snippets, this complete file demonstrates how real-world tools are structured, how parameters work together, and how SQL patterns combine to create powerful data operations.

Overview

File Location: tools/sample/employee-info.yaml Purpose: Provide HR and project management capabilities using IBM i’s SAMPLE database (EMPLOYEE, DEPARTMENT, PROJECT tables) What You’ll Learn:

All 5 parameter types (string, integer, float, boolean, array) in production context
Real-world SQL patterns: joins, self-joins, aggregations, pagination
Parameter validation strategies
Toolset organization for discoverability

Source Configuration

Every YAML file starts with a source definition:

sources:
  ibmi-sample:
    host: ${DB2i_HOST}
    user: ${DB2i_USER}
    password: ${DB2i_PASS}
    port: 8076
    ignore-unauthorized: true

Source Reuse: This source is reused by all 8 tools in the file. Define sources once at the top, reference them in each tool using the source field. This ensures consistent connection settings and simplifies credential management.

Tool 1: String Parameters with Pattern Validation

Tool: get_employee_details Demonstrates: String pattern validation, table joins, self-joins for hierarchical data

get_employee_details:
  source: ibmi-sample
  description: Retrieve detailed information about an employee including department and manager
  statement: |
    SELECT
      E.EMPNO,
      E.FIRSTNME,
      E.MIDINIT,
      E.LASTNAME,
      E.JOB,
      E.HIREDATE,
      E.SALARY,
      E.BONUS,
      E.WORKDEPT,
      D.DEPTNAME,
      D.LOCATION,
      M.FIRSTNME AS MGR_FIRSTNME,
      M.LASTNAME AS MGR_LASTNAME
    FROM SAMPLE.EMPLOYEE E
    LEFT JOIN SAMPLE.DEPARTMENT D ON E.WORKDEPT = D.DEPTNO
    LEFT JOIN SAMPLE.EMPLOYEE M ON D.MGRNO = M.EMPNO
    WHERE E.EMPNO = :employee_id
  parameters:
    - name: employee_id
      type: string
      description: "Employee ID (e.g., '000010') - Must be 6 digits"
      required: true
      pattern: "^[0-9]{6}$"

Key Techniques:

Pattern validation: ^[0-9]{6}$ enforces exactly 6 digits
LEFT JOIN: Handles missing departments or managers gracefully
Self-join: EMPLOYEE M retrieves manager information by joining EMPLOYEE to itself
Descriptive aliases: MGR_FIRSTNME, MGR_LASTNAME clarify the data source

MCP Tool Call:

{
  "name": "get_employee_details",
  "arguments": {
    "employee_id": "000010"
  }
}

Tool 2 & 3: String Enum Parameters

Tools: find_employees_by_department, find_employees_by_job Demonstrates: Enum constraints for controlled value selection

Department Filter
Job Title Filter

find_employees_by_department:
  source: ibmi-sample
  description: List employees in a specific department
  statement: |
    SELECT
      E.EMPNO,
      E.FIRSTNME,
      E.MIDINIT,
      E.LASTNAME,
      E.JOB,
      E.HIREDATE,
      E.SALARY
    FROM SAMPLE.EMPLOYEE E
    WHERE E.WORKDEPT = :department_id
    ORDER BY E.LASTNAME, E.FIRSTNME
  parameters:
    - name: department_id
      type: string
      description: "Department ID - Select from predefined departments"
      required: true
      enum: ["A00", "B01", "C01", "D01", "E01"]

find_employees_by_job:
  source: ibmi-sample
  description: Find employees with a specific job title
  statement: |
    SELECT
      E.EMPNO,
      E.FIRSTNME,
      E.MIDINIT,
      E.LASTNAME,
      E.WORKDEPT,
      D.DEPTNAME,
      E.HIREDATE,
      E.SALARY
    FROM SAMPLE.EMPLOYEE E
    LEFT JOIN SAMPLE.DEPARTMENT D ON E.WORKDEPT = D.DEPTNO
    WHERE E.JOB = :job_title
    ORDER BY E.LASTNAME, E.FIRSTNME
  parameters:
    - name: job_title
      type: string
      description: "Job title - Select from common job titles"
      required: true
      enum: ["MANAGER", "ANALYST", "DESIGNER", "CLERK", "SALESREP", "PRES"]

Enum Parameters: Enum parameters automatically enhance descriptions for LLMs: “Must be one of: ‘A00’, ‘B01’, ‘C01’…”. This provides autocomplete-like guidance and prevents invalid queries. Use enums whenever you have a fixed set of valid values.

Tool 4: Boolean Parameters

Tool: get_employee_projects Demonstrates: Boolean flags for conditional filtering, complex multi-table joins

get_employee_projects:
  source: ibmi-sample
  description: List projects an employee is working on
  statement: |
    SELECT
      P.PROJNO,
      P.PROJNAME,
      A.ACTNO,
      A.ACTDESC,
      EPA.EMSTDATE AS START_DATE,
      EPA.EMENDATE AS END_DATE,
      EPA.EMPTIME
    FROM SAMPLE.EMPPROJACT EPA
    JOIN SAMPLE.PROJECT P ON EPA.PROJNO = P.PROJNO
    JOIN SAMPLE.PROJACT PA ON EPA.PROJNO = PA.PROJNO AND EPA.ACTNO = PA.ACTNO
    JOIN SAMPLE.ACT A ON EPA.ACTNO = A.ACTNO
    WHERE EPA.EMPNO = :employee_id
    AND (:include_completed = 1 OR EPA.EMENDATE IS NULL)
    ORDER BY EPA.EMSTDATE DESC
  parameters:
    - name: employee_id
      type: string
      description: "Employee ID (e.g., '000010') - Must be 6 digits"
      required: true
      pattern: "^[0-9]{6}$"
    - name: include_completed
      type: boolean
      description: "Include completed projects (true) or only active projects (false)"
      default: true

Key Techniques:

Boolean in SQL: :include_completed = 1 (true) or = 0 (false)
Conditional filtering: (:include_completed = 1 OR EPA.EMENDATE IS NULL) filters active projects when false
4-table joins: Connects employee project activities with projects, project activities, and activity descriptions
Default value: Makes parameter optional (defaults to showing all projects)

Usage Examples:

// Get all projects (completed and active)
{
  "name": "get_employee_projects",
  "arguments": {
    "employee_id": "000010",
    "include_completed": true
  }
}

// Get only active projects
{
  "name": "get_employee_projects",
  "arguments": {
    "employee_id": "000010",
    "include_completed": false
  }
}

Tool 5: Integer Parameters with Aggregations

Tool: get_department_salary_stats Demonstrates: Multiple optional integers, default values, SQL aggregations

get_department_salary_stats:
  source: ibmi-sample
  description: Salary statistics by department with optional salary range filter
  statement: |
    SELECT
      D.DEPTNO,
      D.DEPTNAME,
      COUNT(E.EMPNO) AS EMPLOYEE_COUNT,
      AVG(E.SALARY) AS AVG_SALARY,
      MIN(E.SALARY) AS MIN_SALARY,
      MAX(E.SALARY) AS MAX_SALARY,
      SUM(E.SALARY) AS TOTAL_SALARY
    FROM SAMPLE.DEPARTMENT D
    LEFT JOIN SAMPLE.EMPLOYEE E ON D.DEPTNO = E.WORKDEPT
    WHERE (D.DEPTNO = :department_id OR :department_id = '*ALL')
    AND (E.SALARY >= :min_salary OR :min_salary IS NULL)
    AND (E.SALARY <= :max_salary OR :max_salary IS NULL)
    GROUP BY D.DEPTNO, D.DEPTNAME
    ORDER BY D.DEPTNO
  parameters:
    - name: department_id
      type: string
      description: "Department ID (e.g., 'A00') or '*ALL' for all departments"
      default: "*ALL"
    - name: min_salary
      type: integer
      description: "Minimum salary filter"
      min: 0
      max: 100000
      default: 0
    - name: max_salary
      type: integer
      description: "Maximum salary filter"
      min: 0
      max: 100000
      default: 100000

Key Techniques:

Integer constraints: min: 0, max: 100000 prevent invalid salary ranges
Aggregation functions: COUNT, AVG, MIN, MAX, SUM provide statistical summaries
GROUP BY: Groups results by department for aggregate calculations
Special value pattern: '*ALL' provides “all departments” option
NULL handling: OR :min_salary IS NULL allows optional filtering

Default Values: Integer parameters with default values don’t need required: false. The presence of a default makes them optional automatically. This pattern works for all parameter types.

Tool 6: Array Parameters

Tool: find_project_team_members Demonstrates: Array parameters with SQL IN clauses, array length constraints

find_project_team_members:
  source: ibmi-sample
  description: Find all employees working on specific projects
  statement: |
    SELECT
      E.EMPNO,
      E.FIRSTNME,
      E.MIDINIT,
      E.LASTNAME,
      E.JOB,
      E.WORKDEPT,
      D.DEPTNAME,
      EPA.PROJNO,
      EPA.EMSTDATE AS PROJECT_START_DATE,
      EPA.EMENDATE AS PROJECT_END_DATE,
      EPA.EMPTIME AS TIME_ALLOCATION
    FROM SAMPLE.EMPPROJACT EPA
    JOIN SAMPLE.EMPLOYEE E ON EPA.EMPNO = E.EMPNO
    LEFT JOIN SAMPLE.DEPARTMENT D ON E.WORKDEPT = D.DEPTNO
    WHERE EPA.PROJNO IN (:project_ids)
    ORDER BY EPA.PROJNO, E.LASTNAME, E.FIRSTNME
  parameters:
    - name: project_ids
      type: array
      itemType: string
      description: "List of project IDs to search for (e.g., ['MA2100', 'AD3100'])"
      required: true
      minLength: 1
      maxLength: 10

Key Techniques:

Array expansion: IN (:project_ids) automatically expands to IN (?, ?, ?) with safe parameter binding
itemType: Specifies that array contains strings (also supports integer, float, boolean)
Length constraints: minLength: 1 ensures at least one ID, maxLength: 10 prevents overly broad queries
Example in description: Guides LLM on correct JSON array format

MCP Tool Call:

{
  "name": "find_project_team_members",
  "arguments": {
    "project_ids": ["MA2100", "AD3100", "PL2100"]
  }
}

Array Input Format: Arrays must be passed as JSON arrays, not strings:

✅ Correct: {"project_ids": ["MA2100", "AD3100"]}
❌ Incorrect: {"project_ids": "('MA2100', 'AD3100')"}

Tool 7: Float Parameters

Tool: calculate_employee_bonus Demonstrates: Float parameters for decimal calculations, mathematical operations

calculate_employee_bonus:
  source: ibmi-sample
  description: Calculate potential bonus for an employee based on performance rating
  statement: |
    SELECT
      E.EMPNO,
      E.FIRSTNME,
      E.LASTNAME,
      E.SALARY,
      E.SALARY * :performance_multiplier AS CALCULATED_BONUS
    FROM SAMPLE.EMPLOYEE E
    WHERE E.EMPNO = :employee_id
  parameters:
    - name: employee_id
      type: string
      description: "Employee ID (e.g., '000010')"
      required: true
      pattern: "^[0-9]{6}$"
    - name: performance_multiplier
      type: float
      description: "Performance rating multiplier (0.0-0.3)"
      required: true
      min: 0.0
      max: 0.3
      default: 0.1

Key Techniques:

Float type: Allows decimal values (0.1, 0.15, 0.25, etc.)
Range constraints: min: 0.0, max: 0.3 limits multiplier to 0-30%
SQL arithmetic: E.SALARY * :performance_multiplier performs calculation
Combined parameters: String pattern + float calculation in single tool

Usage:

// 10% bonus (default)
{
  "name": "calculate_employee_bonus",
  "arguments": {
    "employee_id": "000010",
    "performance_multiplier": 0.1
  }
}

// 25% bonus
{
  "name": "calculate_employee_bonus",
  "arguments": {
    "employee_id": "000010",
    "performance_multiplier": 0.25
  }
}

Tool 8: Pagination with Multiple Parameters

Tool: search_employees Demonstrates: LIMIT/OFFSET pagination, case-insensitive search, partial matching

search_employees:
  source: ibmi-sample
  description: Search for employees by name with pagination
  statement: |
    SELECT
      E.EMPNO,
      E.FIRSTNME,
      E.MIDINIT,
      E.LASTNAME,
      E.JOB,
      E.WORKDEPT,
      D.DEPTNAME
    FROM SAMPLE.EMPLOYEE E
    LEFT JOIN SAMPLE.DEPARTMENT D ON E.WORKDEPT = D.DEPTNO
    WHERE UPPER(E.FIRSTNME) LIKE UPPER('%' || :name_search || '%')
    OR UPPER(E.LASTNAME) LIKE UPPER('%' || :name_search || '%')
    ORDER BY E.LASTNAME, E.FIRSTNME
    LIMIT :page_size OFFSET (:page_number - 1) * :page_size
  parameters:
    - name: name_search
      type: string
      description: "Name to search for (partial match)"
      required: true
      minLength: 2
    - name: page_size
      type: integer
      description: "Number of results per page"
      default: 10
      min: 1
      max: 100
    - name: page_number
      type: integer
      description: "Page number (starting from 1)"
      default: 1
      min: 1

Key Techniques:

minLength: minLength: 2 prevents single-character searches that return too many results
Pagination pattern: LIMIT :page_size OFFSET (:page_number - 1) * :page_size
Case-insensitive search: UPPER(column) LIKE UPPER(pattern)
Partial matching: '%' || :name_search || '%' finds names containing the search term
Multiple integer parameters: page_size and page_number with sensible defaults

Usage:

// First page of results
{
  "name": "search_employees",
  "arguments": {
    "name_search": "Smith",
    "page_size": 10,
    "page_number": 1
  }
}

// Second page with custom size
{
  "name": "search_employees",
  "arguments": {
    "name_search": "JO",
    "page_size": 25,
    "page_number": 2
  }
}

Toolset Organization

The file defines 3 toolsets to organize the 8 tools by functional area:

toolsets:
  employee_information:
    title: "Employee Information"
    description: "Tools for retrieving and analyzing employee data"
    tools:
      - get_employee_details
      - find_employees_by_department
      - find_employees_by_job
      - search_employees

  project_management:
    title: "Project Management"
    description: "Tools for managing project assignments and team members"
    tools:
      - get_employee_projects
      - find_project_team_members

  salary_analysis:
    title: "Salary Analysis"
    description: "Tools for analyzing salary data across departments"
    tools:
      - get_department_salary_stats
      - calculate_employee_bonus

Selective Loading: Toolsets enable selective loading. Load only what you need:

--toolsets employee_information loads just employee lookup tools
--toolsets employee_information,salary_analysis loads two categories
Omit --toolsets to load everything

This improves startup time and reduces API surface for focused agents.

Running the Example

List available toolsets:

npx -y @ibm/ibmi-mcp-server@latest --list-toolsets --tools tools/sample/employee-info.yaml

Start server with specific toolsets:

# Load only employee information tools
npx -y @ibm/ibmi-mcp-server@latest --tools tools/sample/employee-info.yaml --toolsets employee_information

# Load multiple toolsets
npx -y @ibm/ibmi-mcp-server@latest --tools tools/sample/employee-info.yaml --toolsets employee_information,salary_analysis

# Load entire directory (all sample tools)
npx -y @ibm/ibmi-mcp-server@latest --tools tools/sample

Parameter Type Summary

This example demonstrates all five parameter types across eight tools:

Tool	String	Integer	Float	Boolean	Array
`get_employee_details`	✅ (pattern)
`find_employees_by_department`	✅ (enum)
`find_employees_by_job`	✅ (enum)
`get_employee_projects`	✅ (pattern)			✅
`get_department_salary_stats`	✅ (default)	✅ (optional)
`find_project_team_members`					✅
`calculate_employee_bonus`	✅ (pattern)		✅
`search_employees`	✅ (minLength)	✅ (pagination)

SQL Techniques Demonstrated

This file also showcases essential SQL patterns for IBM i development:

Joins

INNER JOIN: Connecting related tables
LEFT JOIN: Handling optional relationships
Self-join: Hierarchical data (manager lookup)
Multi-table joins: 4-way joins for complex data

Aggregations

COUNT: Counting records
AVG, MIN, MAX, SUM: Statistical calculations
GROUP BY: Grouping for aggregates

Filtering

WHERE clauses: Basic and conditional filtering
IN clauses: Array-based filtering
NULL handling: Optional parameter patterns
Special values: *ALL pattern for “all records”

Search & Pagination

LIKE with wildcards: Partial matching
UPPER(): Case-insensitive search
LIMIT/OFFSET: Pagination pattern
ORDER BY: Sorting results

Common IBM i Patterns

Here are proven patterns for working with IBM i system services:

System Information Queries

tools:
  system_overview:
    source: ibmi-system
    description: "Comprehensive system status and configuration"
    statement: |
      SELECT
        system_name,
        current_timestamp,
        elapsed_time,
        total_jobs,
        active_threads,
        cpu_utilization,
        main_storage_size / 1024 / 1024 AS memory_gb,
        current_unprotected_used / 1024 / 1024 AS used_memory_gb
      FROM TABLE(QSYS2.SYSTEM_STATUS()) X

  subsystem_status:
    source: ibmi-system
    description: "Status of all subsystems"
    statement: |
      SELECT subsystem_name, subsystem_library, status,
             monitor_job, secondary_language,
             COALESCE(current_active_jobs, 0) as active_jobs,
             COALESCE(maximum_active_jobs, 0) as max_jobs
      FROM qsys2.subsystem_info
      ORDER BY subsystem_name

Library and Object Management

tools:
  library_objects:
    source: ibmi-system
    description: "List objects in a library with optional type filtering"
    parameters:
      - name: library
        type: string
        required: true
        pattern: "^[A-Z][A-Z0-9_]*$"
        description: "Library name to examine"
      - name: object_type
        type: string
        required: false
        enum: ["*FILE", "*PGM", "*SRVPGM", "*DTAARA", "*USRSPC"]
        description: "Object type filter (optional)"
    statement: |
      SELECT object_name, object_type, object_size,
             created_timestamp, last_used_timestamp,
             object_owner, authorization_list
      FROM table(qsys2.object_statistics(:library,
                 COALESCE(:object_type, '*ALL'))) x
      ORDER BY object_name

  library_list:
    source: ibmi-system
    description: "Show current library list for analysis"
    statement: |
      SELECT ordinal_position, library_name, library_type,
             library_text_description
      FROM qsys2.library_list_info
      ORDER BY ordinal_position

Database Analysis

tools:
  table_analysis:
    source: ibmi-system
    description: "Analyze table structure and statistics"
    parameters:
      - name: schema
        type: string
        required: true
        description: "Schema (library) name"
      - name: table_name
        type: string
        required: true
        description: "Table name to analyze"
    statement: |
      SELECT
        t.table_name,
        t.table_type,
        t.number_rows,
        t.create_timestamp,
        c.column_name,
        c.ordinal_position,
        c.data_type,
        c.column_size,
        c.is_nullable,
        c.column_default
      FROM qsys2.systables t
      JOIN qsys2.syscolumns c ON t.table_schema = c.table_schema
                             AND t.table_name = c.table_name
      WHERE t.table_schema = UPPER(:schema)
        AND t.table_name = UPPER(:table_name)
      ORDER BY c.ordinal_position

  foreign_key_relationships:
    source: ibmi-system
    description: "Find foreign key relationships for a table"
    parameters:
      - name: schema
        type: string
        required: true
      - name: table_name
        type: string
        required: true
    statement: |
      SELECT
        fk.constraint_name,
        fk.table_schema AS fk_schema,
        fk.table_name AS fk_table,
        fk.column_name AS fk_column,
        pk.table_schema AS pk_schema,
        pk.table_name AS pk_table,
        pk.column_name AS pk_column
      FROM qsys2.syskeycst fk
      JOIN qsys2.sysrefcst r ON fk.constraint_name = r.constraint_name
      JOIN qsys2.syskeycst pk ON r.unique_constraint_name = pk.constraint_name
      WHERE fk.table_schema = UPPER(:schema)
        AND fk.table_name = UPPER(:table_name)
      ORDER BY fk.constraint_name, fk.ordinal_position

Advanced Features

Response Formatting

Control how results are presented to AI agents:

tools:
  formatted_system_report:
    source: ibmi-system
    description: "System report with markdown formatting"
    responseFormat: markdown
    statement: |
      SELECT
        '# System Status Report' AS report_header,
        '## Current Time: ' || CURRENT_TIMESTAMP AS timestamp_section,
        '### CPU Utilization: ' || cpu_utilization || '%' AS cpu_info,
        '### Memory Usage: ' ||
          ROUND((current_unprotected_used * 100.0 / main_storage_size), 2) ||
          '%' AS memory_info
      FROM TABLE(QSYS2.SYSTEM_STATUS()) X

Security Configuration

Mark sensitive operations for audit logging and access control:

tools:
  sensitive_user_info:
    source: ibmi-system
    description: "User profile information (requires authority)"
    security:
      audit: true
      scopes: ["user:read"]
      readOnly: true
    parameters:
      - name: user_profile
        type: string
        required: true
        pattern: "^[A-Z][A-Z0-9_]*$"
    statement: |
      SELECT user_profile_name, status, previous_signon,
             days_until_password_expires, user_class_name
      FROM qsys2.user_info
      WHERE user_profile_name = :user_profile

  privileged_system_config:
    source: ibmi-system
    description: "System configuration requiring special authority"
    security:
      audit: true
      requiredAuthority: "*ALLOBJ"
      warning: "Requires *ALLOBJ special authority"
    statement: |
      SELECT * FROM qsys2.system_value_info
      WHERE system_value_name LIKE 'Q%SEC%'

Error Handling and Validation

Build robust tools with comprehensive error handling:

tools:
  validated_object_query:
    source: ibmi-system
    description: "Query objects with comprehensive validation"
    parameters:
      - name: library
        type: string
        required: true
        pattern: "^[A-Z][A-Z0-9_]*$"
        maxLength: 10
        description: "Library name (must exist and be accessible)"
      - name: object_filter
        type: string
        required: false
        pattern: "^[A-Z*][A-Z0-9_*]*$"
        maxLength: 10
        description: "Object name filter (supports * wildcards)"
    statement: |
      WITH library_check AS (
        SELECT library_name
        FROM qsys2.library_info
        WHERE library_name = :library
      ),
      object_list AS (
        SELECT o.*, l.library_name
        FROM library_check l,
             table(qsys2.object_statistics(l.library_name,
                   COALESCE(:object_filter, '*ALL'))) o
      )
      SELECT
        CASE
          WHEN NOT EXISTS(SELECT 1 FROM library_check)
          THEN 'ERROR: Library ' || :library || ' not found or not accessible'
          ELSE object_name
        END AS result,
        object_type,
        object_size,
        created_timestamp
      FROM object_list
      UNION ALL
      SELECT
        'ERROR: Library ' || :library || ' not found or not accessible',
        NULL, NULL, NULL
      FROM library_check
      WHERE NOT EXISTS(SELECT 1 FROM library_check)
      ORDER BY result

Testing and Debugging

Validation Commands

Test your YAML configuration before deployment:

# Validate YAML syntax and structure
npm run validate -- --config my-tools.yaml

# List available toolsets
npm run start:http -- --list-toolsets --tools my-tools.yaml

# Test specific tool execution
npm run test -- --grep "yaml-sql"

For complete testing strategies including MCP Inspector usage, see the Quick Start Guide. For production testing, refer to the Production Deployment guide.

Debug Mode

Enable debug logging to troubleshoot tool execution:

MCP_LOG_LEVEL=debug TOOLS_YAML_PATH=my-tools.yaml npm run start:http

Common Issues and Solutions

Parameter Binding Errors

Problem: Parameter :parameter_name not found in statementSolution: Ensure parameter names in the statement match exactly with parameter definitions:

# ❌ Mismatch
parameters:
  - name: user_name
statement: "WHERE user = :username"  # Wrong parameter name

# ✅ Correct
parameters:
  - name: user_name
statement: "WHERE user = :user_name"  # Matching parameter name

SQL Syntax Errors

Problem: SQL statement fails to executeSolution: Test SQL separately in a DB2 client first:

# Test in IBM i ACS or other SQL client
SELECT * FROM qsys2.active_job_info
WHERE user_name = 'TESTUSER'
FETCH FIRST 10 ROWS ONLY;

Authority Issues

Problem: SQL0551: Not authorized to objectSolution: Verify user has appropriate authorities:

-- Check object authorities
SELECT * FROM qsys2.object_privileges
WHERE object_schema = 'QSYS2'
  AND object_name = 'ACTIVE_JOB_INFO'
  AND grantee = 'YOUR_USER';

Connection Issues

Problem: Cannot connect to IBM i systemSolution: Verify connection parameters and Mapepire daemon:

# Check if Mapepire daemon is running
ping your-ibmi-host
telnet your-ibmi-host 8076

File Organization Best Practices

Single Domain Approach

Organize tools by business domain or functional area:

my-tools/
├── performance.yaml      # System performance monitoring
├── security.yaml         # Security and audit tools
├── business.yaml         # Business logic and reporting
└── development.yaml      # Development and testing tools

Multi-Environment Support

Use environment-specific configurations:

# environments/production.yaml
sources:
  production:
    host: ${PROD_DB2i_HOST}
    user: ${PROD_DB2i_USER}
    password: ${PROD_DB2i_PASS}
    port: 8076
    ignore-unauthorized: false

# environments/development.yaml
sources:
  development:
    host: ${DEV_DB2i_HOST}
    user: ${DEV_DB2i_USER}
    password: ${DEV_DB2i_PASS}
    port: 8076
    ignore-unauthorized: true

Version Control

Include metadata for tool versioning and maintenance:

metadata:
  version: "2.1.0"
  description: "Production monitoring tools for IBM i"
  author: "IT Operations Team"
  created: "2024-01-15"
  lastUpdated: "2024-01-20"
  keywords: ["monitoring", "performance", "production"]

Performance Considerations

Query Optimization

Always include appropriate performance optimizations:

# ✅ Good: Row limiting and proper indexing
statement: |
  SELECT job_name, user_name, cpu_used
  FROM qsys2.active_job_info
  WHERE user_name = :user_name
  ORDER BY cpu_used DESC
  FETCH FIRST 100 ROWS ONLY

# ❌ Avoid: Unlimited results
statement: |
  SELECT * FROM qsys2.active_job_info
  WHERE user_name = :user_name

Connection Pooling

The MCP server automatically manages connection pooling, but you can optimize usage:

# Use consistent source names for better pooling
sources:
  primary-system:  # Consistent naming
    host: ${DB2i_HOST}
    # ... connection details

tools:
  tool1:
    source: primary-system  # Reuses connection
  tool2:
    source: primary-system  # Reuses connection

Next Steps

Testing Guide

Learn how to test and debug your SQL tools effectively

Examples & Patterns

Explore real-world examples and advanced patterns

Agent Integration

Build AI agents that use your custom SQL tools

Production Deployment

Deploy your tools to production with monitoring

Tool Design Philosophy: Effective SQL tools balance three concerns: usability (clear parameters and descriptions for AI agents), security (proper validation and authority checking), and performance (efficient queries that respect system resources). Start simple and add complexity incrementally as you understand your specific use cases.

​Your First SQL Tool

​Step 1: Create the Configuration File

​Step 2: Test Your Tool

​Adding Parameters

Parameter Examples Guide

​Parameter Validation

​Complete Example: Employee Information Tools

​Overview

​Source Configuration

​Tool 1: String Parameters with Pattern Validation

​Tool 2 & 3: String Enum Parameters

​Tool 4: Boolean Parameters

​Tool 5: Integer Parameters with Aggregations

​Tool 6: Array Parameters

​Tool 7: Float Parameters

​Tool 8: Pagination with Multiple Parameters

​Toolset Organization

​Running the Example

​Parameter Type Summary

​SQL Techniques Demonstrated

Joins

Aggregations

Filtering

Search & Pagination

​Common IBM i Patterns

​System Information Queries

​Library and Object Management

​Database Analysis

​Advanced Features

​Response Formatting

​Security Configuration

​Error Handling and Validation

​Testing and Debugging

​Validation Commands

​Debug Mode

​Common Issues and Solutions

​File Organization Best Practices

​Single Domain Approach

​Multi-Environment Support

​Version Control

​Performance Considerations

​Query Optimization

​Connection Pooling

​Next Steps

Testing Guide

Examples & Patterns

Agent Integration

Production Deployment

Your First SQL Tool

Step 1: Create the Configuration File

Step 2: Test Your Tool

Adding Parameters

Parameter Validation

Complete Example: Employee Information Tools

Overview

Source Configuration

Tool 1: String Parameters with Pattern Validation

Tool 2 & 3: String Enum Parameters

Tool 4: Boolean Parameters

Tool 5: Integer Parameters with Aggregations

Tool 6: Array Parameters

Tool 7: Float Parameters

Tool 8: Pagination with Multiple Parameters

Toolset Organization

Running the Example

Parameter Type Summary

SQL Techniques Demonstrated

Common IBM i Patterns

System Information Queries

Library and Object Management

Database Analysis

Advanced Features

Response Formatting

Security Configuration

Error Handling and Validation

Testing and Debugging

Validation Commands

Debug Mode

Common Issues and Solutions

File Organization Best Practices

Single Domain Approach

Multi-Environment Support

Version Control

Performance Considerations

Query Optimization

Connection Pooling

Next Steps