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"
    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, or one masking rule.

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.
`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

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. describe_table adds 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.

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. 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

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

​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

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