Metadata-Version: 2.5
Name: awslabs.db2-mcp-server
Version: 0.1.1
Summary: An AWS Labs Model Context Protocol (MCP) server for Amazon RDS for Db2
Project-URL: homepage, https://awslabs.github.io/mcp/
Project-URL: repository, https://github.com/awslabs/mcp.git
Project-URL: changelog, https://github.com/awslabs/mcp/blob/main/src/db2-mcp-server/CHANGELOG.md
Author: Amazon Web Services
Author-email: AWSLabs MCP <203918161+awslabs-mcp@users.noreply.github.com>
License: Apache-2.0
License-File: LICENSE
License-File: NOTICE
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Requires-Python: >=3.10
Requires-Dist: boto3>=1.42.79
Requires-Dist: botocore>=1.42.79
Requires-Dist: ibm-db>=3.2.3
Requires-Dist: loguru>=0.7.0
Requires-Dist: mcp[cli]<2.0.0,>=1.23.0
Requires-Dist: pydantic>=2.10.6
Requires-Dist: python-multipart>=0.0.27
Description-Content-Type: text/markdown

# Amazon RDS for Db2 MCP Server

An AWS Labs Model Context Protocol (MCP) server for Amazon RDS for Db2. It lets an
MCP-aware assistant **connect** to RDS for Db2 instances over SSL and **run read-only
SQL** — turning natural-language questions into Db2 queries.

> **Scope.** This server handles *connect, query, and schema inspection*. It does **not**
> provision Db2 instances. Provisioning is owned by the companion
> `rds-db2-provision-skill` (a Terraform composer/orchestrator). Together they cover the
> full **deploy → connect → query** journey, each in its proper layer.

## Features

- **`connect_to_database`** — open and cache an SSL connection using credentials pulled from
  AWS Secrets Manager.
- **`run_query`** — run a parameterized SQL query (read-only by default).
- **`get_table_schema`** — fetch column metadata from `SYSCAT.COLUMNS`.
- **`is_database_connected`** / **`get_database_connection_info`** — inspect cached connections.

### Read-only by default

The server starts in read-only mode. Mutating statements (`INSERT`, `UPDATE`, `DELETE`,
DDL, `CALL ... ADMIN_CMD`, etc.), transaction-control bypasses, and common SQL-injection
patterns are rejected before execution; the connection runs with autocommit off and rolls
back after every query. Pass `--allow_write_query` to permit writes.

> **What read-only mode does and does not guarantee.** Enforcement is by inspection of the
> SQL text, plus autocommit-off and a post-query `ROLLBACK`. There is no Db2-side
> enforcement behind it, so `ROLLBACK` cannot undo non-transactional side effects. Two known
> cases: **sequences** are explicitly non-transactional in Db2, so
> `SELECT NEXT VALUE FOR seq` increments and stays incremented; and a **function or table
> function declared `MODIFIES SQL DATA`** can write from inside a `SELECT`, which the
> keyword denylist does not see. If you need a hard guarantee, point `--secret_arn` at a
> credential for a user that has no write privilege — that is enforced by Db2 itself rather
> than by this server.

#### SQL restrictions (intentional)

The injection guard is deliberately conservative and rejects some SQL that is technically
read-only:

- **`UNION` / `UNION ALL`** are blocked. `UNION` is a classic data-exfiltration vector, so
  the server rejects `UNION ... SELECT` even in read-only mode, including legitimate
  analytics like `SELECT region, COUNT(*) FROM sales UNION SELECT region, COUNT(*) FROM archive`.
  Rewrite such queries without `UNION` (e.g. run the two `SELECT`s separately). This is a
  conscious safety-over-convenience choice; open an issue if it blocks a real workflow.
- Stacked queries, `CALL ... ADMIN_CMD`, `SYSPROC.*`, and authorization-catalog views
  (`SYSCAT.DBAUTH`, `SYSCAT.SCHEMAAUTH`, and the other `*AUTH` views) are also rejected.
  These are matched by object-name shape, so quoting or spacing (`SYSCAT."DBAUTH"`,
  `SYSCAT . DBAUTH`) does not evade them.

#### What is *not* treated as SQL

Two forms of over-matching were removed, because they rejected ordinary read-only queries:

- **Delimited identifiers are names, not syntax.** Their contents are not keyword-scanned,
  so `SELECT "SET" FROM T`, `SELECT * FROM "DELETE"` and `SELECT "a;b" FROM T` are all
  permitted. (They *are* still inspected by the object-name patterns above, which is how
  `SYSCAT."DBAUTH"` stays blocked.)
- **Db2 utility verbs are only mutating when they lead the statement.** `REORG`,
  `RUNSTATS`, `IMPORT`, `LOAD`, `EXPORT`, `FLUSH` and `REFRESH` are not reserved words, so
  they are perfectly ordinary column names — `REFRESH` is a real `CHAR(1)` column on
  `SYSCAT.TABLES`. `REFRESH TABLE MV1` is rejected; `SELECT REFRESH FROM SYSCAT.TABLES` is
  not. `INSERT`/`UPDATE`/`MERGE` are still matched anywhere, because they genuinely mutate
  from a nested position via a data-change-table-reference
  (`SELECT a FROM FINAL TABLE (INSERT ...)`).
- **`SET`** statements (including `SET CURRENT ...` session-state changes) are treated as
  mutating and rejected in read-only mode, since they can alter session behavior
  (e.g. `SET CURRENT SCHEMA`, `SET CURRENT QUERY OPTIMIZATION`).

## Connectivity & SSL

RDS for Db2 is reached over a private endpoint inside your VPC. In the Db2 tooling
ecosystem the instance is **SSL-only**: `DB2COMM=SSL`, `ssl_svcename=50443`, and the plain
TCP listener (port `50000`, this server's `--ssl_encryption off` default) is dormant and
closed in the security group. This server therefore **defaults to SSL on port 50443**.

SSL uses the IBM driver's `SSLServerCertificate` option pointed at the RDS **regional
certificate bundle (PEM)** — no Java keystore or `keytool` required. Download the bundle
for your region and pass its path with `--ssl_server_certificate`.

> **The certificate bundle is required when `--ssl_encryption require` (the default).**
> `SECURITY=SSL` without a server certificate encrypts the connection but does not
> authenticate the server, leaving it open to man-in-the-middle interception of
> credentials and query data. The server therefore refuses to start in SSL mode without
> a certificate bundle. Use `--ssl_encryption off` only for non-SSL/local testing.

- Client setup (CloudShell / EC2): [Connect to Amazon RDS for Db2 using AWS CloudShell](https://aws.amazon.com/blogs/database/connect-to-amazon-rds-for-db2-using-aws-cloudshell/)
- SSL without a keystore (the approach used here): [Create an SSL connection to Amazon RDS for Db2 in Java without keystore or keytool](https://aws.amazon.com/blogs/database/create-an-ssl-connection-to-amazon-rds-for-db2-in-java-without-keystore-or-keytool/)

Content from the linked blogs was summarized; see the originals for full detail.

## Prerequisites

1. Install [`uv`](https://docs.astral.sh/uv/getting-started/installation/) and Python 3.10+.
2. AWS credentials with permission to call `rds:DescribeDBInstances` and
   `secretsmanager:GetSecretValue` for the target instance/secret.
3. Network reachability to the instance endpoint on port 50443 (same VPC, peered VPC, or
   tunnel), and the RDS regional SSL certificate bundle.
4. The IBM `ibm_db` driver is installed automatically as a dependency.

## Installation

Add this to your MCP client configuration (e.g. Kiro, Cline, Cursor, Claude Desktop).

`--ssl_server_certificate` is **required**: `--ssl_encryption` defaults to `require`, and
without the RDS regional certificate bundle the server certificate cannot be validated.
Download the bundle for your region first — see [Using SSL/TLS to encrypt a connection to
a DB instance](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html).
There is no environment-variable fallback for this path, so it must be passed as a CLI
argument.

```json
{
  "mcpServers": {
    "awslabs.db2-mcp-server": {
      "command": "uvx",
      "args": [
        "awslabs.db2-mcp-server@latest",
        "--region",
        "us-east-1",
        "--ssl_server_certificate",
        "/path/to/us-east-1-bundle.pem"
      ],
      "env": {
        "AWS_PROFILE": "your-profile",
        "AWS_REGION": "us-east-1",
        "FASTMCP_LOG_LEVEL": "ERROR"
      },
      "disabled": false,
      "autoApprove": []
    }
  }
}
```

To pre-connect a single instance at startup, pass arguments after the package name:

```json
{
  "mcpServers": {
    "awslabs.db2-mcp-server": {
      "command": "uvx",
      "args": [
        "awslabs.db2-mcp-server@latest",
        "--region", "us-east-1",
        "--db_endpoint", "db2.abc123.us-east-1.rds.amazonaws.com",
        "--database", "DB2DB",
        "--ssl_server_certificate", "/path/to/us-east-1-bundle.pem"
      ],
      "env": {"AWS_PROFILE": "your-profile", "FASTMCP_LOG_LEVEL": "ERROR"},
      "disabled": false,
      "autoApprove": []
    }
  }
}
```

## Command-line options

| Option | Default | Description |
|---|---|---|
| `--region` | — | AWS region (required if `--db_endpoint` is set) |
| `--db_endpoint` | — | Db2 endpoint to pre-connect and validate at startup |
| `--instance_identifier` | first DNS label of `db_endpoint` | RDS instance identifier |
| `--database` | `DB2DB` | Db2 database name |
| `--port` | `50443` (SSL) / `50000` (off) | Db2 port |
| `--secret_arn` | RDS managed master secret | Secrets Manager ARN. **Repeatable.** `<arn>` sets a default; `<instance_identifier>=<arn>` pins an ARN to one instance. See below. |
| `--ssl_encryption` | `require` | `require` (SSL) or `off` (plain TCP) |
| `--ssl_server_certificate` | — | Path to the RDS regional PEM bundle (required when `--ssl_encryption require`) |
| `--ssl_hostname_validation` | `basic` | `basic` validates the cert hostname; `off` disables it (tunnel testing only) |
| `--allow_write_query` | off | Permit non-SELECT statements |
| `--max_rows` | `1000` | Max rows per query (0 = no limit; negative values are rejected) |
| `--query_timeout_s` | `30` | Per-query timeout in seconds (0 = none; negative values are rejected) |
| `--login_timeout_s` | `15` | Per-connect (TCP/TLS handshake + auth) timeout in seconds (0 = none; negative values are rejected) |

### Credentials are operator-controlled

`connect_to_database` has **no `secret_arn` parameter**. The model chooses only *which
instance* to target, by `instance_identifier`; the mapping from identifier to ARN comes
exclusively from `--secret_arn`.

This is deliberate. The server calls `GetSecretValue` under its own IAM role, so a
model-supplied ARN would make the tool a general-purpose Secrets Manager read oracle for
every secret that role can reach — and combined with an unconstrained `db_endpoint`, a
fetched master password could be directed at an arbitrary host. `oracle-mcp-server`
forbids the same thing for the same reason.

```bash
# Single instance — the ARN applies to any target
--secret_arn arn:aws:secretsmanager:us-east-1:111122223333:secret:db2-prod

# Several instances — pin each, optionally with a bare default for the rest
--secret_arn db2-prod=arn:aws:secretsmanager:...:secret:prod \
--secret_arn db2-dev=arn:aws:secretsmanager:...:secret:dev \
--secret_arn arn:aws:secretsmanager:...:secret:fallback
```

Resolution order: pinned ARN → bare default → the RDS managed master secret discovered via
`DescribeDBInstances`. At most one bare default may be given.

### Data types in results

Two conversions trade convenience for correctness:

| Db2 type | Returned as | Why |
|---|---|---|
| `DECIMAL`, `DECFLOAT` | JSON **string** (`"12345678901234567890.12"`) | Db2 holds up to 31 significant digits; an IEEE double holds ~15–17. Returning a float silently corrupted money columns. JSON has no exact decimal type, so a string is the only lossless option. |
| `BLOB`, `BINARY`, `VARBINARY` | **base64** string | Binary is not text. Decoding with `errors='replace'` was lossy, not an encoding — a 1 MiB BLOB came back 50% U+FFFD. |
| non-finite `DOUBLE`/`DECFLOAT` (`NaN`, `Infinity`) | `null` | Not representable in JSON; emitting them bare produced output that is not valid JSON. |

Note the asymmetry this creates: a `DECIMAL` column renders as a JSON string while a
`DOUBLE` column renders as a JSON number. That is intentional — an exact string beats a
confidently wrong number.

## The deploy → connect → query journey

1. **Deploy** with the `rds-db2-provision-skill` (Terraform). It produces an instance with a
   managed master-user secret and SSL on 50443. Note its endpoint and master-secret ARN.
2. **Connect** with `connect_to_database`.
3. **Query** with `run_query` / `get_table_schema`.

## Related resources

- `rds-db2-provision-skill` — the Terraform composer that deploys RDS for Db2.
- [Deploying Amazon RDS for Db2 using Terraform](https://aws.amazon.com/blogs/database/deploying-amazon-rds-for-db2-using-terraform/) — the published Terraform modules the provisioning skill reuses.
- [Create a monitoring dashboard for Amazon RDS for Db2](https://aws.amazon.com/blogs/database/create-monitoring-dashboard-for-amazon-rds-for-db2/) — basis for a planned phase-2 monitoring toolset.
- [Connect to Amazon RDS for Db2 from your laptop](https://aws.amazon.com/blogs/database/connect-to-amazon-rds-for-db2-from-your-laptop/) — EC2 + SSM tunnel approach used in the testing section above.
- [`aws-samples/sample-rds-db2-tools`](https://github.com/aws-samples/sample-rds-db2-tools) — companion scripts, skills, and examples.

## Development

```shell
uv venv && uv sync --all-groups
uv run --frozen pytest --cov --cov-branch --cov-report=term-missing   # unit tests
uv run --frozen pytest -m live                                        # live tests (needs an instance)
```

## Testing over an SSM tunnel

RDS for Db2 instances are private (not publicly accessible), so a laptop usually
cannot reach the endpoint on 50443 directly. Use a small EC2 instance in the same
VPC as an SSM port-forwarding proxy — no inbound ports or SSH keys required.

Reference: [Connect to Amazon RDS for Db2 from your laptop](https://aws.amazon.com/blogs/database/connect-to-amazon-rds-for-db2-from-your-laptop/).

Prerequisites: an SSM-managed EC2 in the instance's VPC whose security group is
allowed to reach the DB on the SSL port (50443), the AWS CLI **Session Manager
plugin**, and the RDS regional certificate bundle (PEM).

```shell
# 1. Download the regional certificate bundle
curl -o us-east-1-bundle.pem https://truststore.pki.rds.amazonaws.com/us-east-1/us-east-1-bundle.pem

# 2. Open a port-forwarding tunnel: local 50443 -> RDS endpoint:50443 via the EC2 proxy
aws ssm start-session --region us-east-1 --target <ec2-instance-id> \
  --document-name AWS-StartPortForwardingSessionToRemoteHost \
  --parameters host="<rds-endpoint>",portNumber="50443",localPortNumber="50443"
```

The Db2 v12 client driver defaults `SSLClientHostnameValidation` to `BASIC`, so it
validates the certificate's hostname. Over a tunnel you connect to `127.0.0.1`,
which cannot match the certificate CN — that fails with `SQL20576N`. For tunnel
testing, disable hostname validation with `--ssl_hostname_validation off` (the
`RDS_DB2_SSL_HOSTNAME_VALIDATION=off` env var below). Keep it on in production,
where the server connects to the real endpoint and the hostname matches.

```shell
# 3. Run the live test through the tunnel
RDS_DB2_ENDPOINT=127.0.0.1 \
RDS_DB2_PORT=50443 \
RDS_DB2_REGION=us-east-1 \
RDS_DB2_DATABASE=DB2DB \
RDS_DB2_SECRET_ARN=<master-user-secret-arn> \
RDS_DB2_SSL=require \
RDS_DB2_SSL_HOSTNAME_VALIDATION=off \
RDS_DB2_SSL_CERT=$(pwd)/us-east-1-bundle.pem \
uv run --frozen pytest -m live -q
```

Clean up afterward: end the SSM session (Ctrl-C). No `/etc/hosts` change is needed.

## Security

- Credentials are read from AWS Secrets Manager and are never logged.
- Connections use SSL by default; prefer keeping `--ssl_encryption require`.
- The server is read-only unless `--allow_write_query` is explicitly set.

## License

Licensed under the Apache License, Version 2.0.
