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

# SQL Tools Overview

> Learn how YAML-defined SQL tools enable rapid development of IBM i database operations without writing TypeScript code.

The IBM i MCP Server's SQL Tools system allows you to create powerful database operations using simple YAML configurations. Define SQL queries in YAML files, and the server automatically converts them into AI-accessible tools with parameter validation, security controls, and response formatting.

**No TypeScript required** - SQL professionals can build sophisticated IBM i tools using familiar SQL syntax and simple YAML structure.

***

## Pre-Built Tool Categories

The `tools/` directory contains pre-built tool configurations organized by category:

| Directory                                                                              | Category               | Description                                                                        | Key Tools                                                          |
| -------------------------------------------------------------------------------------- | ---------------------- | ---------------------------------------------------------------------------------- | ------------------------------------------------------------------ |
| [**sample/**](https://github.com/IBM/ibmi-mcp-server/tree/main/tools/sample)           | Sample Data            | Demonstration tools using IBM i SAMPLE schema (employee, department, project data) | Employee lookup, department analysis, project management           |
| [**sys-admin/**](https://github.com/IBM/ibmi-mcp-server/tree/main/tools/sys-admin)     | System Administration  | High-level system service discovery and metadata exploration                       | Service catalogs, schema browsing, example queries                 |
| [**security/**](https://github.com/IBM/ibmi-mcp-server/tree/main/tools/security)       | Security Analysis      | Library list security assessment and vulnerability detection                       | Library list configuration, authority checks, security analysis    |
| [**performance/**](https://github.com/IBM/ibmi-mcp-server/tree/main/tools/performance) | Performance Monitoring | System performance metrics and resource utilization                                | System status, active jobs, memory pools, HTTP server stats        |
| [**developer/**](https://github.com/IBM/ibmi-mcp-server/tree/main/tools/developer)     | Development Tools      | Object statistics and dependency analysis for developers                           | Recently used objects, stale object detection, dependency tracking |

<Tip>
  **List Available Toolsets:**

  ```bash theme={null}
  npx -y @ibm/ibmi-mcp-server@latest --list-toolsets --tools tools
  ```
</Tip>

***

## YAML File Format

SQL tools are defined in YAML files with three main sections:

<CardGroup cols={3}>
  <Card title="Sources" icon="database" href="/sql-tools/sources">
    **Database connections**

    Define IBM i system connections with credentials, ports, and connection options

    [Sources Reference →](/sql-tools/sources)
  </Card>

  <Card title="Tools" icon="wrench" href="/sql-tools/tools">
    **SQL operations**

    Individual queries with parameters, validation, and security controls

    [Tools Reference →](/sql-tools/tools)
  </Card>

  <Card title="Toolsets" icon="layer-group" href="/sql-tools/toolsets">
    **Logical groups**

    Organize related tools for discovery and selective loading

    [Toolsets Reference →](/sql-tools/toolsets)
  </Card>
</CardGroup>

### Basic Structure

```yaml theme={null}
# 1. Sources - Database connections
sources:
  ibmi-system:
    host: ${DB2i_HOST}
    user: ${DB2i_USER}
    password: ${DB2i_PASS}
    port: 8076

# 2. Tools - SQL operations
tools:
  system_status:
    source: ibmi-system
    description: "Get system performance metrics"
    statement: |
      SELECT * FROM TABLE(QSYS2.SYSTEM_STATUS()) X
    parameters: []

# 3. Toolsets - Logical groupings
toolsets:
  performance_monitoring:
    title: "Performance Monitoring"
    description: "System performance and resource tools"
    tools: [system_status]
```

<Note>
  **Why YAML?** Separating SQL definitions from application code enables SQL professionals to build and maintain tools without TypeScript knowledge while ensuring security through parameter validation and prepared statements.
</Note>

## Key Concepts

### Sources - Database Connections

Define reusable connection configurations with environment variables for security:

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

**Key features:**

* Environment variable substitution for credentials
* Multiple environment support (dev, test, prod)
* Connection pooling and timeouts
* SSL/TLS configuration

<Card title="Complete Sources Reference" icon="database" href="/sql-tools/sources">
  Learn about all source configuration options, security best practices, and troubleshooting →
</Card>

***

### Tools - SQL Operations

Individual SQL queries with parameter validation and security controls:

```yaml theme={null}
tools:
  active_job_info:
    source: ibmi-system
    description: "Find top CPU consumers"
    parameters:
      - name: limit
        type: integer
        default: 10
        minimum: 1
        maximum: 100
    statement: |
      SELECT CPU_TIME, JOB_NAME FROM
      TABLE(QSYS2.ACTIVE_JOB_INFO()) A
      ORDER BY CPU_TIME DESC
      FETCH FIRST :limit ROWS ONLY
```

**Parameter types:**

* **String** - Pattern validation, length constraints, enums
* **Integer** - Range validation, defaults
* **Float** - Decimal numbers for calculations
* **Boolean** - True/false flags
* **Array** - Lists of values for IN clauses

<Card title="Complete Tools Reference" icon="wrench" href="/sql-tools/tools">
  Explore all tool configuration options, parameter types, security features, and examples →
</Card>

***

### Toolsets - Logical Organization

Group related tools for discovery and selective loading:

```yaml theme={null}
toolsets:
  performance_monitoring:
    title: "Performance Monitoring"
    description: "System performance and resource tools"
    tools:
      - system_status
      - active_job_info
      - memory_pools
```

**Organization strategies:**

* By functional domain (monitoring, security, reporting)
* By business process (onboarding, payroll, inventory)
* By user role (developer, DBA, auditor)
* By environment (production, development, testing)

<Card title="Complete Toolsets Reference" icon="layer-group" href="/sql-tools/toolsets">
  Learn about toolset organization, selective loading, and best practices →
</Card>

***

## Security & Validation

### Automatic Security

* **SQL Injection Protection** - All parameters use prepared statements
* **Type Validation** - Parameters validated against defined schemas
* **IBM i Authorities** - Queries respect object-level permissions
* **Audit Logging** - Optional detailed execution logs

### Security Configuration

Mark sensitive tools for enhanced protection:

```yaml theme={null}
security:
  readOnly: true
  audit: true
  requiredAuthority: "*AUDIT"
  scopes: ["security:read"]
  warning: "Accesses sensitive data"
```

***

## Loading and Configuration

### Load Specific Toolsets

```bash theme={null}
# Load single toolset
npx -y @ibm/ibmi-mcp-server@latest --tools tools/ --toolsets performance_monitoring

# Load multiple toolsets
npx -y @ibm/ibmi-mcp-server@latest --tools tools/ --toolsets performance_monitoring,security_audit
```

### Environment Variables

```bash theme={null}
# Development environment
DB2i_HOST=ibmi-dev.local
DB2i_USER=DEVUSER
DB2i_PASS=DevPassword

# Load specific toolsets
SELECTED_TOOLSETS=development_full,testing_validation
```

***

## Complete Example

A simple but complete YAML configuration:

```yaml theme={null}
# 1. Define database connection
sources:
  ibmi-system:
    host: ${DB2i_HOST}
    user: ${DB2i_USER}
    password: ${DB2i_PASS}
    port: 8076

# 2. Create SQL tools
tools:
  system_status:
    source: ibmi-system
    description: "Overall system performance metrics"
    statement: |
      SELECT * FROM TABLE(QSYS2.SYSTEM_STATUS()) X
    parameters: []

  active_job_info:
    source: ibmi-system
    description: "Find top CPU consumers"
    parameters:
      - name: limit
        type: integer
        default: 10
        minimum: 1
        maximum: 100
    statement: |
      SELECT CPU_TIME, JOB_NAME, USER_NAME
      FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) A
      ORDER BY CPU_TIME DESC
      FETCH FIRST :limit ROWS ONLY

# 3. Organize into toolsets
toolsets:
  performance_monitoring:
    title: "Performance Monitoring"
    description: "System performance and resource tools"
    tools:
      - system_status
      - active_job_info
```

<Note>
  **Complete Examples**: See the [Building SQL Tools](/sql-tools/building-tools) guide for the comprehensive `employee-info.yaml` example that demonstrates all parameter types and SQL patterns in a production-ready configuration.
</Note>

***

## Next Steps

<CardGroup cols={3}>
  <Card title="Sources Reference" icon="database" href="/sql-tools/sources">
    Database connection configuration
  </Card>

  <Card title="Tools Reference" icon="wrench" href="/sql-tools/tools">
    SQL tool definitions and parameters
  </Card>

  <Card title="Toolsets Reference" icon="layer-group" href="/sql-tools/toolsets">
    Organizing tools into groups
  </Card>
</CardGroup>

<CardGroup cols={2}>
  <Card title="Building SQL Tools" icon="hammer" href="/sql-tools/building-tools">
    Step-by-step guide with complete examples
  </Card>

  <Card title="Configuration Guide" icon="gear" href="/configuration">
    Server configuration and environment variables
  </Card>
</CardGroup>

<Note>
  **Design Philosophy**: YAML SQL tools democratize AI agent development for IBM i environments. SQL professionals build sophisticated tools using familiar SQL syntax, while the MCP server handles AI integration, security, parameter validation, and protocol management automatically.
</Note>