Building SQL Tools

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:

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

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.

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.

Getting Started

Core Concepts

SQL Tools System

Agent Development

Building SQL Tools

Building SQL Tools

Your First SQL Tool

Step 1: Create the Configuration File

Step 2: Test Your Tool

Adding Parameters

Parameter Validation

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

Getting Started

Core Concepts

SQL Tools System

Agent Development

​Building SQL Tools

​Your First SQL Tool

​Step 1: Create the Configuration File

​Step 2: Test Your Tool

​Adding Parameters

​Parameter Validation

​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

Building SQL Tools

Your First SQL Tool

Step 1: Create the Configuration File

Step 2: Test Your Tool

Adding Parameters

Parameter Validation

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