Business SQL tools

Administrators can add read-only tools, and notes about tables the catalog does not explain, without changing the server code. The tools are SELECT and WITH statements. Parameter values are bound. They are never pasted into the SQL text. Set MCP_CUSTOM_TOOLS to a YAML file, a directory, or a comma-separated list of either. The server reads them once at startup and refuses to start when a file is invalid or a statement fails the read-only check. When QUERY_ALLOWED_SCHEMAS is set, every table reference must stay inside that list too. An empty or unset MCP_CUSTOM_TOOLS loads nothing. The built-in tools keep working. See examples/erp-tools for a generic pack: sales orders, purchase orders, service orders, manufacturing orders, a bill of materials, the general ledger, and item and customer master data. The statements show patterns that show up on real order files: a numeric date, a derived status, a header with jobs and lines, and a text search. Every library, table, and column name in that pack is a placeholder. Point them at your own files before you load the directory.

File format

version: 1
tools:
  - name: search_sales_orders
    title: Search sales orders
    toolset: sales
    description: Open sales orders for a customer, newest first.
    parameters:
      customer: { type: string, required: true, maxLength: 10, description: Customer number }
      status: { type: string, enum: [O, C], default: O }
    maxRows: 200
    sql: |
      SELECT H.ORDERNO, H.CUSTNO, H.ORDERDATE, H.STATUS
      FROM MYLIB.ORDERHDR H
      WHERE H.CUSTNO = :customer AND H.STATUS = :status
      ORDER BY H.ORDERDATE DESC
annotations:
  MYLIB.ORDERHDR:
    entity: sales_order
    description: Sales order header
    columns:
      STATUS: "O = open, C = closed"
    filters:
      - sql: "TRIM(STATFLG) <> 'D'"
        columns: [STATFLG]
        reason: D rows are deleted orders
    relations:
      - table: MYLIB.ORDERS
        join: { ORDERNO: ORDERNO }
        cardinality: one-to-many
        description: Order lines

version must be 1. A file needs at least one tool, one annotation, one masking rule, or instructions.

Tools

Field	Required	Meaning
`name`	yes	snake_case. Cannot reuse a built-in tool name. Must be unique across every loaded file.
`title`	yes	Short label shown to the client
`description`	yes	What the tool returns, and how to choose arguments
`toolset`	no	snake_case group. `MCP_TOOLS_ENABLED=toolset:sales` registers only that group.
`parameters`	no	Named arguments. Every parameter must appear as `:name` in the SQL, and every `:name` must be declared.
`maxRows`	no	Row cap for this tool. The server also applies `QUERY_MAX_LIMIT`, and uses the smaller of the two. When `maxRows` is omitted, `QUERY_DEFAULT_LIMIT` is used. A result that stops at the cap has `truncated: true` and a warning.
`system`	no	Profile from `DB2I_PROFILES` the tool always runs on. See Running on one system.
`sql`	yes	One read-only statement. It must start with `SELECT` or `WITH`.

Parameter types are string (optional maxLength), integer, number, boolean, date, and enum. A string parameter may also set enum to a list of allowed values. date values are YYYY-MM-DD. A parameter with a default may be omitted, and the default is bound. required: false with no default may be omitted, and NULL is bound. Anything else must be sent by the client. Boolean values bind as 1 and 0. :name is replaced with a ? marker. The same name may appear more than once, and the same value is bound each time. Placeholders inside string literals, quoted identifiers, and comments are left as text. A hand-written ? is rejected so the bind order stays unambiguous. An optional filter has to survive a NULL. Compare a cast marker, then the column:

WHERE (CAST(:customer AS VARCHAR(10)) IS NULL OR H.CUSTNO = :customer)

Use CAST(:from_date AS DATE) when the column is a real DATE. A numeric YYYYMMDD column needs the conversion in Common patterns. The cast gives the marker a type when the argument is NULL.

Running on one system

With several systems in DB2I_PROFILES, set system: to pin a tool to one of them:

tools:
  - name: open_orders_prod
    title: Open orders (production)
    description: Open sales orders on the production system.
    system: prod
    sql: SELECT ORDERNO, CUSTNO FROM SALES.ORDERHDR WHERE STATUS = 'O'

A pinned tool has no system argument and always runs on its system. An unknown name stops startup.
A tool without system: gets the same optional system argument as the built-in tools, and runs on the default (first) system when the caller names none. A tool that declares its own parameter named system gets no extra argument and runs on the default system.
At startup a pinned tool is checked against its system’s allowlist. Other tools are checked against the default system’s list. Every call is checked again against the system it runs on.
An HTTP session that logged in to one system does not list tools pinned to another.

Annotations

Keys are SCHEMA.TABLE. Names are folded to uppercase.

Field	Meaning
`entity`	snake_case name such as `sales_order`
`description`	What the table is, in business words
`columns`	Meaning of a column, including status codes
`relations`	A link the catalog does not declare as a foreign key
`filters`	Row filters most queries on the table need, such as leaving out deleted rows

A relation names the other SCHEMA.TABLE, a join map of local column to remote column, an optional cardinality (one-to-one, one-to-many, many-to-one, many-to-many), and an optional description. get_business_context returns these notes. Filter with entity, table (ORDERHDR or MYLIB.ORDERHDR), or omit both to list every annotation. The entity filter ignores case and treats spaces and hyphens as underscores. When no entity has the requested name, it returns the entities whose names partly match it (one name contains the other, or they share the most word parts, such as order and line) with partial_match: true and a hint. When nothing matches, it returns empty data with available_entities, plus available_tables for a table filter, and a hint to pick one of them or omit the filters. The table filter itself stays exact. describe_table adds filters, business_description and relations when the table is annotated, and a business_description on columns that have one. list_tables adds business_description on annotated tables.

Row filters

A filter has the SQL a query should include (sql), the columns it uses, and an optional reason. Use it for rules that are easy to miss and change the answer, such as a flag that marks deleted rows. Deleted child rows often sit under a live header, so annotate the line table too, not only the header.

get_business_context lists filters first in each table, and describe_table puts them before the column list.
With QUERY_PARSE_CHECK on (the default), execute_query, export_query and validate_query check each statement against the filters of the annotated tables it reads. When none of a filter’s columns appears in the statement, qualified with that table or unqualified, the query still runs. The result lists the table in skippedFilters and adds a warning that quotes the filter and its reason. The check looks for the column, not the exact predicate, and never blocks a query, since some questions need deleted rows. With the parse check off, there is no filter check.
validate_query reports skippedFilters and warnings without changing valid.
validate-tools --connect checks that each filter column exists on its table.
Business SQL tools are not checked. Put the filter in their SQL.

Instructions

instructions is an optional top-level text. All files together may hold at most 4000 characters of it. The server sends it to clients as MCP server instructions: in the initialize result for 2025-era clients, and in the server/discover result on protocol 2026-07-28, which has no initialize. Either way the model has it for the whole session without calling a tool first. Use it for the few rules no query may miss, such as which flag marks a deleted row. Table and column detail belongs in annotations. Put a deleted-row flag in the table’s filters too: the instructions arrive once at the start, while a filter is checked against every query.

version: 1
instructions: |
  MYLIB.ORDERHDR: always filter TRIM(STATFLG) <> 'D'. D rows are deleted orders.
  STATUS 60 is invoiced.

A file may hold only instructions. Texts from several files are joined in file order. A file that also defines tools sends its text only to sessions that have at least one of those tools. MCP_TOOLS_ENABLED, MCP_TOOLS_DISABLED and a tool’s system: decide that. So write guidance about a file’s tools, such as “use search_sales_orders for order questions”, in that file, and put rules that every query must follow in a file without tools. The example pack keeps them in rules.yaml. When custom files are loaded, the server puts a short built-in part first:

with annotations and get_business_context or describe_table enabled: read a table’s business context with the enabled ones before writing SQL against it
with a business tool registered for the session: prefer a business tool when one answers the question

A server without MCP_CUSTOM_TOOLS sends no instructions. Some clients do not pass server instructions to the model. So when annotations are loaded and get_business_context or describe_table is enabled, the descriptions of execute_query and export_query also end with a sentence that asks the model to call that tool for a table before querying it. Tool descriptions reach the model in every client. Clients read instructions when they connect, from the initialize or server/discover result. With MCP_CUSTOM_TOOLS_WATCH, a change reaches the next such request over HTTP, because the default stateless mode builds the server for each request. A stdio server, and a session in the deprecated MCP_SESSION_MODE=stateful mode, keep the text they started with. The sentence in the execute_query and export_query descriptions follows the reload, and stdio servers and stateful sessions also get notifications/tools/list_changed. Clients decide what to do with server instructions. Claude Code adds them to the model’s context. Check your client if the rules do not seem to reach the model.

Masking

A masking section names columns the agent should not see in full. Keys are SCHEMA.TABLE. Column names are unquoted SQL names. Both are folded to uppercase. The same table and column in two files is rejected.

Rule	Result
`redact`	The value is replaced with `****`
`last4`	The last four characters are kept and the rest become ``. A value of four characters or fewer is replaced with `***`

version: 1
masking:
  MYLIB.CUSTOMERS:
    EMAIL: redact
    PHONE: last4

execute_query and YAML tools may select a masked column only as a plain item in the outer select list: EMAIL, C.EMAIL, or MYLIB.CUSTOMERS.EMAIL. SELECT * is allowed, and the matching result keys are masked. An alias, an expression, a predicate, a join, GROUP BY, ORDER BY, a subquery, and UNION, EXCEPT, or INTERSECT are rejected. An ORDER BY position (ORDER BY 2) is rejected too, because it can point at the masked column. An unqualified EMAIL counts as the masked column whenever MYLIB.CUSTOMERS is in the statement, even when another table also has a column of that name. A view, an alias object, or a table function over a masked table is not masked unless that object is listed itself. YAML tools are checked when the files load, including a rule that lives in a different file from the tool. execute_query uses QSYS2.PARSE_STATEMENT to see which tables the statement touches, so it refuses to run when masking is loaded and QUERY_PARSE_CHECK is off. extended metadata=true in DB2I_JDBC_OPTIONS renames result columns, and the server refuses to start with that option while masking is loaded. profile_table applies the same rules: a masked column keeps its distinct and null counts and returns no low, high, minimum, or maximum value. See Security for why this is a backstop and not a database control.

Common patterns

The example pack uses placeholder names (MYLIB.ORDERHDR, ORDERNO, ORDERDAT). Copy the shape of the statement, then rename every identifier to the files you actually have. The status numbers below are an example ladder, not a standard.

An optional filter has to accept NULL

An omitted optional argument is bound as NULL. NULL = NULL is unknown, so a bare comparison drops every row. Test the cast first:

AND (CAST(:customer AS VARCHAR(10)) IS NULL OR TRIM(H.CUSTNO) = TRIM(CAST(:customer AS VARCHAR(10))))

Use the same :name in both places. The same value is bound each time. TRIM matters when the column is a fixed-length character field with trailing blanks.

A date stored as a number

Many order files store the day as an integer YYYYMMDD, not as a DATE. A date parameter arrives as text YYYY-MM-DD. Build the number with SUBSTR and compare it to the column:

AND (
  CAST(:from_date AS VARCHAR(10)) IS NULL
  OR H.ORDERDAT >= INTEGER(
    SUBSTR(CAST(:from_date AS VARCHAR(10)), 1, 4) ||
    SUBSTR(CAST(:from_date AS VARCHAR(10)), 6, 2) ||
    SUBSTR(CAST(:from_date AS VARCHAR(10)), 9, 2)
  )
)

Show the same number back as a date with DIGITS, which zero-pads it to the width of the column:

SUBSTR(DIGITS(H.ORDERDAT), 1, 4) || '-' ||
  SUBSTR(DIGITS(H.ORDERDAT), 5, 2) || '-' ||
  SUBSTR(DIGITS(H.ORDERDAT), 7, 2) AS ORDER_DATE

Do not use REPLACE to strip the dashes. The read-only check treats REPLACE as a data-changing statement and the server will not start. TRANSLATE is safe if you prefer it, and so is the SUBSTR form above. When the column is a real DATE, compare it directly. The general-ledger example does this with TRANSDATE:

AND (CAST(:from_date AS DATE) IS NULL OR T.TRANSDATE >= :from_date)

Derived status

A single column rarely matches the word a person uses (“open”, “closed”, “invoiced”). Compute it in the statement and document the codes on the annotation. The sales example treats STATFLG = 'E' as an error, status 60 as invoiced, status 40 and above as picked, and anything else as open. Deleted rows (STATFLG = 'D') are filtered out rather than counted. A service order is often “closed” only when every job under it is closed. That needs the jobs in the same statement:

CASE
  WHEN TRIM(H.STATFLG) = 'E' THEN 'error'
  WHEN H.STATUS = 60 THEN 'invoiced'
  WHEN COUNT(J.JOBNO) > 0
    AND SUM(CASE WHEN TRIM(J.CLOSEDFLG) <> 'Y' THEN 1 ELSE 0 END) = 0
    THEN 'closed'
  ELSE 'open'
END AS DERIVED_STATUS

To filter on that result, wrap the grouped query and test the alias outside. The status number and the flag stay in the GROUP BY.

Header, job, and line

A service order in the example pack is three files:

SVCHDR is the header, and it joins ORDTYPE where SVCFLAG = 'Y' so ordinary sales types stay out
SVCJOB is one job package and points at an installed unit with ITEMNO and SERIALNO
SVCLINE is a labor or part line on that job

get_service_order returns one row per job. list_service_order_lines returns the lines. Put that shape in the annotations too, so get_business_context can explain a join the catalog does not declare.

Text search

Match several columns, and use EXISTS when the value lives on a child row. Fold case on both sides:

UPPER(TRIM(C.CUSTNAME)) LIKE '%' || UPPER(TRIM(CAST(:text AS VARCHAR(30)))) || '%'
OR EXISTS (
  SELECT 1
  FROM MYLIB.SVCJOB J
  WHERE J.SVCNO = H.SVCNO
    AND UPPER(TRIM(J.SERIALNO)) LIKE '%' || UPPER(TRIM(CAST(:text AS VARCHAR(30)))) || '%'
)

A leading % does not use an index. Keep maxRows small, and require the text argument so a client cannot scan the whole file by accident.

Row caps

Leave LIMIT and FETCH FIRST out of the YAML. Set maxRows on the tool. The server appends FETCH FIRST n ROWS ONLY and will not go above QUERY_MAX_LIMIT.

What is still enforced

Custom tools use the read-only connection, the rate limiter, and the read-only tool hints. At startup the server runs the same read-only check as execute_query. When QUERY_ALLOWED_SCHEMAS is set, it also checks every table reference, and refuses to start if a statement names another library or cannot be parsed. Qualify tables with a library (MYLIB.ORDERHDR) so the check does not depend on the session schema. At call time the allowlist is checked again with that session’s default schema, which matters for unqualified names. When QUERY_PARSE_CHECK is on, the first call of each tool asks QSYS2.PARSE_STATEMENT whether the statement is a query. That result is cached for the life of the process. A missing function rejects the tool until you set QUERY_PARSE_CHECK=false. mcp-server-db2i validate-tools <path...> runs those startup checks and exits, without a database. Add --connect to run the PARSE_STATEMENT check as well, and to check that annotation filter columns exist. That needs credentials and a reachable host. See Validating tool files. MCP_CUSTOM_TOOLS_WATCH=true runs the same checks again when a watched file changes. A valid set replaces the registry and clients are told to refresh tools/list. A bad save is logged and does not replace the tools that are already running. The default is off. Watching with an empty MCP_CUSTOM_TOOLS stops startup. MCP_TOOLS_ENABLED and MCP_TOOLS_DISABLED accept a custom tool name or toolset:<name>, as well as the built-in names. A toolset selector does not match built-in tools. An unknown name or toolset stops startup.

MCP_CUSTOM_TOOLS=./examples/erp-tools
MCP_TOOLS_ENABLED=toolset:sales,get_business_context
QUERY_ALLOWED_SCHEMAS=MYLIB

That registers the sales tools and get_business_context, and leaves execute_query unregistered. IBM i object authority on the user profile is the last line of defense. The profile should be able to read the business files and should not be able to change them. A read-only database connection and the statement checks sit in front of that. They do not replace it.

Docker

Mount the YAML directory read-only and set MCP_CUSTOM_TOOLS to the path inside the container. See the Docker guide.

Getting started

Reference

Deployment

Contributing

File format

Tools

Running on one system

Annotations

Row filters

Instructions

Masking

Common patterns

An optional filter has to accept NULL

A date stored as a number

Derived status

Header, job, and line

Text search

Row caps

What is still enforced

Docker

​File format

​Tools

​Running on one system

​Annotations

​Row filters

​Instructions

​Masking

​Common patterns

​An optional filter has to accept NULL

​A date stored as a number

​Derived status

​Header, job, and line

​Text search

​Row caps

​What is still enforced

​Docker

File format

Tools

Running on one system

Annotations

Row filters

Instructions

Masking

Common patterns

An optional filter has to accept NULL

A date stored as a number

Derived status

Header, job, and line

Text search

Row caps

What is still enforced

Docker