Teradata Workload Management (WLM) Server

Python 3.13+ MCP

A Model Control Protocol (MCP) server for Teradata Workload Management (WLM) that provides comprehensive monitoring and management capabilities for Teradata systems.

Features

• 41 Management Tools: 28 core monitoring tools + 13 configuration management tools
• 39 MCP Resources: Reference data, templates, ruleset exploration, and workflow guidance
• Full Teradata Connectivity: TD2, LDAP, Kerberos, TDNEGO, JWT authentication
• TLS/SSL Support: ALLOW, PREFER, REQUIRE, VERIFY-CA, VERIFY-FULL modes
• Per-Tool Audit Trail: QueryBand tracking per tool call for workload management
• Async DB Operations: All database calls run in thread pool (non-blocking)
• Connection Resilience: Intelligent retry with exponential backoff and jitter
• Connection Health Monitoring: Automatic health checks and recovery
• OAuth 2.1 Authentication: Optional Keycloak integration with scope-based access control
• Template-Driven Configuration: Pre-built patterns for common TDWM configurations
• Generic Error Messages: No raw database errors leaked to clients

Use Cases

Performance Troubleshooting

Scenario: Identify why queries are running slowly or getting delayed

Tools to use:

1. show_sessions - Identify active sessions and their state
2. identify_blocking - Find sessions causing blocks
3. show_sql_text_for_session - See what SQL is running
4. monitor_amp_load - Check AMP utilization
5. display_delay_queue - See if queries are delayed
6. show_tasm_statistics - Analyze TASM workload distribution

Resources to explore:

• tdwm://summary - Overall system status
• tdwm://throttle-statistics - Current throttle limits and delays

Emergency Response

Scenario: System is overloaded, need to quickly restrict workload

Workflow:

1. Use tdwm://workflow/emergency-throttle resource for step-by-step guide
2. Use tdwm://template/throttle/application-basic for quick throttle pattern
3. create_system_throttle with low concurrency limit
4. enable_throttle to activate
5. activate_ruleset to apply changes immediately
6. Monitor with show_trottle_statistics

Scheduled Maintenance

Scenario: Block user access during maintenance window

Workflow:

1. Use tdwm://workflow/maintenance-window for complete guide
2. Create filter with create_filter targeting all users or specific applications
3. enable_filter + activate_ruleset to block access
4. Perform maintenance
5. disable_filter + activate_ruleset to restore access

Installation

pip install tdwm-mcp

Quick Start

# 1. Install the package
pip install tdwm-mcp

# 2. Configure database connection
export DATABASE_URI="teradata://username:password@hostname/database"

# 3. Start the MCP server
uv run tdwm-mcp

# 4. Test with a simple tool call (via your MCP client)
# Tool: show_sessions

Configuration

All configuration is done via environment variables. See .env.example for a complete reference.

Database Connection

# Connection URL (required)
export DATABASE_URI="teradata://username:password@hostname/database"

Authentication Mechanisms

The server supports all Teradata authentication methods via the DB_LOGMECH environment variable:

TD2 (Default - Username/Password)

export DATABASE_URI="teradata://username:password@hostname/database"
# DB_LOGMECH=TD2 is the default, no need to set explicitly

LDAP

export DATABASE_URI="teradata://@hostname/database"
export DB_LOGMECH=LDAP
export DB_LOGDATA="[email protected] password=ldap_password"

Directory	authcid Format
Active Directory (Simple)	[email protected]
Active Directory (DIGEST-MD5)	DOMAIN\username
OpenLDAP / Sun DS	username

Kerberos (KRB5)

export DATABASE_URI="teradata://@hostname/database"
export DB_LOGMECH=KRB5
# Requires valid Kerberos ticket (kinit)

TDNEGO (Token Negotiation)

export DB_LOGMECH=TDNEGO
# Negotiates between TD2, LDAP, KRB5 automatically

JWT (JSON Web Token)

export DATABASE_URI="teradata://@hostname/database"
export DB_LOGMECH=JWT
export DB_LOGDATA="authcid=token_issuer password=jwt_token_string"

TLS/SSL Configuration

# SSL mode: ALLOW, PREFER, REQUIRE, VERIFY-CA, VERIFY-FULL
export DB_SSL_MODE=REQUIRE

# Transport encryption (default: true)
export DB_ENCRYPT_DATA=true

Mode	Description
ALLOW	Try SSL, fall back to plaintext
PREFER	Prefer SSL, but allow plaintext
REQUIRE	Require SSL, fail if not available
VERIFY-CA	Verify CA certificate
VERIFY-FULL	Verify CA and hostname

Transport Configuration

# Transport: stdio (default), sse, streamable-http
export MCP_TRANSPORT=stdio
export MCP_HOST=0.0.0.0
export MCP_PORT=8000
export MCP_PATH=/mcp/

CORS Configuration

# Allowed origins (default: * for all)
export CORS_ALLOWED_ORIGINS="https://your-app.example.com"

Retry Configuration

# Tool-level retry
export TOOL_MAX_RETRIES=2
export TOOL_RETRY_INITIAL_DELAY=0.5
export TOOL_MAX_RETRY_DELAY=2.0

# Connection-level retry
export DB_MAX_RETRIES=3
export DB_INITIAL_BACKOFF=1.0
export DB_MAX_BACKOFF=30.0

OAuth 2.1 (Optional)

export OAUTH_ENABLED=true
export KEYCLOAK_URL=https://your-keycloak-server.com
export KEYCLOAK_REALM=your-realm
export KEYCLOAK_CLIENT_ID=tdwm-mcp
export KEYCLOAK_CLIENT_SECRET=your-secret
export OAUTH_RESOURCE_SERVER_URL=https://your-mcp-server.com
export OAUTH_REQUIRED_SCOPES=tdwm:read,tdwm:monitor
export OAUTH_REQUIRE_HTTPS=true

Scopes: tdwm:read, tdwm:write, tdwm:admin, tdwm:query, tdwm:monitor, tdwm:workload

Docker

All docker-compose files use environment variable expansion - no credentials are hardcoded. Create a .env file from .env.example before running.

# Basic SSE transport
cp .env.example .env
# Edit .env with your credentials
docker compose up -d

# With OAuth (Keycloak)
docker compose -f docker-compose.oauth.yml up -d

# SSE on alternate port
docker compose -f docker-compose.sse.yml up -d

Connection Resilience

The server includes automatic retry logic for handling Teradata connection failures.

How It Works

All tools are wrapped with an intelligent retry decorator that:

1. Detects Connection Errors - Distinguishes between connection failures (retryable) and SQL errors (not retryable)
2. Smart Retry Logic - Based on operation safety:
   • Read operations (queries, monitoring): Up to 2 retries
   • Write operations (creates, updates): Up to 1 retry
   • Dangerous operations (deletes, aborts): No automatic retry
3. Exponential Backoff - Progressive delays (0.5s -> 1.0s -> 2.0s) with jitter
4. Health Monitoring - Connection health checks every 5 minutes via SELECT 1

Detected Teradata Errors

• Network timeouts and disconnections
• Session disconnections (Error 3126)
• Transaction aborts due to TDWM termination (Error 2631)
• Session limit exceeded (Error 8017)
• Communication link failures

Per-Tool QueryBand

Every tool call sets a transaction-level QueryBand for audit and workload management:

SET QUERY_BAND = 'ApplicationName=TDWM_MCP;ToolName=show_sessions;Transport=stdio;' FOR TRANSACTION

This enables Teradata administrators to track which MCP tool initiated each query.

Architecture

+------------------------------------------------------------+
|                    MCP Client                               |
|              (Claude Desktop, etc.)                         |
+----------------------------+-------------------------------+
                             | MCP Protocol
+----------------------------v-------------------------------+
|                TDWM MCP Server                              |
|  +------------------------------------------------------+  |
|  |   server.py - FastMCP App + Settings                 |  |
|  +------------------------------------------------------+  |
|                             |                               |
|  +------------------------------------------------------+  |
|  |   fnc_common.py                                      |  |
|  |   - Connection Manager   - QueryBand helper          |  |
|  |   - get_connection()     - _set_queryband()          |  |
|  |   - @with_connection_retry decorator                 |  |
|  +------+-------------------+---------------------------+  |
|         |                   |                               |
|  +------v------+  +--------v---------+  +--------------+   |
|  | fnc_tools   |  | fnc_tools_       |  | fnc_         |   |
|  | .py         |  | priority1.py     |  | resources.py |   |
|  |             |  |                  |  |              |   |
|  | 28 core     |  | 13 config        |  | 39           |   |
|  | monitoring  |  | management       |  | resources    |   |
|  | tools       |  | tools            |  |              |   |
|  +------+------+  +--------+---------+  +------+-------+   |
|         |                  |                    |            |
|  +------v------------------v--------------------v--------+  |
|  |   settings.py      queryband.py     retry_utils.py   |  |
|  |   connection_manager.py             tdsql/tdsql.py    |  |
|  +-------------------------------------------------------+  |
+----------------------------+---------------------------------+
                             | teradatasql (TD2/LDAP/KRB5/JWT)
+----------------------------v---------------------------------+
|                 Teradata Database                             |
|               (TDWM/TASM System)                             |
+--------------------------------------------------------------+

Module Organization

Module	Purpose
settings.py	Frozen Settings dataclass - all env vars read once at startup
queryband.py	Per-tool QueryBand builder with value sanitization
tdsql/tdsql.py	Teradata connection wrapper with LOGMECH/TLS support
connection_manager.py	Connection health monitoring, retry, reconnection
retry_utils.py	@with_connection_retry() decorator, error detection
fnc_common.py	Shared utilities, connection access, queryband helper
fnc_tools.py	28 core monitoring tools (async, with queryband)
fnc_tools_priority1.py	13 configuration management tools
fnc_resources.py	39 MCP resource handlers
resource_reference.py	Reference data resources (8)
resource_templates.py	Configuration templates (13)
resource_queries.py	Ruleset exploration queries (8)
server.py	FastMCP app, transport setup, initialization
oauth_context.py	OAuth scope-based authorization
auth/	OAuth 2.1 config, middleware, endpoints, metadata

Dependency Graph

settings.py, queryband.py, retry_utils.py (no internal deps)
    |
tdsql/tdsql.py (uses settings)
    |
connection_manager.py (uses tdsql, queryband)
    |
fnc_common.py (uses connection_manager, queryband, retry_utils)
    |
    +-- fnc_tools_priority1.py (uses fnc_common)
    |       |
    +-- fnc_tools.py (uses fnc_common + fnc_tools_priority1)
    |       |
    +-- fnc_resources.py (uses fnc_common)
            |
        server.py (uses all above + settings + auth)

Available Tools

Session Management

• show_sessions - Show my active sessions
• show_sql_steps_for_session - Show SQL execution steps for a specific session
• show_sql_text_for_session - Show SQL text for a specific session
• abort_sessions_user - Abort all sessions for a specific user
• identify_blocking - Identify users causing blocking situations

Query Band and Monitoring

• monitor_session_query_band - Monitor query band for a specific session
• list_query_band - List query bands by type (TRANSACTION, PROFILE, SESSION, or ALL)
• show_query_log - Show query log for a specific user

System Resource Monitoring

• show_physical_resources - Monitor system physical resources
• monitor_amp_load - Monitor AMP (Access Module Processor) load
• monitor_awt - Monitor AWT (AMP Worker Task) resources
• monitor_config - Monitor virtual configuration

Workload Management

• list_active_WD - List active workloads (WD)
• list_WD - List all workloads (WD)
• show_tdwm_summary - Show workloads summary information

Delay Queue Management

• list_delayed_request - List all delayed queries
• abort_delayed_request - Abort delayed requests for a specific session
• display_delay_queue - Display delay queue details by type (WORKLOAD, SYSTEM, UTILITY, or ALL)
• release_delay_queue - Release delayed requests for a session or user

Throttle and Performance

• show_trottle_statistics - Show throttle statistics (ALL, QUERY, SESSION, WORKLOAD)
• list_utility_stats - List statistics for utility usage on the system

System Information

• show_cod_limits - Show COD (Capacity On Demand) limits
• show_top_users - Show users consuming the most resources
• show_sw_event_log - Show system software event logs (OPERATIONAL or ALL)

TASM (Teradata Active System Management)

• tdwm_list_clasification - List classification types for workload (TASM) rules
• show_tasm_statistics - Show TASM performance statistics
• show_tasm_even_history - Show TASM event history
• show_tasm_rule_history_red - Show what caused the system to enter RED state

Configuration Management

Throttle Management

• create_system_throttle - Create system-level throttle with concurrency limits
• modify_throttle_limit - Dynamically adjust throttle concurrency limits
• delete_throttle - Remove throttle definition
• enable_throttle - Activate throttle rule
• disable_throttle - Deactivate throttle rule

Filter Management

• create_filter - Create filter to block/reject queries
• delete_filter - Remove filter definition
• enable_filter - Activate filter rule
• disable_filter - Deactivate filter rule

Rule Management

• add_classification_to_rule - Add classification criteria to any rule
• add_subcriteria_to_target - Add sub-criteria (e.g., FTSCAN for TABLE)
• activate_ruleset - Apply all pending changes to make them live
• list_rulesets - List all available rulesets

Available Resources

Reference Data Resources

• tdwm://reference/classification-types - All 31 classification types with categories
• tdwm://reference/classification-types/{category} - Filter by category
• tdwm://reference/operators - Classification operators (I, O, IO)
• tdwm://reference/subcriteria-types - Sub-criteria types (FTSCAN, MINSTEPTIME, etc.)
• tdwm://reference/actions - Filter action types (E=Exception, A=Abort)
• tdwm://reference/throttle-types - Throttle types (DM, M)
• tdwm://reference/states - TASM system states (GREEN, YELLOW, ORANGE, RED)
• tdwm://reference/catalog - Comprehensive catalog of all reference resources

Configuration Templates

• tdwm://templates/throttle - List all throttle templates
• tdwm://template/throttle/{template_id} - Specific throttle template
• tdwm://templates/filter - List all filter templates
• tdwm://template/filter/{template_id} - Specific filter template
• tdwm://templates/catalog - Templates catalog

Ruleset Exploration

• tdwm://rulesets - List all available rulesets
• tdwm://system/active-ruleset - Get currently active ruleset name
• tdwm://ruleset/{ruleset_name} - Detailed ruleset information
• tdwm://ruleset/{name}/throttles - List throttles in ruleset
• tdwm://ruleset/{name}/throttle/{throttle_name} - Throttle details
• tdwm://ruleset/{name}/filters - List filters in ruleset
• tdwm://ruleset/{name}/filter/{filter_name} - Filter details
• tdwm://ruleset/{name}/pending-changes - Check pending changes

Workflow Templates

• tdwm://workflows - List all available workflows
• tdwm://workflow/create-throttle - Complete throttle creation workflow
• tdwm://workflow/create-filter - Complete filter creation workflow
• tdwm://workflow/maintenance-window - Enable/disable filters for maintenance
• tdwm://workflow/emergency-throttle - Quick emergency response workflow
• tdwm://workflow/modify-existing-throttle - Modify existing throttle workflow

Legacy Resources

• tdwm://sessions, tdwm://workloads, tdwm://active-workloads, tdwm://summary
• tdwm://delayed-queries, tdwm://throttle-statistics, tdwm://physical-resources
• tdwm://amp-load, tdwm://classification-types

Usage Examples

Basic Session Monitoring

await call_tool("show_sessions")
await call_tool("show_sql_text_for_session", {"sessionNo": 1234})
await call_tool("show_sql_steps_for_session", {"sessionNo": 1234})

Create a System Throttle

await call_tool("create_system_throttle", {
    "ruleset_name": "MyFirstConfig",
    "throttle_name": "ETL_THROTTLE",
    "description": "Limit ETL workload concurrency",
    "throttle_type": "DM",
    "limit": 5,
    "classification_criteria": [
        {"description": "ETL App", "type": "APPL", "value": "ETL_APP", "operator": "I"}
    ]
})

Create a Maintenance Window Filter

await call_tool("create_filter", {
    "ruleset_name": "MyFirstConfig",
    "filter_name": "MAINTENANCE_BLOCK",
    "description": "Block non-critical queries during maintenance",
    "classification_criteria": [
        {"description": "Reporting Users", "type": "USER", "value": "reporting_user", "operator": "I"}
    ],
    "action": "E"
})

Template-Driven Configuration

# 1. Read template
template = await read_resource("tdwm://template/throttle/application-basic")

# 2. Get active ruleset
active = await read_resource("tdwm://system/active-ruleset")

# 3. Create throttle using template structure
await call_tool("create_system_throttle", { ... })

# 4. Activate
await call_tool("activate_ruleset", {"ruleset_name": "MyFirstConfig"})

# 5. Verify
result = await read_resource("tdwm://ruleset/MyFirstConfig/throttle/MY_THROTTLE")

Security

Credential Handling

• Database credentials via environment variables only (never hardcoded)
• Passwords obfuscated in all log messages and error outputs via obfuscate_password()
• Client-facing errors are generic ("Check server logs for details") - no raw exceptions leaked
• Docker compose files use ${VAR} expansion - no real credentials in version control

SQL Safety

• All user-provided parameters use parameterized queries (? placeholders)
• TDWM stored procedures called via CALL ... (?, ?, ?) pattern
• Session numbers validated as integers before use in MonitorSQLSteps / MonitorSQLText

OAuth 2.1

• JWT validation with RS256/RS384/RS512/ES256/ES384/ES512
• Token introspection fallback via Keycloak
• Scope-based access control per tool (read/write/admin/query/monitor)
• HTTPS enforcement configurable via OAUTH_REQUIRE_HTTPS

Async Safety

• All DB operations wrapped in asyncio.to_thread() (event loop never blocked)
• Connection manager uses asyncio.Lock() to prevent concurrent reconnection races
• Per-tool QueryBand set as FOR TRANSACTION (not session-level, no cross-tool leakage)

Development

Project Structure

tdwm-mcp/
+-- src/tdwm_mcp/
|   +-- __init__.py
|   +-- server.py                  # FastMCP app and initialization
|   +-- settings.py                # Frozen Settings dataclass
|   +-- queryband.py               # Per-tool QueryBand builder
|   +-- fnc_common.py              # Shared utilities & connection management
|   +-- fnc_tools.py               # Core monitoring tools (28 tools)
|   +-- fnc_tools_priority1.py     # Configuration management (13 tools)
|   +-- fnc_resources.py           # Resource routing (39 resources)
|   +-- fnc_prompts.py             # MCP prompts
|   +-- retry_utils.py             # Retry decorator & error detection
|   +-- connection_manager.py      # Connection health management
|   +-- resource_reference.py      # Reference data resources
|   +-- resource_templates.py      # Configuration templates
|   +-- resource_queries.py        # Ruleset exploration queries
|   +-- tdwm_static.py             # Static reference data
|   +-- oauth_context.py           # OAuth scope-based authorization
|   +-- tdsql/
|   |   +-- __init__.py
|   |   +-- tdsql.py               # Teradata SQL wrapper (LOGMECH/TLS)
|   +-- auth/
|       +-- config.py              # OAuth configuration
|       +-- middleware.py           # JWT validation middleware
|       +-- endpoints.py           # OAuth HTTP endpoints
|       +-- metadata.py            # Protected resource metadata
+-- docker-compose.yml             # Basic SSE transport
+-- docker-compose.oauth.yml       # With Keycloak integration
+-- docker-compose.sse.yml         # SSE on alternate port
+-- Dockerfile
+-- .env.example                   # Environment variable reference
+-- pyproject.toml
+-- uv.lock
+-- README.md

Adding New Tools

# In fnc_tools.py

@with_connection_retry()
async def my_new_tool(param1: str) -> ResponseType:
    """Description of what the tool does."""
    tdconn = await get_connection()
    def _run():
        _set_queryband(tdconn, "my_new_tool")
        cur = tdconn.cursor()
        rows = cur.execute("SELECT * FROM my_table WHERE col1 = ?", [param1])
        return format_text_response(list(rows.fetchall()))
    try:
        return await asyncio.to_thread(_run)
    except ConnectionError:
        raise
    except Exception as e:
        logger.error(f"Error in my_new_tool: {e}")
        return format_error_response("Failed to execute. Check server logs for details.")

# Register in handle_list_tools() and handle_tool_call()

Key patterns:

• Wrap DB work in asyncio.to_thread(_run) (non-blocking)
• Call _set_queryband(tdconn, "tool_name") for audit trail
• Re-raise ConnectionError for retry decorator
• Return generic error messages (never str(e))

Testing

uv sync
export DATABASE_URI="teradata://user:pass@host/db"
uv run tdwm-mcp

# Test with MCP Inspector
npx @modelcontextprotocol/inspector uv run tdwm-mcp

Dependencies

• teradatasql>=20.0.0.30 - Teradata SQL driver
• mcp[cli]>=1.12.3 - Model Control Protocol framework
• fastapi>=0.115.12 - HTTP framework for SSE/streamable-http
• pydantic>=2.11.4 - Data validation
• uvicorn>=0.34.2 - ASGI server
• pyjwt[crypto]>=2.8.0 - JWT token handling
• authlib>=1.2.0 - OAuth 2.1 client
• aiohttp>=3.9.0 - Async HTTP for token introspection
• httpx>=0.24.0 - HTTP client