> ## 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 Examples Guide
> Practical examples and patterns for using parameters in SQL tools with validation, constraints, and best practices.
**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 parameter specifications.
This guide provides practical examples for using parameters in SQL tools. Parameters make tools dynamic and reusable by accepting inputs that are validated and safely bound to SQL statements.
## Quick Reference
**All parameter types and their constraints:**
| Type | Constraints | Use Cases |
| --------- | ------------------------------------------- | ---------------------------------------------- |
| `string` | `minLength`, `maxLength`, `pattern`, `enum` | Library names, object names, search patterns |
| `integer` | `minimum`, `maximum`, `enum` | Row limits, IDs, counts, numeric filters |
| `float` | `min`, `max`, `enum` | Percentages, thresholds, measurements |
| `boolean` | None | Flags, enable/disable options |
| `array` | `minLength`, `maxLength`, `itemType` | Multiple filters, batch operations, IN clauses |
**Common parameter properties:**
| Property | Required | Description |
| ------------- | ----------- | ----------------------------------------------------------------------- |
| `name` | ✅ Yes | Parameter name used in SQL (`:parameter_name`) |
| `type` | ✅ Yes | One of: `string`, `integer`, `float`, `boolean`, `array` |
| `description` | Recommended | AI-facing description with usage guidance |
| `required` | No | Whether parameter must be provided (default: true unless `default` set) |
| `default` | No | Default value when parameter is not provided |
***
## String Parameters
String parameters accept text values with optional length, pattern, and enum constraints.
### Basic String
**Use case:** Simple text input without validation
```yaml theme={null}
parameters:
- name: search_term
type: string
description: "Search term to find in service names. Example: 'ACTIVE_JOB'"
required: true
```
**SQL usage:**
```sql theme={null}
WHERE service_name LIKE '%' || :search_term || '%'
```
**Tool call:**
```json theme={null}
{
"name": "search_services",
"arguments": {
"search_term": "ACTIVE_JOB"
}
}
```
***
### String with Length Constraints
**Use case:** Enforce minimum/maximum length (e.g., IBM i object names)
```yaml theme={null}
parameters:
- name: object_name
type: string
description: "IBM i object name (1-10 characters). Example: 'CUSTFILE', 'MYLIB'"
required: true
minLength: 1
maxLength: 10
```
**Validation:**
* ✅ `"CUSTFILE"` - Valid (8 characters)
* ❌ `""` - Error: Too short (0 \< 1)
* ❌ `"VERYLONGOBJECTNAME"` - Error: Too long (18 > 10)
***
### String with Pattern (Regex)
**Use case:** Enforce specific format (e.g., IBM i naming conventions)
```yaml theme={null}
parameters:
- name: library_name
type: string
description: "IBM i library name (uppercase alphanumeric, starts with letter). Example: 'APPLIB', 'MYLIB'"
required: true
pattern: "^[A-Z][A-Z0-9_]*$"
maxLength: 10
```
**Common patterns:**
```yaml theme={null}
# IBM i library/object name
pattern: "^[A-Z][A-Z0-9_]*$"
# Employee ID (6 digits)
pattern: "^[0-9]{6}$"
# Email address
pattern: "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"
# Phone number (US format)
pattern: "^\\d{3}-\\d{3}-\\d{4}$"
```
**Validation:**
* ✅ `"MYLIB"` - Valid
* ✅ `"APP_LIB"` - Valid
* ❌ `"mylib"` - Error: Doesn't match pattern (lowercase)
* ❌ `"123LIB"` - Error: Doesn't match pattern (starts with number)
***
### String with Enum
**Use case:** Restrict to specific allowed values
```yaml theme={null}
parameters:
- name: object_type
type: string
description: "IBM i object type to filter by"
required: true
enum: ["*FILE", "*PGM", "*SRVPGM", "*DTAARA"]
default: "*FILE"
```
**Validation:**
* ✅ `"*FILE"` - Valid
* ✅ `"*PGM"` - Valid
* ❌ `"*TABLE"` - Error: Not in enum list
**SQL usage:**
```sql theme={null}
WHERE object_type = :object_type
```
**AI Agent Benefit**: Enum parameters are automatically described to AI agents as "Must be one of: '\*FILE', '\*PGM', '\*SRVPGM', '\*DTAARA'" which helps them select valid values.
***
### Optional String
**Use case:** Allow filtering that can be skipped
```yaml theme={null}
parameters:
- name: user_filter
type: string
description: "Optional user name filter. Leave empty to show all users."
required: false
```
**SQL usage with NULL handling:**
```sql theme={null}
WHERE (:user_filter IS NULL OR user_name = :user_filter)
```
**Tool calls:**
```json theme={null}
// With filter
{"user_filter": "JSMITH"}
// Without filter (shows all)
{"user_filter": null}
// or omit entirely: {}
```
***
## Integer Parameters
Integer parameters accept whole numbers with optional min/max constraints.
### Basic Integer
**Use case:** Row limits, counts, IDs
```yaml theme={null}
parameters:
- name: limit
type: integer
description: "Maximum number of rows to return (1-100)"
default: 10
minimum: 1
maximum: 100
```
**SQL usage:**
```sql theme={null}
FETCH FIRST :limit ROWS ONLY
```
**Validation:**
* ✅ `10` - Valid
* ✅ `100` - Valid (at maximum)
* ❌ `0` - Error: Below minimum
* ❌ `150` - Error: Above maximum
* ❌ `"10"` - Error: Must be integer, not string
***
### Pagination Parameters
**Use case:** Page number and size for result pagination
```yaml theme={null}
parameters:
- name: page_size
type: integer
description: "Number of results per page (1-100)"
default: 25
minimum: 1
maximum: 100
- name: page_number
type: integer
description: "Page number to retrieve (starts at 1)"
default: 1
minimum: 1
```
**SQL usage:**
```sql theme={null}
LIMIT :page_size OFFSET (:page_number - 1) * :page_size
```
**Tool call:**
```json theme={null}
{
"page_size": 25,
"page_number": 2 // Gets rows 26-50
}
```
***
### Integer with Enum
**Use case:** Specific numeric choices
```yaml theme={null}
parameters:
- name: priority
type: integer
description: "Priority level (1=High, 2=Medium, 3=Low)"
enum: [1, 2, 3]
default: 2
```
***
## Float Parameters
Float parameters accept decimal numbers for percentages, thresholds, and measurements.
### Basic Float
**Use case:** Percentage multiplier, threshold value
```yaml theme={null}
parameters:
- name: performance_multiplier
type: float
description: "Performance rating multiplier for bonus calculation (0.0-0.3)"
required: true
min: 0.0
max: 0.3
default: 0.1
```
**SQL usage:**
```sql theme={null}
SELECT
employee_id,
salary,
salary * :performance_multiplier AS bonus
FROM employees
```
**Tool call:**
```json theme={null}
{
"performance_multiplier": 0.15 // 15% bonus
}
```
**Validation:**
* ✅ `0.1` - Valid
* ✅ `0.25` - Valid
* ❌ `0.5` - Error: Exceeds maximum (0.5 > 0.3)
* ❌ `-0.1` - Error: Below minimum
***
### Threshold Example
```yaml theme={null}
parameters:
- name: cpu_threshold
type: float
description: "CPU usage threshold percentage (0.0-100.0)"
min: 0.0
max: 100.0
default: 80.0
```
**SQL usage:**
```sql theme={null}
WHERE cpu_utilization >= :cpu_threshold
```
***
## Boolean Parameters
Boolean parameters accept true/false values for flags and options.
### Basic Boolean
**Use case:** Include/exclude certain data
```yaml theme={null}
parameters:
- name: include_completed
type: boolean
description: "Include completed projects (true) or only active projects (false)"
default: true
```
**SQL usage:**
```sql theme={null}
WHERE (:include_completed = 1 OR end_date IS NULL)
```
**Boolean to SQL**: Booleans are passed as `1` (true) or `0` (false) in SQL. Use `= 1` or `= 0` in WHERE clauses.
**Tool calls:**
```json theme={null}
// Show all projects
{"include_completed": true}
// Show only active projects
{"include_completed": false}
```
***
### Boolean Flag Examples
```yaml theme={null}
# Case-sensitive search
- name: case_sensitive
type: boolean
description: "Perform case-sensitive search (true) or case-insensitive (false)"
default: false
# Include inactive records
- name: show_inactive
type: boolean
description: "Include inactive records in results"
default: false
# Verbose output
- name: detailed
type: boolean
description: "Include detailed information in results"
default: false
```
**SQL with boolean:**
```sql theme={null}
WHERE
CASE
WHEN :case_sensitive = 1
THEN name = :search_term
ELSE
UPPER(name) = UPPER(:search_term)
END
```
***
## Array Parameters
Array parameters accept lists of values, commonly used with SQL IN clauses.
### Basic Array
**Use case:** Filter by multiple values
```yaml theme={null}
parameters:
- name: project_ids
type: array
itemType: string
description: "List of project IDs to search for. Example: ['MA2100', 'AD3100', 'PL2100']"
required: true
minLength: 1
maxLength: 10
```
**SQL usage:**
```sql theme={null}
WHERE project_id IN (:project_ids)
```
**Tool call:**
```json theme={null}
{
"project_ids": ["MA2100", "AD3100", "PL2100"]
}
```
**Array Format**: Arrays must be JSON arrays (`["A", "B"]`), NOT strings (`"('A', 'B')"`) or SQL syntax.
**Validation:**
* ✅ `["MA2100"]` - Valid (1 item, within minLength)
* ✅ `["MA2100", "AD3100", "PL2100"]` - Valid (3 items)
* ❌ `[]` - Error: Below minLength (0 \< 1)
* ❌ `["A", "B", ... "K"]` (11 items) - Error: Exceeds maxLength
***
### Array with Different Item Types
**Integer array:**
```yaml theme={null}
- name: employee_ids
type: array
itemType: integer
description: "List of employee IDs"
minLength: 1
maxLength: 50
```
**Tool call:**
```json theme={null}
{"employee_ids": [1001, 1002, 1003]}
```
**Boolean array:**
```yaml theme={null}
- name: status_flags
type: array
itemType: boolean
description: "List of status flags"
```
**Tool call:**
```json theme={null}
{"status_flags": [true, false, true]}
```
***
## Validation and Error Handling
### Validation Process
Parameters are validated in this order:
1. **Type Check** - Ensure value matches declared type
2. **Required Check** - Ensure required parameters are provided
3. **Constraint Check** - Validate min/max, length, pattern, enum
4. **SQL Binding** - Safely bind validated parameters to SQL
### Common Validation Errors
**Error:** `Expected integer, got string for parameter 'limit'`
**Cause:** Passing wrong type
```json theme={null}
// ❌ Wrong
{"limit": "10"}
// ✅ Correct
{"limit": 10}
```
**Error:** `Required parameter 'employee_id' is missing`
**Cause:** Not providing a required parameter
```yaml theme={null}
parameters:
- name: employee_id
type: string
required: true # Must be provided
```
```json theme={null}
// ❌ Missing required parameter
{}
// ✅ Provided
{"employee_id": "000010"}
```
**Error:** `String length 18 exceeds maximum 10 for parameter 'library_name'`
**Cause:** Value violates constraint
```yaml theme={null}
parameters:
- name: library_name
type: string
maxLength: 10
```
```json theme={null}
// ❌ Too long
{"library_name": "VERYLONGLIBRARYNAME"}
// ✅ Valid
{"library_name": "MYLIB"}
```
**Error:** `Value does not match pattern '^[A-Z][A-Z0-9_]*$' for parameter 'library_name'`
**Cause:** Value doesn't match regex pattern
```yaml theme={null}
parameters:
- name: library_name
type: string
pattern: "^[A-Z][A-Z0-9_]*$"
```
```json theme={null}
// ❌ Doesn't match (lowercase)
{"library_name": "mylib"}
// ✅ Matches
{"library_name": "MYLIB"}
```
**Error:** `Value must be one of: *FILE, *PGM, *SRVPGM for parameter 'object_type'`
**Cause:** Value not in enum list
```yaml theme={null}
parameters:
- name: object_type
type: string
enum: ["*FILE", "*PGM", "*SRVPGM"]
```
```json theme={null}
// ❌ Not in list
{"object_type": "*TABLE"}
// ✅ Valid
{"object_type": "*FILE"}
```
***
## Best Practices
### 1. Write AI-Friendly Descriptions
The `description` field is shown to AI agents. Make it helpful:
```yaml theme={null}
# ✅ Good - includes examples and format
description: "Employee ID (6 digits). Example: '000010', '000250'"
# ❌ Vague
description: "Employee ID"
```
### 2. Use Appropriate Constraints
Match constraints to your data requirements:
```yaml theme={null}
# IBM i library name
- name: library
type: string
pattern: "^[A-Z][A-Z0-9_]*$"
maxLength: 10
# Row limit
- name: limit
type: integer
minimum: 1
maximum: 1000
default: 50
# Search term (at least 2 characters to avoid broad searches)
- name: search
type: string
minLength: 2
maxLength: 100
```
### 3. Provide Sensible Defaults
For optional parameters, include defaults:
```yaml theme={null}
- name: page_size
type: integer
default: 25
minimum: 1
maximum: 100
- name: include_inactive
type: boolean
default: false
```
### 4. Use Enums for Fixed Choices
When values are limited, use enums:
```yaml theme={null}
# Status values
- name: status
type: string
enum: ["ACTIVE", "INACTIVE", "PENDING"]
default: "ACTIVE"
# Priority levels
- name: priority
type: integer
enum: [1, 2, 3]
default: 2
```
### 5. Handle Optional Parameters in SQL
Use NULL checks for optional parameters:
```sql theme={null}
-- Optional string filter
WHERE (:user_filter IS NULL OR user_name = :user_filter)
-- Optional integer threshold
WHERE (:min_salary IS NULL OR salary >= :min_salary)
-- Optional boolean flag with special value
WHERE (:department_id = '*ALL' OR department = :department_id)
```
***
## Security: Parameter Binding
**Critical**: Parameters are automatically bound using prepared statements. This prevents SQL injection attacks. NEVER concatenate parameters into SQL strings.
**✅ Safe (parameter binding):**
```yaml theme={null}
statement: |
SELECT * FROM employees
WHERE employee_id = :employee_id
```
**❌ Dangerous (string concatenation):**
```yaml theme={null}
# DO NOT DO THIS
statement: |
SELECT * FROM employees
WHERE employee_id = '${employee_id}'
```
**How parameter binding works:**
1. SQL statement is prepared with placeholders: `SELECT * FROM employees WHERE employee_id = ?`
2. Parameters are validated against constraints
3. Parameters are bound separately from the SQL structure
4. Database executes the prepared statement with bound parameters
This ensures the SQL structure cannot be modified by user input, preventing injection attacks entirely.
***
## Complete Examples
### Example 1: Employee Search Tool
```yaml theme={null}
tools:
search_employees:
source: ibmi-sample
description: "Search for employees by name with pagination"
parameters:
- name: name_search
type: string
description: "Name to search for (partial match, minimum 2 characters). Example: 'Smith', 'John'"
required: true
minLength: 2
maxLength: 50
- name: page_size
type: integer
description: "Number of results per page (1-100)"
default: 25
minimum: 1
maximum: 100
- name: page_number
type: integer
description: "Page number to retrieve (starts at 1)"
default: 1
minimum: 1
statement: |
SELECT empno, firstnme, lastname, job, workdept
FROM SAMPLE.EMPLOYEE
WHERE UPPER(firstnme) LIKE UPPER('%' || :name_search || '%')
OR UPPER(lastname) LIKE UPPER('%' || :name_search || '%')
ORDER BY lastname, firstnme
LIMIT :page_size OFFSET (:page_number - 1) * :page_size
```
### Example 2: Project Team Finder
```yaml theme={null}
tools:
find_project_team:
source: ibmi-sample
description: "Find all employees working on specific projects"
parameters:
- name: project_ids
type: array
itemType: string
description: "List of project IDs. Example: ['MA2100', 'AD3100']"
required: true
minLength: 1
maxLength: 10
- name: include_completed
type: boolean
description: "Include employees with completed assignments (true) or only active assignments (false)"
default: false
statement: |
SELECT E.empno, E.firstnme, E.lastname, EPA.projno, EPA.emstdate, EPA.emendate
FROM SAMPLE.EMPPROJACT EPA
JOIN SAMPLE.EMPLOYEE E ON EPA.empno = E.empno
WHERE EPA.projno IN (:project_ids)
AND (:include_completed = 1 OR EPA.emendate IS NULL)
ORDER BY EPA.projno, E.lastname
```
### Example 3: System Monitoring Tool
```yaml theme={null}
tools:
monitor_cpu_usage:
source: ibmi-system
description: "Monitor jobs exceeding CPU threshold"
parameters:
- name: cpu_threshold
type: float
description: "CPU usage threshold in milliseconds (minimum to report)"
default: 1000.0
min: 0.0
- name: limit
type: integer
description: "Maximum number of jobs to return (1-500)"
default: 50
minimum: 1
maximum: 500
- name: job_type
type: string
description: "Job type filter"
enum: ["BATCH", "INTERACTIVE", "AUTOSTART", "*ALL"]
default: "*ALL"
statement: |
SELECT job_name, user_name, cpu_used, job_type, job_status
FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) A
WHERE cpu_used >= :cpu_threshold
AND (:job_type = '*ALL' OR job_type = :job_type)
ORDER BY cpu_used DESC
FETCH FIRST :limit ROWS ONLY
```
***
## Next Steps
Complete tool configuration reference
Step-by-step guide to creating tools
Database connection configuration
Organizing tools into groups
**Parameter Design Philosophy**: Good parameters make tools flexible without sacrificing safety. Use constraints to enforce data quality, provide defaults for convenience, and write clear descriptions to guide AI agents in using your tools correctly.