Tools Reference

Complete reference for defining SQL tools including parameters, validation, security, and response formatting.

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

Unique identifier for the tool

The 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:

source

Database connection reference

References 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.

description

Tool purpose and usage

Clear, 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"

statement

SQL query to execute

The 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

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"

Integer

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

Float

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

Boolean

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.

Array

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

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)

maxQueryLength

Limit SQL statement length to prevent abuse:

security:
  maxQueryLength: 15000  # Limit statement to 15KB

Default: 10000 characters

Use case: Prevents excessively long or complex queries that could impact performance

forbiddenKeywords

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

---

Fetch-Row Controls

Control how many rows are fetched from the database per tool call. These are distinct from maxDisplayRows, which only truncates the rendered markdown table.

Database vs. display limits: rowsToFetch / fetchAllRows change how many rows the server pulls from Db2 for i. maxDisplayRows only affects how many rows appear in the formatted output. A tool can fetch 500 rows from the database but render only the first 100 in markdown.

Fields

The two fields compose: fetchAllRows is the pagination policy; rowsToFetch, when set, is the per-fetch size in pagination mode, or a single-shot row cap when fetchAllRows is off.

Field	Type	Default	Description
rowsToFetch	integer (≥ 1)	100 (mapepire default)	With fetchAllRows: true, the number of rows per fetchMore call. Without fetchAllRows, a single-shot cap applied to FETCH FIRST :limit ROWS ONLY-style queries.
fetchAllRows	boolean	false	When true, paginate until the database reports is_done or the safety ceiling (IBMI_PAGINATION_MAX_ROWS, default 30000) is reached.

Composition

Config	Behavior
rowsToFetch: N alone	Single-shot execute(N) — up to N rows, one round-trip
fetchAllRows: true alone	Paginate with IBMI_PAGINATION_DEFAULT_PAGE_SIZE (default 1000) per fetch
fetchAllRows: true, rowsToFetch: N	Paginate with N rows per fetch — tune per tool for wide rows

When the paginated result hits IBMI_PAGINATION_MAX_ROWS, the server truncates the rows returned and emits a warning log. The CLI surfaces the truncation in the output footer so callers know the result was clipped.

Context-bloat warning: Large result sets consume LLM context quickly. Prefer rowsToFetch with a deliberate small value; only use fetchAllRows for small catalogs or when the caller has explicitly requested a full dump.

rowsToFetch (single-shot)

Lift the 100-row cap for a single call

tools:
  list_customers:
    source: ibmi-system
    description: "List up to 500 customers"
    rowsToFetch: 500          # lets FETCH FIRST :limit ROWS ONLY actually return 500
    statement: |
      SELECT ID, NAME FROM MYLIB.CUSTOMERS
      FETCH FIRST :limit ROWS ONLY
    parameters:
      - name: limit
        type: integer
        default: 500
        minimum: 1
        maximum: 500

Use when:

• Your FETCH FIRST :limit ROWS ONLY clause needs more than 100 rows
• You know the expected result size and want a predictable ceiling
• You want row-count safety without running paginated fetches

Setting rowsToFetch alone does not automatically raise your SQL’s FETCH FIRST clause — you still need a parameter or literal that matches. rowsToFetch is the ceiling at the driver level.

fetchAllRows (paginate)

Fetch everything (small catalogs only)

tools:
  list_all_schemas:
    source: ibmi-system
    description: "List every schema (small catalog)"
    fetchAllRows: true        # paginates until is_done, bounded by IBMI_PAGINATION_MAX_ROWS
    statement: |
      SELECT SCHEMA_NAME FROM QSYS2.SYSSCHEMAS
      ORDER BY SCHEMA_NAME

Use when:

• The result set is known to be small (reference tables, catalogs, enums)
• The caller explicitly asks for a complete dump
• You don’t know the result size up front but want completeness

Paginated fetches are bounded by IBMI_PAGINATION_MAX_ROWS (default 30000). Beyond that the server truncates and flags the result — do not rely on fetchAllRows for true bulk exports.

Paginate with custom page size

Compose both fields for wide-row tables

tools:
  export_all_columns:
    source: ibmi-system
    description: "Stream every column of every table in SAMPLE"
    fetchAllRows: true        # paginate until exhausted
    rowsToFetch: 500          # 500 rows per fetchMore call
    statement: |
      SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, LENGTH
      FROM QSYS2.SYSCOLUMNS2
      WHERE TABLE_SCHEMA = 'SAMPLE'

Use when:

• The result set is large and you need a smaller per-fetch page (wide rows, memory-sensitive environments)
• The default page size (1000) produces fetches that are too large for your link
• You want explicit control over the WebSocket round-trip/memory tradeoff per tool

Both fields working together is the most expressive option: policy (paginate) and size (per-fetch page) are independent knobs. rowsToFetch without fetchAllRows stays a single-shot cap.

Default (neither)

Backward-compatible 100-row cap

tools:
  recent_jobs:
    source: ibmi-system
    description: "Recent active jobs (up to 100)"
    statement: |
      SELECT JOB_NAME, USER_NAME, CPU_USED
      FROM QSYS2.ACTIVE_JOB_INFO
      ORDER BY CPU_USED DESC
    # No rowsToFetch / fetchAllRows → 100 rows

Tools with neither property set fetch up to 100 rows — the mapepire driver default. This is the pre-existing behavior and requires no changes to keep working.

---

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.)

category

Functional category within domain

environment

Target environment (production, development, testing)

---

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.