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
SSL Certificates: Valid server certificates for HTTPS
VCluster Web Service running: required for retrieving nodes, info, and so forth. Starting the MCP server starts the VCluster Web Service if you use the default configuration.

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.
5443: Database port.

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

There are three methods to start the MCP server: with the default configuration, with a custom configuration file, or you can override the default config with Environment Variables.

Starting the MCP server with the default configuration

Run the following command to start the MCP server with default settings:

/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

Starting the MCP server with a custom configuration file

./mcp_server --config /path_to_custom_config.yaml

Overriding the default config with Environment Variables

You can set environment variables to override default configuration settings. For example:

export MCP_SERVER_ADDR=":8667"
export MCP_LOG_DIR="/var/log/mcp"
export VERTICA_HOST="192.168.1.100"
export VERTICA_PORT="5434"
./mcp_server

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:

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

Tokens 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
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://qalcloud-041:8667/mcp",
        "--insecure",
        "--tls-skip-verify",
        "--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.

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"