Teradata MCP Server

Shows detailed column information about a database table via SQLAlchemy, bind parameters if provided (prepared SQL), and return the fully rendered SQL (with literals) in metadata.

Arguments: database_name - Database name obj_name - table or view name

Returns: ResponseType: formatted response with query results + metadata

Lists all databases in the Teradata System.

Returns: ResponseType: formatted response with query results + metadata

Execute a SQL query via SQLAlchemy, bind parameters if provided (prepared SQL), and return the fully rendered SQL (with literals) in metadata.

Arguments: sql - SQL text, with optional bind-parameter placeholders

Returns: ResponseType: formatted response with query results + metadata

Get tables commonly used together by database users, this is helpful to infer relationships between tables via SQLAlchemy, bind parameters if provided (prepared SQL), and return the fully rendered SQL (with literals) in metadata.

Arguments: database_name - Database name object_name - table or view name

Returns: ResponseType: formatted response with query results + metadata

Displays the DDL definition of a table via SQLAlchemy, bind parameters if provided (prepared SQL), and return the fully rendered SQL (with literals) in metadata.

Arguments: database_name - Database name table_name - table name

Returns: ResponseType: formatted response with query results + metadata

Lists all tables in a database.

Arguments: database_name - Database name

Returns: ResponseType: formatted response with query results + metadata

This function returns data sample and inferred structure from a database table or view via SQLAlchemy, bind parameters if provided (prepared SQL), and return the fully rendered SQL (with literals) in metadata.

Arguments: table_name - table or view name database_name - Database name

Returns: ResponseType: formatted response with query results + metadata

Measure the usage of a table and views by users in a given schema, this is helpful to infer what database objects are most actively used or drive most value via SQLAlchemy, bind parameters if provided (prepared SQL), and return the fully rendered SQL (with literals) in metadata.

Arguments: database_name - Database name

Returns: ResponseType: formatted response with query results + metadata

ANALYZE SQL QUERY CLUSTER PERFORMANCE STATISTICS

This tool analyzes pre-computed cluster statistics to identify optimization opportunities without re-running the clustering pipeline. Perfect for iterative analysis and decision-making on which query clusters to focus optimization efforts.

ANALYSIS CAPABILITIES:

• Performance Ranking: Sort clusters by any performance metric to identify top resource consumers

• Resource Impact Assessment: Compare clusters by CPU usage, I/O volume, and execution complexity

• Skew Problem Detection: Identify clusters with CPU or I/O distribution issues

• Workload Characterization: Understand query patterns by user, application, and workload type

• Optimization Prioritization: Focus on clusters with highest impact potential

AVAILABLE SORTING METRICS:

• avg_cpu: Average CPU seconds per cluster (primary optimization target)

• avg_io: Average logical I/O operations (scan intensity indicator)

• avg_cpuskw: Average CPU skew (distribution problem indicator)

• avg_ioskw: Average I/O skew (hot spot indicator)

• avg_pji: Average Physical-to-Logical I/O ratio (compute intensity)

• avg_uii: Average Unit I/O Intensity (I/O efficiency)

• avg_numsteps: Average query plan complexity

• queries: Number of queries in cluster (frequency indicator)

• cluster_silhouette_score: Clustering quality measure

PERFORMANCE CATEGORIZATION: Automatically categorizes clusters using configurable thresholds (from sql_opt_config.yml):

• HIGH_CPU_USAGE: Average CPU > config.performance_thresholds.cpu.high

• HIGH_IO_USAGE: Average I/O > config.performance_thresholds.io.high

• HIGH_CPU_SKEW: CPU skew > config.performance_thresholds.skew.high

• HIGH_IO_SKEW: I/O skew > config.performance_thresholds.skew.high

• NORMAL: Clusters within configured normal performance ranges

TYPICAL ANALYSIS WORKFLOW:

1. Sort by 'avg_cpu' or 'avg_io' to find highest resource consumers

2. Sort by 'avg_cpuskw' or 'avg_ioskw' to find distribution problems

3. Use limit_results to focus on top problematic clusters

OPTIMIZATION DECISION FRAMEWORK:

• High CPU + High Query Count: Maximum impact optimization candidates

• High Skew + Moderate CPU: Distribution/statistics problems

• High I/O + Low PJI: Potential indexing opportunities

• High NumSteps: Complex query rewriting candidates

OUTPUT FORMAT: Returns detailed cluster statistics with performance rankings, categories, and metadata for LLM analysis and optimization recommendations.

COMPLETE SQL QUERY CLUSTERING PIPELINE FOR HIGH-USAGE QUERY OPTIMIZATION

This tool executes the entire SQL query clustering workflow to identify and analyze high CPU usage queries for optimization opportunities. It's designed for database performance analysts and DBAs who need to systematically identify query optimization candidates.

FULL PIPELINE WORKFLOW:

1. Query Log Extraction: Extracts SQL queries from DBC.DBQLSqlTbl with comprehensive performance metrics

2. Performance Metrics Calculation: Computes CPU skew, I/O skew, PJI (Physical to Logical I/O ratio), UII (Unit I/O Intensity)

3. Query Tokenization: Tokenizes SQL text using {sql_clustering_config.get('model', {}).get('model_id', 'bge-small-en-v1.5')} tokenizer via ivsm.tokenizer_encode

4. Embedding Generation: Creates semantic embeddings using ivsm.IVSM_score with ONNX models

5. Vector Store Creation: Converts embeddings to vector columns via ivsm.vector_to_columns

6. K-Means Clustering: Groups similar queries using TD_KMeans with optimal K from configuration

7. Silhouette Analysis: Calculates clustering quality scores using TD_Silhouette

8. Statistics Generation: Creates comprehensive cluster statistics with performance aggregations

PERFORMANCE METRICS EXPLAINED:

• AMPCPUTIME: Total CPU seconds across all AMPs (primary optimization target)

• CPUSKW/IOSKW: CPU/I/O skew ratios (>2.0 indicates distribution problems)

• PJI: Physical-to-Logical I/O ratio (higher = more CPU-intensive)

• UII: Unit I/O Intensity (higher = more I/O-intensive relative to CPU)

• LogicalIO: Total logical I/O operations (indicates scan intensity)

• NumSteps: Query plan complexity (higher = more complex plans)

CONFIGURATION (from sql_opt_config.yml):

• Uses top {default_max_queries} queries by CPU time (configurable)

• Creates {default_optimal_k} clusters by default (configurable via optimal_k parameter)

• Embedding model: {sql_clustering_config.get('model', {}).get('model_id', 'bge-small-en-v1.5')}

• Vector dimensions: {sql_clustering_config.get('embedding', {}).get('vector_length', 384)}

• All database and table names are configurable

OPTIMIZATION WORKFLOW: After running this tool, use:

1. sql_Analyze_Cluster_Stats to identify problematic clusters

2. sql_Retrieve_Cluster_Queries to get actual SQL from target clusters

3. LLM analysis to identify patterns and propose specific optimizations

USE CASES:

• Identify query families consuming the most system resources

• Find queries with similar patterns but different performance

• Discover optimization opportunities through clustering analysis

• Prioritize DBA effort on highest-impact query improvements

• Understand workload composition and resource distribution

PREREQUISITES:

• DBC.DBQLSqlTbl and DBC.DBQLOgTbl must be accessible

• Embedding models and tokenizers must be installed in feature_ext_db

• Sufficient space in feature_ext_db for intermediate and final tables

RETRIEVE ACTUAL SQL QUERIES FROM SPECIFIC CLUSTERS FOR PATTERN ANALYSIS

This tool extracts the actual SQL query text and performance metrics from selected clusters, enabling detailed pattern analysis and specific optimization recommendations. Essential for moving from cluster-level analysis to actual query optimization.

DETAILED ANALYSIS CAPABILITIES:

• SQL Pattern Recognition: Analyze actual query structures, joins, predicates, and functions

• Performance Correlation: Connect query patterns to specific performance characteristics

• Optimization Identification: Identify common anti-patterns, missing indexes, inefficient joins

• Code Quality Assessment: Evaluate query construction, complexity, and best practices

• Workload Understanding: See actual business logic and data access patterns

QUERY SELECTION STRATEGIES:

• By CPU Impact: Sort by 'ampcputime' to focus on highest CPU consumers

• By I/O Volume: Sort by 'logicalio' to find scan-intensive queries

• By Skew Problems: Sort by 'cpuskw' or 'ioskw' for distribution issues

• By Complexity: Sort by 'numsteps' for complex execution plans

• By Response Time: Sort by 'response_secs' for user experience impact

AVAILABLE METRICS FOR SORTING:

• ampcputime: Total CPU seconds (primary optimization target)

• logicalio: Total logical I/O operations (scan indicator)

• cpuskw: CPU skew ratio (distribution problems)

• ioskw: I/O skew ratio (hot spot indicators)

• pji: Physical-to-Logical I/O ratio (compute intensity)

• uii: Unit I/O Intensity (I/O efficiency)

• numsteps: Query execution plan steps (complexity)

• response_secs: Wall-clock execution time (user impact)

• delaytime: Time spent in queue (concurrency issues)

AUTOMATIC PERFORMANCE CATEGORIZATION: Each query is categorized using configurable thresholds (from sql_opt_config.yml):

• CPU Categories: VERY_HIGH_CPU (>config.very_high), HIGH_CPU (>config.high), MEDIUM_CPU (>10s), LOW_CPU

• CPU Skew: SEVERE_CPU_SKEW (>config.severe), HIGH_CPU_SKEW (>config.high), MODERATE_CPU_SKEW (>config.moderate), NORMAL

• I/O Skew: SEVERE_IO_SKEW (>config.severe), HIGH_IO_SKEW (>config.high), MODERATE_IO_SKEW (>config.moderate), NORMAL

Use thresholds set in config file for, CPU - high, very_high, Skew moderate, high, severe

TYPICAL OPTIMIZATION WORKFLOW:

1. Start with clusters identified from sql_Analyze_Cluster_Stats

2. Retrieve top queries by impact metric (usually 'ampcputime')

3. Analyze SQL patterns for common issues:

   • Missing WHERE clauses or inefficient predicates

   • Cartesian products or missing JOIN conditions

   • Inefficient GROUP BY or ORDER BY operations

   • Suboptimal table access patterns

   • Missing or outdated statistics

4. Develop specific optimization recommendations

QUERY LIMIT STRATEGY:

• Use the query limit set in config file for pattern recognition and analysis, unless user specifies a different limit

OUTPUT INCLUDES:

• Complete SQL query text for each query

• All performance metrics, user, application, and workload context, cluster membership and rankings

• Performance categories for quick filtering

Get the column summary statistics for a table.

Arguments: database_name - name of the database table_name - table name to analyze

Returns: ResponseType: formatted response with query results + metadata

Get the destinct categories from column in a table.

Arguments: database_name - name of the database table_name - table name to analyze column_name - column name to analyze

Returns: ResponseType: formatted response with query results + metadata

Get the column names that having missing values in a table.

Arguments: database_name - name of the database table_name - table name to analyze

Returns: ResponseType: formatted response with query results + metadata

Get the column names that having negative values in a table.

Arguments: database_name - name of the database table_name - table name to analyze

Returns: ResponseType: formatted response with query results + metadata

Get the rows with missing values in a table.

Arguments: database_name - name of the database table_name - table name to analyze column_name - column name to analyze

Returns: ResponseType: formatted response with query results + metadata

Get the standard deviation from column in a table.

Arguments: database_name - name of the database table_name - table name to analyze column_name - column name to analyze

Returns: ResponseType: formatted response with query results + metadata

Get the univariate statistics for a table.

Arguments: database_name - name of the database table_name - table name to analyze column_name - column name to analyze

Returns: ResponseType: formatted response with query results + metadata

Execute complete RAG workflow to answer user questions based on document context. This tool handles the entire RAG pipeline in a single step when a user query is tagged with /rag.

WORKFLOW STEPS (executed automatically):

1. Configuration setup using configurable values from rag_config.yml

2. Store user query with '/rag ' prefix stripping

3. Generate query embeddings using either BYOM (ONNXEmbeddings) or IVSM functions based on config

4. Perform semantic search against precomputed chunk embeddings

5. Return context chunks for answer generation

CONFIGURATION VALUES (from rag_config.yml):

• version: 'ivsm' or 'byom' to select embedding approach

• All database names, table names, and model settings are configurable

• Vector store metadata fields are dynamically detected

• Embedding parameters are configurable

• Default chunk retrieval count is configurable

• Default values are provided as fallback

TECHNICAL DETAILS:

• Strips the '/rag ' prefix if present from user questions

• Creates query table if it does not exist (columns: id, txt, created_ts)

• BYOM approach: Uses mldb.ONNXEmbeddings UDF for tokenization and embedding

• IVSM approach: Uses ivsm.tokenizer_encode and ivsm.IVSM_score functions

• Both approaches store embeddings in configured output table

• Uses cosine similarity via TD_VECTORDISTANCE for semantic search

• Returns the top-k matching chunks from the configured vector store

• Each result includes chunk text, similarity score, and metadata fields

CRITICAL ANSWERING RULES:

• Answer ONLY using retrieved chunks - no external knowledge, speculation, or inference

• Quote source content directly without paraphrasing, summarizing, or rewriting

• If no relevant context found: "Not enough information found in the provided context"

• If partial context: "The available context does not fully answer the question"

• Include document/page references when available (e.g., "On page 2 of 'demo_policy.pdf'...")

• Execute entire workflow silently without showing function calls to user - only show final answer

LANGUAGE RESTRICTIONS:

• Do not say "According to the context" or "The context says" - just quote directly

• Do not say "It can be inferred that..." - no inference allowed

• Use exact or near-verbatim quotes only

• No paraphrasing, summarizing, or adding transitions between quotes

• Copy content precisely - no expansion or interpretation

BEHAVIORAL REQUIREMENTS:

• Execute all RAG workflow steps silently without showing tool calls to users

• Only display final answers based strictly on retrieved context chunks

• Do not use external knowledge or speculation - only quote retrieved content directly

• RAG mode only triggers with explicit /rag prefix - do not enter RAG mode implicitly

• Hide all tool execution steps from user

• Provide clean, direct response with only the answer based on retrieved chunks

• This is a complete end-to-end RAG workflow - no additional tools needed

EXECUTION: Run completely silently - user only sees their question and the final context-grounded answer.

Get database space if database name is provided, otherwise get all databases space allocations.

Arguments: database_name - database name

Returns: ResponseType: formatted response with query results + metadata

Get the Teradata system usage summary metrics by weekday and hour for each workload type and query complexity bucket.

Arguments: dimensions - list of dimensions to aggregate the resource usage summary. All dimensions are: ["LogDate", "hourOfDay", "dayOfWeek", "workloadType", "workloadComplexity", "UserName", "AppId"] user_name - user name date - Date to analyze, formatted as YYYY-MM-DD dayOfWeek - day of the week to analyze hourOfDay - hour of day to analyze workloadType - workload type to analyze, example: 'LOAD', 'ETL/ELT', 'EXPORT', 'QUERY', 'ADMIN', 'OTHER' workloadComplexity - workload complexity to analyze, example: 'Ingest & Prep', 'Answers', 'System/Procedural' AppId - Application ID to analyze, example: 'TPTLOAD%', 'TPTUPD%', 'FASTLOAD%', 'MULTLOAD%', 'EXECUTOR%', 'JDBC%'

Get table space used for a table if table name is provided or get table space for all tables in a database if a database name is provided."

Arguments: database_name - database name table_name - table name

Returns: ResponseType: formatted response with query results + metadata

Get a list of SQL run against a table in the last number of days.

Arguments: table_name - table name no_days - number of days

Returns: ResponseType: formatted response with query results + metadata

Measure the usage of a table and views by users, this is helpful to understand what user and tables are driving most resource usage at any point in time.

Arguments: database_name - database name to analyze user_name - user name to analyze

Get a list of SQL run by a user in the last number of days if a user name is provided, otherwise get list of all SQL in the last number of days.

Arguments: user_name - user name no_days - number of days

Returns: ResponseType: formatted response with query results + metadata

Arguments: arguments - arguments to analyze

Returns: ResponseType: formatted response with query results + metadata

Function to generate a line plot for labels and columns. Columns mentioned in labels are used for x-axis and columns are used for y-axis.

PARAMETERS: table_name: Required Argument. Specifies the name of the table to generate the donut plot. Types: str

RETURNS: dict

Function to generate a pie chart plot for labels and columns. Columns mentioned in labels are used as labels and column is used to plot.

PARAMETERS: table_name: Required Argument. Specifies the name of the table to generate the donut plot. Types: str

RETURNS: dict

Function to generate a polar area plot for labels and columns. Columns mentioned in labels are used as labels and column is used to plot.

PARAMETERS: table_name: Required Argument. Specifies the name of the table to generate the donut plot. Types: str

RETURNS: dict

Function to generate a radar plot for labels and columns. Columns mentioned in labels are used as labels and column is used to plot.

PARAMETERS: table_name: Required Argument. Specifies the name of the table to generate the donut plot. Types: str

RETURNS: dict

Get permissions for a role.

Arguments: role_name - role name to analyze

Returns: ResponseType: formatted response with query results + metadata

Get permissions for a user.

Arguments: user_name - user name to analyze

Returns: ResponseType: formatted response with query results + metadata

Get roles assigned to a user.

Arguments: user_name - user name to analyze

Returns: ResponseType: formatted response with query results + metadata

Get Teradata database version information.

Get the Teradata flow control metrics for a specified date range.

Get the user feature usage metrics for a specified date range.

Get the Teradata user delay metrics for a specified date range.

Get the Teradata session information for user.

Get the Teradata total system database space usage.