> ## Documentation Index > Fetch the complete documentation index at: https://docs.db2i-mcp.com/llms.txt > Use this file to discover all available pages before exploring further. # Business SQL tools > Add read-only ERP queries and table notes as MCP tools, defined in YAML. 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](https://github.com/Strom-Capital/mcp-server-db2i/tree/main/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 ```yaml theme={null} 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](#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: ```sql theme={null} 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](#common-patterns). The cast gives the marker a type when the argument is NULL. ### Running on one system With several systems in [`DB2I_PROFILES`](configuration.md#multiple-systems), set `system:` to pin a tool to one of them: ```yaml theme={null} 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. ```yaml theme={null} 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 `****` | ```yaml theme={null} 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](security.md#column-masking) 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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`: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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 ` 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](development.md#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:`, as well as the built-in names. A toolset selector does not match built-in tools. An unknown name or toolset stops startup. ```env theme={null} 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](docker.md). ## Related topics - [Docker](/docker.md) - [Configuration](/configuration.md) - [Tools, resources, and prompts](/tools.md) - [Security](/security.md) - [Db2 for i MCP Server](/index.md) This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.