> ## Documentation Index
> Fetch the complete documentation index at: https://ibm-d95bab6e.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Tools Reference
> Complete reference for defining SQL tools including parameters, validation, security, and response formatting.
**Official Schema**: See the [JSON Schema definition](https://github.com/IBM/ibmi-mcp-server/blob/main/packages/server/src/ibmi-mcp-server/schemas/json/sql-tools-config.json) 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:
```yaml theme={null}
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
**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`
```yaml theme={null}
# ✅ Good names
tools:
get_employee_details:
find_employees_by_department:
calculate_employee_bonus:
# ❌ Avoid
tools:
emp:
query1:
temp_tool:
```
**Database connection reference**
References a source name defined in the `sources` section.
```yaml theme={null}
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 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"
```yaml theme={null}
# ✅ 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 execute**
The SQL statement executed when the tool is called. Use pipe `|` for multiline.
```yaml theme={null}
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.
**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
```yaml theme={null}
parameters:
- name: employee_id
type: string
description: "Employee ID (6 digits)"
required: true
pattern: "^[0-9]{6}$"
```
### Parameter Types
**Text values with validation**
```yaml theme={null}
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:**
```yaml theme={null}
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**
```yaml theme={null}
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:**
```sql theme={null}
FETCH FIRST :max_rows ROWS ONLY
```
**Decimal number values**
```yaml theme={null}
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:**
```sql theme={null}
SELECT salary * :performance_multiplier AS bonus
FROM employee
```
**True/false values**
```yaml theme={null}
parameters:
- name: include_completed
type: boolean
description: "Include completed projects (true) or only active (false)"
default: true
```
**Usage in SQL:**
```sql theme={null}
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**
```yaml theme={null}
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:**
```sql theme={null}
WHERE project_id IN (:project_ids)
```
**MCP call format:**
```json theme={null}
{
"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:
```yaml theme={null}
parameters:
- name: user_filter
type: string
required: false
description: "Optional user name filter"
```
**SQL handling for optional parameters:**
```sql theme={null}
WHERE (:user_filter IS NULL OR user_name = :user_filter)
```
***
## Security Configuration
Add security controls to tools for enhanced protection:
```yaml theme={null}
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 |
**Mark tools as read-only (SELECT queries only):**
```yaml theme={null}
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:**
```yaml theme={null}
security:
maxQueryLength: 15000 # Limit statement to 15KB
```
**Default:** `10000` characters
**Use case:** Prevents excessively long or complex queries that could impact performance
**Block specific SQL keywords:**
```yaml theme={null}
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`
```yaml theme={null}
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`](/sql-tools/output-formats#maximum-display-rows), 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.
**Lift the 100-row cap for a single call**
```yaml theme={null}
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.
**Fetch everything (small catalogs only)**
```yaml theme={null}
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.
**Compose both fields for wide-row tables**
```yaml theme={null}
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.
**Backward-compatible 100-row cap**
```yaml theme={null}
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:
```yaml theme={null}
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
Human-readable title for the tool
Semantic version for tracking tool changes
Individual or team responsible for the tool
Search keywords for tool discovery
Date of last modification (ISO format)
Business domain classification (monitoring, security, business, etc.)
Functional category within domain
Target environment (production, development, testing)
***
## Complete Tool Examples
### Simple Query (No Parameters)
```yaml theme={null}
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
```yaml theme={null}
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
```yaml theme={null}
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
```yaml theme={null}
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
```yaml theme={null}
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
**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:**
```sql theme={null}
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
```
**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:**
```yaml theme={null}
parameters:
- name: library
type: string
required: true
pattern: "^[A-Z][A-Z0-9_]*$"
maxLength: 10
description: "IBM i library name (uppercase, max 10 chars)"
```
**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:**
```yaml theme={null}
description: "List active jobs sorted by CPU usage. Returns top 100 jobs. Requires connection to production system. Results updated every 5 seconds."
```
**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:**
```yaml theme={null}
security:
readOnly: true
audit: true
requiredAuthority: "*SECADM"
warning: "Accesses security configuration. Use with caution."
```
***
## Next Steps
Configure database connection sources
Organize tools into logical groups
Step-by-step guide with examples
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.