IBM i MCP Server

--- title: "Building SQL Tools" description: "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 # 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 TOOLS_YAML_PATH=my-first-tools.yaml npm run start:http ``` Use the MCP Inspector to test your tool: ```bash 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 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 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 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 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 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 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 { "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 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 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 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 // 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 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 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 { "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 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 // 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 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 // 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 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 npx -y @ibm/ibmi-mcp-server@latest --list-toolsets --tools tools/sample/employee-info.yaml ``` **Start server with specific toolsets:** ```bash # 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 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 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 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 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 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 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 # 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 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 # ❌ 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 # 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 -- 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 # 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 # 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 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 # ✅ 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 # 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>