IBM i MCP Server

--- title: "SQL Tools Overview" description: "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 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 # 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 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 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 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 security: readOnly: true audit: true requiredAuthority: "*AUDIT" scopes: ["security:read"] warning: "Accesses sensitive data" ``` --- ## Loading and Configuration ### Load Specific Toolsets ```bash # 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 # 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 # 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>