Tools Reference

Official Schema: See the JSON Schema definition for the authoritative tool configuration specification.

Tools are individual SQL operations that AI agents can discover and execute. Each tool encapsulates a SQL statement, parameter definitions, validation rules, and execution metadata.

Tool Structure

Every tool definition follows this structure:

tools:
  tool_name:
    source: ibmi-system
    description: "What this tool does and when to use it"
    statement: |
      SELECT * FROM qsys2.system_status_info
    parameters: []
    security: {}
    metadata: {}

Required Fields

All SQL tools must include these fields:

Field	Type	Description
`tool_name`	string (YAML key)	Unique identifier for the tool, used by AI agents to call it
`source`	string	Name of the database connection source (from `sources` section)
`description`	string	Clear explanation of what the tool does, for AI agent consumption
`statement`	string	SQL query to execute when the tool is called

Field Details

tool_name
source
description
statement

Unique identifier for the toolThe tool name is the YAML key and must be unique across all tools.Naming conventions:

Use lowercase with underscores: get_active_jobs
Be descriptive: find_employees_by_department not emp_search
Prefix by domain if needed: perf_system_status, sec_audit_trail

# ✅ Good names
tools:
  get_employee_details:
  find_employees_by_department:
  calculate_employee_bonus:

# ❌ Avoid
tools:
  emp:
  query1:
  temp_tool:

Database connection referenceReferences a source name defined in the sources section.

sources:
  ibmi-system:
    host: ${DB2i_HOST}
    # ...

tools:
  system_status:
    source: ibmi-system  # Must match source key
    description: "Get system performance metrics"
    # ...

Source Reference: The source name must match a key in the sources section. Each tool can only use one source - if you need to query multiple systems, create separate tools.

Tool purpose and usageClear, concise explanation written for AI agent consumption.Best practices:

Be specific: “List active jobs sorted by CPU usage” not “Show jobs”
Include context: “Get employee details including department and manager information”
Mention limitations: “Returns maximum of 100 rows”
Note requirements: “Requires *AUDIT special authority”

# ✅ Good descriptions
description: "Get system performance metrics including CPU usage, memory, and active jobs"
description: "Find employees by department code (A00, B01, etc.) with salary information"
description: "Search for IBM i services by name pattern with optional case-insensitive matching"

# ❌ Avoid vague descriptions
description: "Gets data"
description: "Employee tool"
description: "System query"

SQL query to executeThe SQL statement executed when the tool is called. Use pipe | for multiline.

statement: |
  SELECT job_name, user_name, cpu_used, elapsed_time
  FROM qsys2.active_job_info
  WHERE job_status = 'ACTIVE'
  ORDER BY cpu_used DESC
  FETCH FIRST :limit ROWS ONLY

SQL requirements:

Must be valid IBM i SQL (Db2 for i syntax)
Use :parameter_name syntax for parameter binding
Include FETCH FIRST n ROWS ONLY for result limiting
Test SQL independently before adding to tool

Parameter Binding: ALWAYS use parameter binding (:parameter_name) for dynamic values. NEVER use string concatenation - this prevents SQL injection attacks and ensures proper type handling.

Parameters

Parameters define dynamic inputs for SQL tools. Each parameter specifies type, validation rules, and usage information.

Complete Parameter Guide

See the full Parameter Guide for detailed examples of all parameter types, validation patterns, and best practices for building SQL tools with parameters.

Parameter Structure

parameters:
  - name: employee_id
    type: string
    description: "Employee ID (6 digits)"
    required: true
    pattern: "^[0-9]{6}$"

Parameter Types

String
Integer
Float
Boolean
Array

Text values with validation

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

Validation options:

pattern - Regular expression for validation
minLength - Minimum string length
maxLength - Maximum string length
enum - Fixed list of allowed values
default - Default value if not provided

Example with enum:

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

Whole number values with range validation

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

Validation options:

minimum - Minimum value (inclusive)
maximum - Maximum value (inclusive)
default - Default value if not provided

Usage in SQL:

FETCH FIRST :max_rows ROWS ONLY

Decimal number values

parameters:
  - name: performance_multiplier
    type: float
    description: "Performance rating multiplier (0.0-0.3)"
    required: true
    min: 0.0
    max: 0.3
    default: 0.1

Validation options:

min - Minimum value (inclusive)
max - Maximum value (inclusive)
default - Default value if not provided

Usage in SQL:

SELECT salary * :performance_multiplier AS bonus
FROM employee

True/false values

parameters:
  - name: include_completed
    type: boolean
    description: "Include completed projects (true) or only active (false)"
    default: true

Usage in SQL:

WHERE (:include_completed = 1 OR end_date IS NULL)

SQL Conversion: Boolean parameters are passed as 1 (true) or 0 (false) to SQL. Use = 1 or = 0 in WHERE clauses.

Lists of values

parameters:
  - name: project_ids
    type: array
    itemType: string
    description: "List of project IDs to search for"
    required: true
    minLength: 1
    maxLength: 10

Validation options:

itemType - Type of array elements (string, integer, float, boolean)
minLength - Minimum array length
maxLength - Maximum array length

Usage in SQL:

WHERE project_id IN (:project_ids)

MCP call format:

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

Array Format: Arrays must be passed as JSON arrays (["A", "B"]), not strings or SQL syntax (('A', 'B')).

Optional Parameters

Parameters without required: true or with default values are optional:

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

SQL handling for optional parameters:

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

Security Configuration

Add security controls to tools for enhanced protection:

tools:
  execute_dynamic_sql:
    source: ibmi-system
    description: "Execute dynamic SQL with safety controls"
    statement: |
      SELECT * FROM user_profiles WHERE user_name = :user_name
    security:
      readOnly: true
      maxQueryLength: 15000
      forbiddenKeywords: ["DROP", "DELETE", "UPDATE", "TRUNCATE"]

Security Fields

Field	Type	Default	Description
`readOnly`	boolean	`true`	Restrict to read-only operations (SELECT queries)
`maxQueryLength`	number	`10000`	Maximum SQL query length in characters
`forbiddenKeywords`	array	`[]`	Additional forbidden SQL keywords beyond defaults

readOnly
maxQueryLength
forbiddenKeywords

Mark tools as read-only (SELECT queries only):

security:
  readOnly: true  # Enforce SELECT-only operations

Best Practice: Mark all tools that don’t modify data as readOnly: true. This provides clear documentation and can be used for access control.

Default: true (for safety - tools are read-only by default)

Limit SQL statement length to prevent abuse:

security:
  maxQueryLength: 15000  # Limit statement to 15KB

Default: 10000 charactersUse case: Prevents excessively long or complex queries that could impact performance

Block specific SQL keywords:

security:
  forbiddenKeywords: ["DROP", "DELETE", "UPDATE", "TRUNCATE", "ALTER"]

Default: [] (empty - uses built-in defaults)Use case: Useful for execute_sql tools that accept dynamic SQL. Prevents destructive operations while allowing flexible queries.

These keywords are added to the default forbidden list. The server has built-in protections against destructive operations.

Response Formatting

Control how tool results are formatted for AI agents:

responseFormat

Type: string Options: json (default), markdown

tools:
  markdown_report:
    source: ibmi-system
    description: "Generate formatted system report"
    responseFormat: markdown
    statement: |
      SELECT
        '# System Status Report' AS header,
        '## CPU: ' || cpu_utilization || '%' AS cpu,
        '## Memory: ' || ROUND((used_memory * 100.0 / total_memory), 2) || '%' AS memory
      FROM TABLE(QSYS2.SYSTEM_STATUS()) X

Response types:

json - Structured data (default, best for AI processing)
markdown - Formatted text with markdown syntax

Metadata

Add descriptive metadata for tool organization and discovery:

tools:
  inventory_analysis:
    source: ibmi-system
    description: "Comprehensive inventory analysis report"
    statement: |
      SELECT * FROM inventory_summary
    metadata:
      title: "Inventory Analysis Report"
      version: "2.1.0"
      author: "IBM i Development Team"
      keywords: ["inventory", "analysis", "reporting"]
      lastUpdated: "2024-01-15"
      domain: business
      category: reporting
      environment: production

Metadata Fields

title

Human-readable title for the tool

version

Semantic version for tracking tool changes

author

Individual or team responsible for the tool

keywords

Search keywords for tool discovery

lastUpdated

Date of last modification (ISO format)

domain

Business domain classification (monitoring, security, business, etc.)

Complete Tool Examples

Simple Query (No Parameters)

tools:
  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
    parameters: []

String Parameter with Validation

tools:
  get_employee_details:
    source: ibmi-sample
    description: "Retrieve detailed employee information including department and manager"
    statement: |
      SELECT
        E.EMPNO,
        E.FIRSTNME,
        E.LASTNAME,
        E.JOB,
        E.SALARY,
        D.DEPTNAME,
        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}$"

Multiple Parameters with Different Types

tools:
  search_employees:
    source: ibmi-sample
    description: "Search for employees by name with pagination"
    statement: |
      SELECT
        E.EMPNO,
        E.FIRSTNME,
        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
        minimum: 1
        maximum: 100
      - name: page_number
        type: integer
        description: "Page number (starting from 1)"
        default: 1
        minimum: 1

Tool with Security Configuration

tools:
  user_profile_audit:
    source: ibmi-system
    description: "Audit user profile security settings (requires *AUDIT authority)"
    statement: |
      SELECT
        user_profile_name,
        status,
        previous_signon,
        days_until_password_expires,
        user_class_name,
        group_profile_name
      FROM qsys2.user_info
      WHERE user_profile_name = :user_profile
    parameters:
      - name: user_profile
        type: string
        required: true
        pattern: "^[A-Z][A-Z0-9_]*$"
        maxLength: 10
        description: "User profile name to audit"
    security:
      readOnly: true
      audit: true
      requiredAuthority: "*AUDIT"
      scopes: ["security:audit"]
      warning: "Accesses sensitive user profile information. Requires *AUDIT authority."

Array Parameter Tool

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

Best Practices

SQL Statement Design

Optimization:

Always include FETCH FIRST n ROWS ONLY to limit results
Use LEFT JOIN instead of INNER JOIN when relationships are optional
Add ORDER BY for consistent result ordering
Use column aliases for better AI understanding

Example:

SELECT
  job_name AS "Job Name",
  user_name AS "User",
  cpu_used AS "CPU Time (ms)"
FROM qsys2.active_job_info
WHERE job_status = 'ACTIVE'
ORDER BY cpu_used DESC
FETCH FIRST 100 ROWS ONLY

Parameter Validation

Always validate:

String patterns for IBM i names: ^[A-Z][A-Z0-9_]*$
String lengths: maxLength: 10 for library names
Integer ranges: minimum: 1, maximum: 1000
Required vs optional: Mark appropriately

Example:

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

Description Quality

Write for AI agents:

Be specific about what data is returned
Mention any special authority requirements
Include parameter examples in descriptions
Note any result limits or performance considerations

Good example:

description: "List active jobs sorted by CPU usage. Returns top 100 jobs. Requires connection to production system. Results updated every 5 seconds."

Security Considerations

Mark sensitive tools:

Set audit: true for all tools accessing user data
Use readOnly: true for SELECT-only operations
Document required authorities in security.requiredAuthority
Add warnings for potentially destructive operations

Example:

security:
  readOnly: true
  audit: true
  requiredAuthority: "*SECADM"
  warning: "Accesses security configuration. Use with caution."

Next Steps

Sources Reference

Configure database connection sources

Toolsets Reference

Organize tools into logical groups

Building SQL Tools

Step-by-step guide with examples

Parameter Validation

Deep dive into validation rules

Tool Design Philosophy: Effective tools are discoverable (clear names and descriptions), safe (proper validation and security), and performant (optimized SQL with result limits). Write tools for AI agent consumption, not just human readability.

Getting Started

Core Concepts

Server Configuration

SQL Tools System

Tool Structure

Required Fields

Field Details

Parameters

Complete Parameter Guide

Parameter Structure

Parameter Types

Optional Parameters

Security Configuration

Security Fields

Response Formatting

responseFormat

Metadata

Metadata Fields

Complete Tool Examples

Simple Query (No Parameters)

String Parameter with Validation

Multiple Parameters with Different Types

Tool with Security Configuration

Array Parameter Tool

Best Practices

Next Steps

Sources Reference

Toolsets Reference

Building SQL Tools

Parameter Validation

Getting Started

Core Concepts

Server Configuration

SQL Tools System

​Tool Structure

​Required Fields

​Field Details

​Parameters

Complete Parameter Guide

​Parameter Structure

​Parameter Types

​Optional Parameters

​Security Configuration

​Security Fields

​Response Formatting

​responseFormat

​Metadata

​Metadata Fields

​Complete Tool Examples

​Simple Query (No Parameters)

​String Parameter with Validation

​Multiple Parameters with Different Types

​Tool with Security Configuration

​Array Parameter Tool

​Best Practices

​Next Steps

Sources Reference

Toolsets Reference

Building SQL Tools

Parameter Validation

Tool Structure

Required Fields

Field Details

Parameters

Parameter Structure

Parameter Types

Optional Parameters

Security Configuration

Security Fields

Response Formatting

responseFormat

Metadata

Metadata Fields

Complete Tool Examples

Simple Query (No Parameters)

String Parameter with Validation

Multiple Parameters with Different Types

Tool with Security Configuration

Array Parameter Tool

Best Practices

Next Steps