SQL Tool Parameters

SQL tools can accept parameters to make queries dynamic and reusable. All parameters are validated before execution and bound securely to prevent SQL injection. This guide covers all parameter types, constraints, and best practices.

Parameters are defined in the parameters array of a tool definition and referenced in SQL statements using the :parameter_name syntax.

Parameter Types

The IBM i MCP Server supports five parameter types:

Type	Description	Use Cases	Constraints Available
`string`	Text values	Library names, object names, patterns	`minLength`, `maxLength`, `pattern`, `enum`
`integer`	Whole numbers	Row limits, IDs, counts	`min`, `max`, `enum`
`float`	Decimal numbers	Thresholds, percentages, measurements	`min`, `max`, `enum`
`boolean`	True/false values	Flags, enable/disable options	None (inherently constrained)
`array`	List of values	Multiple filters, batch operations	`minLength`, `maxLength`, `itemType` (required)

Parameter Structure

Each parameter requires a minimum of name and type, with optional properties for validation and documentation:

parameters:
  - name: parameter_name      # Required: Name used in SQL statement
    type: string              # Required: Data type
    description: "..."        # Recommended: Description for LLM
    required: true            # Optional: Whether parameter is required (default: true)
    default: "value"          # Optional: Default value if not provided

Common Properties

Property	Required	Type	Description
`name`	✅ Yes	string	Parameter name used in SQL (e.g., `:library_name`)
`type`	✅ Yes	string	One of: `string`, `integer`, `float`, `boolean`, `array`
`description`	⭐ Recommended	string	LLM-facing description—clear guidance on usage and examples
`required`	No	boolean	`true` = must be provided, `false` = optional (default: `true` unless `default` is set)
`default`	No	varies	Default value if parameter is not provided

The description field is sent directly to the LLM. Write clear, helpful descriptions with examples to guide the LLM in using the parameter correctly.

String Parameters

String parameters accept text values and support length constraints, pattern matching, and enumerated values.

Available Constraints

minLength: Minimum string length
maxLength: Maximum string length
pattern: Regular expression validation
enum: List of allowed values

Example 1: Basic String Parameter

parameters:
  - name: library_name
    type: string
    description: "Library containing the file. Example: 'APPLIB', 'MYLIB'"
    required: true

Example 2: String with Length Constraints

parameters:
  - name: object_name
    type: string
    description: "IBM i object name (1-10 characters)"
    required: true
    minLength: 1
    maxLength: 10

Example 3: String with Pattern Validation

parameters:
  - name: library_name
    type: string
    description: "Library name (uppercase alphanumeric, starts with letter)"
    required: true
    pattern: "^[A-Z][A-Z0-9_]*$"
    maxLength: 10

Use regex patterns to enforce IBM i naming conventions (uppercase, alphanumeric, special characters).

Example 4: String with Enum Values

From object-statistics-dev.yaml:

parameters:
  - name: sql_object_type
    type: string
    description: "SQL object type to find."
    required: false
    default: "INDEX"
    enum: [ALIAS, FUNCTION, INDEX, PACKAGE, PROCEDURE, ROUTINE, SEQUENCE, TABLE, TRIGGER, TYPE, VARIABLE, VIEW, XSR]

When enum is provided, the description is automatically enhanced with “Must be one of: ‘ALIAS’, ‘FUNCTION’, …” for LLM clarity.

Integer Parameters

Integer parameters accept whole numbers and support minimum/maximum constraints and enumerated values.

Available Constraints

min: Minimum value (inclusive)
max: Maximum value (inclusive)
enum: List of allowed values

Example 1: Basic Integer Parameter

parameters:
  - name: max_rows
    type: integer
    description: "Maximum number of rows to return"
    required: false
    default: 100

Example 2: Integer with Range Constraints

From object-statistics-dev.yaml:

parameters:
  - name: months_unused
    type: integer
    description: "Look back this many months. Examples: 1 (past month), 3 (past 3 months), 6 (past 6 months)"
    required: false
    default: 1
    min: 1
    max: 120

Example 3: Integer with Enum Values

parameters:
  - name: priority_level
    type: integer
    description: "Job priority level"
    required: false
    default: 5
    enum: [1, 5, 10, 20]

Example 4: Pagination Parameters

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

Float Parameters

Float parameters accept decimal numbers and support minimum/maximum constraints.

Available Constraints

min: Minimum value (inclusive)
max: Maximum value (inclusive)
enum: List of allowed values

Example 1: Basic Float Parameter

parameters:
  - name: cpu_threshold
    type: float
    description: "CPU usage threshold percentage (0.0 to 100.0)"
    required: false
    default: 80.0
    min: 0.0
    max: 100.0

Example 2: Float for Decimal Precision

parameters:
  - name: memory_gb
    type: float
    description: "Memory size in gigabytes (supports decimals)"
    required: true
    min: 0.1
    max: 1024.0

Example 3: Performance Multiplier

From employee-info.yaml:

parameters:
  - name: performance_multiplier
    type: float
    description: "Performance rating multiplier (0.0-0.3)"
    required: true
    min: 0.0
    max: 0.3
    default: 0.1

Float parameters are ideal for percentages, ratios, and calculations requiring decimal precision.

Boolean Parameters

Boolean parameters accept true or false values. They do not support additional constraints as they are inherently constrained to two values.

Example 1: Simple Boolean Flag

parameters:
  - name: include_inactive
    type: boolean
    description: "Include inactive objects in results"
    required: false
    default: false

Example 2: Boolean with Clear Documentation

parameters:
  - name: reset_statistics
    type: boolean
    description: "Reset statistics after retrieval. true = reset counters, false = preserve current values"
    required: false
    default: false

Example 3: Completed Project Filter

From employee-info.yaml:

parameters:
  - name: include_completed
    type: boolean
    description: "Include completed projects (true) or only active projects (false)"
    default: true

Using in SQL:

statement: |
  SELECT * FROM SAMPLE.EMPPROJACT EPA
  WHERE EPA.EMPNO = :employee_id
  AND (:include_completed = 1 OR EPA.EMENDATE IS NULL)

Boolean values are automatically converted to 1 (true) or 0 (false) in SQL queries.

Array Parameters

Array parameters accept lists of values and require an itemType to specify the type of elements. They are designed for SQL IN clauses and automatically expand to multiple placeholders.

Array Input Format

IMPORTANT: Array parameters must be passed as JSON arrays, not as strings containing SQL syntax.✅ Correct: {"project_ids": ["MA2100", "AD3100"]}❌ Incorrect: {"project_ids": "('MA2100', 'AD3100')"}❌ Incorrect: {"project_ids": "MA2100,AD3100"}

Available Constraints

itemType: Required - Type of array elements (string, integer, float, or boolean)
minLength: Minimum number of items
maxLength: Maximum number of items

Example 1: String Array

parameters:
  - name: library_list
    type: array
    itemType: string
    description: "List of library names to search (e.g., ['MYLIB', 'QGPL', 'QSYS'])"
    required: false
    minLength: 1
    maxLength: 50

Example 2: Integer Array with Constraints

parameters:
  - name: job_numbers
    type: array
    itemType: integer
    description: "List of job numbers to analyze (e.g., [12345, 67890, 11111])"
    required: true
    minLength: 1
    maxLength: 100

Example 3: Project IDs

From employee-info.yaml:

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

Using Arrays in SQL

Array parameters are automatically expanded to multiple placeholders for SQL IN clauses. Simply use the array parameter name directly in the IN clause:

statement: |
  SELECT * FROM SAMPLE.EMPPROJACT
  WHERE PROJNO IN (:project_ids)

What happens internally:

Input JSON: {"project_ids": ["MA2100", "AD3100", "AD3110"]}
SQL with named parameter: WHERE PROJNO IN (:project_ids)
Automatic expansion: WHERE PROJNO IN (?, ?, ?) (one placeholder per array element)
Parameter binding: Each ? is bound to one array element: "MA2100", "AD3100", "AD3110"
DB2 execution: Standard prepared statement with bound parameters

Key Benefits:

✅ No SQL injection risk - Parameters are safely bound
✅ Variable-length arrays - Works with any array size (within constraints)
✅ Simple syntax - Just use IN (:array_param) in your SQL
✅ Type validation - Each array element is validated against itemType
✅ No Db2-specific workarounds needed - Works like standard JDBC parameter binding

SQL IN Clause Behavior: The IN clause uses OR logic - it matches records where the column equals ANY value in the list, not ALL values.

WHERE PROJNO IN ('MA2100', 'AD3100')  -- Matches records with PROJNO = 'MA2100' OR 'AD3100'

If you need AND logic (matching ALL values), you’ll need different SQL patterns like subqueries or aggregation.

Parameter Constraint Summary

Constraint	Type Support	Description	Example
`min`	integer, float	Minimum value (inclusive)	`min: 1`
`max`	integer, float	Maximum value (inclusive)	`max: 100`
`minLength`	string, array	Minimum length/count	`minLength: 1`
`maxLength`	string, array	Maximum length/count	`maxLength: 50`
`pattern`	string	Regular expression validation	`pattern: "^[A-Z][A-Z0-9]*$"`
`enum`	string, integer, float, boolean	Allowed values only	`enum: [INDEX, TABLE, VIEW]`
`itemType`	array	Type of array elements (required)	`itemType: string`

Best Practices

Parameter Descriptions

The description field is sent directly to the LLM to help it understand how to use the parameter. Follow these guidelines:

✅ DO

Provide clear, concise descriptions
Include examples of valid values
Explain the purpose and impact of the parameter
Use IBM i terminology when applicable
Indicate units for numeric values

Good examples:

description: "Library name. Examples: 'MYLIB', '*LIBL', '*USRLIBL', '*ALLUSR'"
description: "Look back this many months. Examples: 1 (past month), 3 (past 3 months), 6 (past 6 months)"
description: "CPU usage threshold percentage (0.0 to 100.0). Values above this trigger alerts"

❌ DON'T

Use vague descriptions: ~~"A library"~~
Omit examples: ~~"Number of months"~~
Forget to document special values: ~~"Library name" (should mention *LIBL, etc.)~~

Using Parameters in SQL

Parameters are referenced in SQL statements using the :parameter_name syntax: Example: Parameter Binding

statement: |
  SELECT * FROM TABLE (
    qsys2.object_statistics(
      object_schema => :object_schema,
      objtypelist => '*ALL',
      object_name => '*ALL'
    )
  )
  WHERE sql_object_type = :sql_object_type
    AND last_used_timestamp < current_timestamp - :months_unused MONTHS
  ORDER BY last_used_timestamp DESC

Handling Optional Parameters:

statement: |
  SELECT * FROM qsys2.library_info
  WHERE (:name_filter IS NULL OR library_name LIKE :name_filter)
    AND (:type_filter IS NULL OR library_type = :type_filter)
  ORDER BY library_name

Using Default Values:

parameters:
  - name: name_filter
    type: string
    required: false  # NULL if not provided
  - name: max_rows
    type: integer
    required: false
    default: 100     # 100 if not provided

Complete Examples

Example 1: Recently Used Objects

From object-statistics-dev.yaml:

tools:
  find_recently_used_objects:
    source: ibmi-system
    description: Find objects that have been used within a specified time period
    statement: |
      SELECT * FROM TABLE (
        qsys2.object_statistics(
          object_schema => :object_schema,
          objtypelist => '*ALL',
          object_name => '*ALL'
        )
      )
      WHERE last_used_object = 'YES'
        AND sql_object_type = :sql_object_type
        AND last_used_timestamp < current_timestamp - :months_unused MONTHS
      ORDER BY last_used_timestamp DESC
    parameters:
      - name: object_schema
        type: string
        description: "Library name. Examples: 'MYLIB', '*LIBL', '*USRLIBL', '*ALLUSR'"
        required: true

      - name: sql_object_type
        type: string
        description: "SQL object type to find."
        required: false
        default: "INDEX"
        enum: [ALIAS, FUNCTION, INDEX, PACKAGE, PROCEDURE, ROUTINE, SEQUENCE, TABLE, TRIGGER, TYPE, VARIABLE, VIEW, XSR]

      - name: months_unused
        type: integer
        description: "Look back this many months. Examples: 1 (past month), 3 (past 3 months), 6 (past 6 months)"
        required: false
        default: 1
        min: 1
        max: 120

Example 2: Filtered Library Search

tools:
  search_libraries:
    source: ibmi-system
    description: Search for libraries with filtering options
    statement: |
      SELECT library_name, library_type, library_size
      FROM qsys2.library_info
      WHERE (:name_pattern IS NULL OR library_name LIKE :name_pattern)
        AND (:type_filter IS NULL OR library_type = :type_filter)
        AND (:min_size IS NULL OR library_size >= :min_size)
      ORDER BY library_name
      FETCH FIRST :max_rows ROWS ONLY
    parameters:
      - name: name_pattern
        type: string
        description: "Library name pattern (use % for wildcards). Example: 'APP%' matches all libraries starting with APP"
        required: false
        pattern: "^[A-Z0-9%_*]+$"
        maxLength: 10

      - name: type_filter
        type: string
        description: "Filter by library type"
        required: false
        enum: ["PROD", "TEST"]

      - name: min_size
        type: integer
        description: "Minimum library size in bytes"
        required: false
        min: 0

      - name: max_rows
        type: integer
        description: "Maximum number of results to return"
        required: false
        default: 100
        min: 1
        max: 1000

Next Steps

Parameter Validation

Learn how parameters are validated and secured

Output Formats

Configure result table formatting

Building Tools

Complete guide to creating SQL tools

SQL Tools Overview

Introduction to the SQL tools system

Getting Started

Core Concepts

Server Configuration

SQL Tools System

​SQL Tool Parameters

​Parameter Types

​Parameter Structure

​Common Properties

​String Parameters

​Available Constraints

​Example 1: Basic String Parameter

​Example 2: String with Length Constraints

​Example 3: String with Pattern Validation

​Example 4: String with Enum Values

​Integer Parameters

​Available Constraints

​Example 1: Basic Integer Parameter

​Example 2: Integer with Range Constraints

​Example 3: Integer with Enum Values

​Example 4: Pagination Parameters

​Float Parameters

​Available Constraints

​Example 1: Basic Float Parameter

​Example 2: Float for Decimal Precision

​Example 3: Performance Multiplier

​Boolean Parameters

​Example 1: Simple Boolean Flag

​Example 2: Boolean with Clear Documentation

​Example 3: Completed Project Filter

​Array Parameters

​Array Input Format

​Available Constraints

​Example 1: String Array

​Example 2: Integer Array with Constraints

​Example 3: Project IDs

​Using Arrays in SQL

​Parameter Constraint Summary

​Best Practices

​Parameter Descriptions

​Using Parameters in SQL

​Complete Examples

​Example 1: Recently Used Objects

​Example 2: Filtered Library Search

​Next Steps

Parameter Validation

Output Formats

Building Tools

SQL Tools Overview

SQL Tool Parameters

Parameter Types

Parameter Structure

Common Properties

String Parameters

Available Constraints

Example 1: Basic String Parameter

Example 2: String with Length Constraints

Example 3: String with Pattern Validation

Example 4: String with Enum Values

Integer Parameters

Available Constraints

Example 1: Basic Integer Parameter

Example 2: Integer with Range Constraints

Example 3: Integer with Enum Values

Example 4: Pagination Parameters

Float Parameters

Available Constraints

Example 1: Basic Float Parameter

Example 2: Float for Decimal Precision

Example 3: Performance Multiplier

Boolean Parameters

Example 1: Simple Boolean Flag

Example 2: Boolean with Clear Documentation

Example 3: Completed Project Filter

Array Parameters

Array Input Format

Available Constraints

Example 1: String Array

Example 2: Integer Array with Constraints

Example 3: Project IDs

Using Arrays in SQL

Parameter Constraint Summary

Best Practices

Parameter Descriptions

Using Parameters in SQL

Complete Examples

Example 1: Recently Used Objects

Example 2: Filtered Library Search

Next Steps