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

# Building SQL Tools

> Step-by-step guide to creating custom YAML SQL tools for IBM i database operations with examples and best practices.

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.

<Note>
  **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.
</Note>

## 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`:

```yaml theme={null}
# 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:

```bash theme={null}
TOOLS_YAML_PATH=my-first-tools.yaml npm run start:http
```

Use the MCP Inspector to test your tool:

```bash theme={null}
npm run mcp-inspector
```

<Note>
  **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.
</Note>

## Adding Parameters

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

<Card title="Parameter Examples Guide" icon="book-open" href="/sql-tools/parameter-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.
</Card>

<Tabs>
  <Tab title="Basic Parameters">
    ```yaml theme={null}
    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
    ```
  </Tab>

  <Tab title="Optional Parameters">
    ```yaml theme={null}
    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
    ```
  </Tab>

  <Tab title="Enum Parameters">
    ```yaml theme={null}
    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
    ```
  </Tab>
</Tabs>

### Parameter Validation

The MCP server automatically validates parameters before executing SQL:

```yaml theme={null}
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)"
```

<Warning>
  **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.
</Warning>

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

<Info>
  **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.
</Info>

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

```yaml theme={null}
sources:
  ibmi-sample:
    host: ${DB2i_HOST}
    user: ${DB2i_USER}
    password: ${DB2i_PASS}
    port: 8076
    ignore-unauthorized: true
```

<Note>
  **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.
</Note>

***

### Tool 1: String Parameters with Pattern Validation

**Tool:** `get_employee_details`

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

```yaml theme={null}
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:**

```json theme={null}
{
  "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

<Tabs>
  <Tab title="Department Filter">
    ```yaml theme={null}
    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"]
    ```
  </Tab>

  <Tab title="Job Title Filter">
    ```yaml theme={null}
    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"]
    ```
  </Tab>
</Tabs>

<Note>
  **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.
</Note>

***

### Tool 4: Boolean Parameters

**Tool:** `get_employee_projects`

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

```yaml theme={null}
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:**

```json theme={null}
// 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

```yaml theme={null}
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

<Note>
  **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.
</Note>

***

### Tool 6: Array Parameters

**Tool:** `find_project_team_members`

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

```yaml theme={null}
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:**

```json theme={null}
{
  "name": "find_project_team_members",
  "arguments": {
    "project_ids": ["MA2100", "AD3100", "PL2100"]
  }
}
```

<Warning>
  **Array Input Format:** Arrays must be passed as **JSON arrays**, not strings:

  * ✅ Correct: `{"project_ids": ["MA2100", "AD3100"]}`
  * ❌ Incorrect: `{"project_ids": "('MA2100', 'AD3100')"}`
</Warning>

***

### Tool 7: Float Parameters

**Tool:** `calculate_employee_bonus`

**Demonstrates:** Float parameters for decimal calculations, mathematical operations

```yaml theme={null}
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:**

```json theme={null}
// 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

```yaml theme={null}
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:**

```json theme={null}
// 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:

```yaml theme={null}
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
```

<Note>
  **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.
</Note>

***

### Running the Example

**List available toolsets:**

```bash theme={null}
npx -y @ibm/ibmi-mcp-server@latest --list-toolsets --tools tools/sample/employee-info.yaml
```

**Start server with specific toolsets:**

```bash theme={null}
# 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:

<CardGroup cols={2}>
  <Card title="Joins" icon="link">
    * **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
  </Card>

  <Card title="Aggregations" icon="chart-column">
    * **COUNT:** Counting records
    * **AVG, MIN, MAX, SUM:** Statistical calculations
    * **GROUP BY:** Grouping for aggregates
  </Card>

  <Card title="Filtering" icon="filter">
    * **WHERE clauses:** Basic and conditional filtering
    * **IN clauses:** Array-based filtering
    * **NULL handling:** Optional parameter patterns
    * **Special values:** `*ALL` pattern for "all records"
  </Card>

  <Card title="Search & Pagination" icon="magnifying-glass">
    * **LIKE with wildcards:** Partial matching
    * **UPPER():** Case-insensitive search
    * **LIMIT/OFFSET:** Pagination pattern
    * **ORDER BY:** Sorting results
  </Card>
</CardGroup>

<Success>
  **Complete Reference Implementation**: This file serves as a comprehensive template for building production-ready IBM i MCP tools. Use it as a starting point for your own tool development, adapting the patterns to your specific database schema and business requirements.
</Success>

***

## Common IBM i Patterns

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

### System Information Queries

```yaml theme={null}
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

```yaml theme={null}
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

```yaml theme={null}
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:

```yaml theme={null}
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:

```yaml theme={null}
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:

```yaml theme={null}
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:

```bash theme={null}
# 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"
```

<Note>
  For complete testing strategies including MCP Inspector usage, see the [Quick Start Guide](/quickstart#step-4-test-with-mcp-inspector-5-minutes). For production testing, refer to the [Production Deployment](/deployment/production) guide.
</Note>

### Debug Mode

Enable debug logging to troubleshoot tool execution:

```bash theme={null}
MCP_LOG_LEVEL=debug TOOLS_YAML_PATH=my-tools.yaml npm run start:http
```

### Common Issues and Solutions

<AccordionGroup>
  <Accordion title="Parameter Binding Errors" icon="exclamation-triangle">
    **Problem**: `Parameter :parameter_name not found in statement`

    **Solution**: Ensure parameter names in the `statement` match exactly with parameter definitions:

    ```yaml theme={null}
    # ❌ 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
    ```
  </Accordion>

  <Accordion title="SQL Syntax Errors" icon="code">
    **Problem**: SQL statement fails to execute

    **Solution**: Test SQL separately in a DB2 client first:

    ```bash theme={null}
    # Test in IBM i ACS or other SQL client
    SELECT * FROM qsys2.active_job_info
    WHERE user_name = 'TESTUSER'
    FETCH FIRST 10 ROWS ONLY;
    ```
  </Accordion>

  <Accordion title="Authority Issues" icon="lock">
    **Problem**: `SQL0551: Not authorized to object`

    **Solution**: Verify user has appropriate authorities:

    ```sql theme={null}
    -- Check object authorities
    SELECT * FROM qsys2.object_privileges
    WHERE object_schema = 'QSYS2'
      AND object_name = 'ACTIVE_JOB_INFO'
      AND grantee = 'YOUR_USER';
    ```
  </Accordion>

  <Accordion title="Connection Issues" icon="wifi">
    **Problem**: Cannot connect to IBM i system

    **Solution**: Verify connection parameters and Mapepire daemon:

    ```bash theme={null}
    # Check if Mapepire daemon is running
    ping your-ibmi-host
    telnet your-ibmi-host 8076
    ```
  </Accordion>
</AccordionGroup>

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

```yaml theme={null}
# 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:

```yaml theme={null}
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:

```yaml theme={null}
# ✅ 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:

```yaml theme={null}
# 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

<CardGroup cols={2}>
  <Card title="Testing Guide" icon="flask" href="/sql-tools/testing">
    Learn how to test and debug your SQL tools effectively
  </Card>

  <Card title="Examples & Patterns" icon="lightbulb" href="/sql-tools/examples">
    Explore real-world examples and advanced patterns
  </Card>

  <Card title="Agent Integration" icon="robot" href="/agents/building-agents">
    Build AI agents that use your custom SQL tools
  </Card>

  <Card title="Production Deployment" icon="server" href="/deployment/production">
    Deploy your tools to production with monitoring
  </Card>
</CardGroup>

<Note>
  **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.
</Note>