This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

MCP server

The OpenText Analytics Database MCP server is a secure, JWT-authenticated server that implements Model Context Protocol (MCP) for database interactions between your database and AI agents.

1: Claude Desktop example
2: Curl examples

The OpenText™ Analytics Database MCP server is a secure, JWT-authenticated server that implements Model Context Protocol (MCP) for database interactions between your database and AI agents. It provides a tool for executing queries, managing job queues, and interacting with the database through APIs.

Prerequisites

System requirements

Operating System: Linux (Ubuntu/RHEL/CentOS).
OpenText™ Analytics Database: Already running and accessible.
VCluster Web Service running: required to retrieve node and cluster information. When using the default configuration, starting the MCP server automatically starts the VCluster Web Service.

Note

The MCP server is not supported on Kubernetes in the 26.1 release, but will be supported in later releases.

Network requirements

The following ports must be accessible through the firewall:

8667: Default port for MCP server. Must be open for HTTPS connection.
8665: Default for VCluster services.

Database access

Valid database credentials.
Database user with appropriate permissions to execute intended queries.
Network connectivity to the database server.

Required files and directories

Ensure the following files and directories exist before starting the MCP server:

SSL Certificate (default location: /opt/vertica/config/vcluster_server/):
- server.pem
- server.key
- rootca.pem
Configuration Directory (this is auto-created if it is missing):
- /opt/vertica/config/
- /opt/vertica/config/mcp_server/
Log Directory (this is auto-created if it is missing):
- /opt/vertica/log/

Starting the MCP server

Run the following command to start the MCP server:

/opt/vertica/bin/manage_vcluster_server start

This starts both the MCP server and VCluster server.

To start only the MCP server:

/opt/vertica/bin/manage_vcluster_server start mcp_server

Note

You can customize your MCP server configuration by editing /opt/vertica/config/mcp_server.yaml.

Log files for the MCP server are located by default at /opt/vertica/log/mcp_server.log and /opt/vertica/log/mcp_audit.log.

Obtaining JWT tokens

After starting the MCP server, JWT tokens are required for authentication. Each token contains encrypted database credentials and user information. You can either use the interactive generator to create tokens or input parameters in the command line.

Generating a token using the interactive generator

Run:

/opt/vertica/bin/vertica_mcp_server --generate-token

The following are the prompts (with example inputs):

Enter user ID: dbadmin
Enter database name: mydb
Enter password: ********
Enter description (optional): Database Administrator
Enter expiration days (default 365): 365

Generating a token using command line inputs

Run (with all parameters specified):

./mcp_server --generate-token \
  --userid dbadmin \
  --dbname mydb \
  --dbpass mypassword \
  --description "Data Analyst" \
  --expiration-days 90

Token output

Regardless of the method used, the output will be a JWT token string that can be used for authenticating requests to the MCP server:

Generated JWT Token:
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VyaWQiOiJkYmFkbWluIiwiZGJfbmFtZSI6Im15ZGIiLCJwYXNzd29yZCI6ImVuY3J5cHRlZF9wYXNzd29yZCIsImlhdCI6MTczMjI0MzYzMSwibmJmIjoxNzMyMjQzNjMxLCJleHAiOjE3NjM3Nzk2MzF9.abc123...

Client added to /opt/vertica/config/mcp_server/client.yaml
Status: active

Token signatures are stored in /opt/vertica/config/mcp_server/client.yaml and you can manage them there.

Token security best practices

Use short expiration times for sensitive environments (30-90 days).
Store tokens securely - treat them like passwords.
Never commit tokens to version control.
Rotate tokens regularly - regenerate before expiration.
Use HTTPS only - never send tokens over unencrypted connections.
Monitor audit logs - review authentication attempts.

1 - Claude Desktop example

Examples for configuring and using the OpenText Analytics Database MCP server with Claude Desktop.

This page provides examples for configuring the OpenText™ Analytics Database MCP server with Claude Desktop.

Configuring MCP server with Claude Desktop

Claude Desktop can connect to your MCP server to enable database query capabilities within Claude conversations.

Prerequisites

Claude Desktop installed (latest version).
MCP server running and accessible.
Valid JWT token for authentication.
Certificates to access VCluster server from the database administrator. For database adminstrators, the default certificates are located at /opt/vertica/config/vcluster_server/.
NPX tool mcp-remote (npm install -g mcp-remote).

Configuration

The Claude Desktop configuration file location varies by OS:

macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
Windows: %APPDATA%\Claude\claude_desktop_config.json
Linux: ~/.config/Claude/claude_desktop_config.json

Edit the configuration file and add your MCP server. Example configuration:

{
  "mcpServers": {
    "vertica-mcp-server": {
      "command": "npx",
      "args": [
        "mcp-remote",
        "https://ip-address/mcp",
        "--insecure",
        "--tls-skip-verify",
        "--cert",
        "<path_to_certificate>\\certfile.pem",
        "--key",
        "<path_to_key_file>\\keyfile.key",
        "--ca-cert".
        "<path_to_ca_cert>\\ca.pem",
        "--header",
        "X-API-Key:${API_KEY_VALUE}"
        ],
      "env": {
        "NODE_TLS_REJECT_UNAUTHORIZED": "0",
        "API_KEY_VALUE": "replace with your API key"
      }
    }
  }
}

Verifying the configuration

Restart Claude Desktop after saving the configuration (make sure kill all background processes)
Check Claude's MCP status - look for your server in the MCP panel
Test with a simple query:
```
SELECT version()
```

2 - Curl examples

Examples for using the OpenText Analytics Database MCP server with curl for testing and automation.

This page provides curl examples for testing and automating interactions with the OpenText™ Analytics Database MCP server. This is provided as a developer guide for interacting with the APIs.

Using the MCP server with curl

For testing and automation, you can interact with the MCP server directly using curl.

Basic request format

All MCP requests use JSON-RPC 2.0 format:

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: YOUR_JWT_TOKEN" \
  -d '{
    "jsonrpc": "2.0",
    "method": "METHOD_NAME",
    "params": {
      "key": "value"
    },
    "id": 1
  }'

Initialize connection

First, initialize the MCP session:

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "initialize",
    "params": {
      "protocolVersion": "2024-11-05",
      "capabilities": {
        "roots": {
          "listChanged": true
        }
      },
      "clientInfo": {
        "name": "curl-client",
        "version": "1.0.0"
      }
    },
    "id": 1
  }'

Response:

{
  "jsonrpc": "2.0",
  "id": 1,
  "result": {
    "protocolVersion": "2024-11-05",
    "serverInfo": {
      "name": "vertica-mcp-server",
      "version": "1.0.0"
    },
    "capabilities": {
      "tools": {},
      "resources": {},
      "prompts": {}
    }
  }
}

List available tools

Discover what tools are available:

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/list",
    "params": {},
    "id": 2
  }'

Response:

{
  "jsonrpc": "2.0",
  "id": 2,
  "result": {
    "tools": [
      {
        "name": "execute_query",
        "description": "Execute SQL queries against Vertica database",
        "inputSchema": {
          "type": "object",
          "properties": {
            "query": {
              "type": "string",
              "description": "SQL query to execute"
            },
            "subcluster": {
              "type": "string",
              "description": "Optional subcluster name"
            }
          },
          "required": ["query"]
        }
      },
      {
        "name": "submit_queue_query",
        "description": "Submit long-running query to job queue"
      },
      {
        "name": "list_jobs",
        "description": "List all jobs in the queue"
      }
    ]
  }
}

Execute a query

Run a simple SQL query:

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "execute_query",
      "arguments": {
        "query": "SELECT version()"
      }
    },
    "id": 3
  }'

Response:

{
  "jsonrpc": "2.0",
  "id": 3,
  "result": {
    "content": [
      {
        "type": "text",
        "text": "{\"columns\":[\"version\"],\"rows\":[{\"version\":\"Vertica Analytic Database v24.3.0-0\"}],\"count\":1}"
      }
    ]
  }
}

Execute query with subcluster routing

Route query to a specific subcluster:

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "execute_query",
      "arguments": {
        "query": "SELECT node_name, subcluster_name FROM nodes",
        "subcluster": "analytics_cluster"
      }
    },
    "id": 4
  }'

Submit long-running query to queue

For complex analytics queries that take longer to complete:

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "submit_queue_query",
      "arguments": {
        "query": "SELECT customer_id, SUM(amount) FROM sales GROUP BY customer_id",
        "description": "Monthly sales by customer"
      }
    },
    "id": 5
  }'

Response:

{
  "jsonrpc": "2.0",
  "id": 5,
  "result": {
    "content": [
      {
        "type": "text",
        "text": "Job submitted successfully. Job ID: 550e8400-e29b-41d4-a716-446655440000"
      }
    ]
  }
}

Check job status

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "get_job",
      "arguments": {
        "job_id": "550e8400-e29b-41d4-a716-446655440000"
      }
    },
    "id": 6
  }'

List all jobs

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "list_jobs",
      "arguments": {}
    },
    "id": 7
  }'

Cancel a job

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "cancel_job",
      "arguments": {
        "job_id": "550e8400-e29b-41d4-a716-446655440000"
      }
    },
    "id": 8
  }'

Health check

Check server health without authentication:

curl -k https://localhost:8667/health

Response:

{
  "status": "healthy",
  "total_pools": 3,
  "max_pools": 100
}

Using jq for pretty output

Format JSON responses with jq:

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "execute_query",
      "arguments": {
        "query": "SELECT table_name FROM tables LIMIT 5"
      }
    },
    "id": 1
  }' | jq '.'

Bash script example

Create a reusable script for queries:

#!/bin/bash
# mcp_query.sh

API_KEY="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."
SERVER="https://localhost:8667/mcp"
QUERY="$1"

if [ -z "$QUERY" ]; then
    echo "Usage: $0 'SQL QUERY'"
    exit 1
fi

curl -k -X POST "$SERVER" \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: $API_KEY" \
  -d "{
    \"jsonrpc\": \"2.0\",
    \"method\": \"tools/call\",
    \"params\": {
      \"name\": \"execute_query\",
      \"arguments\": {
        \"query\": \"$QUERY\"
      }
    },
    \"id\": 1
  }" | jq -r '.result.content[0].text'

Usage:

chmod +x mcp_query.sh
./mcp_query.sh "SELECT COUNT(*) FROM customers"