dba_databaseVersion:
type: tool
description: " Get Teradata database version information."
sql: |
select InfoKey, InfoData FROM DBC.DBCInfoV;
dba_flowControl:
type: tool
description: "Get the Teradata flow control metrics for a specified date range."
parameters:
start_date:
description: "The start date for the query range in YYYY-MM-DD format."
end_date:
description: "The end date for the query range in YYYY-MM-DD format."
sql: |
SELECT A.THEDATE AS "Date"
, A.THETIME (FORMAT '99:99:99') AS "Time"
, CASE
WHEN DAY_OF_WEEK = 1 THEN 'Sun'
WHEN DAY_OF_WEEK = 2 THEN 'Mon'
WHEN DAY_OF_WEEK = 3 THEN 'Tue'
WHEN DAY_OF_WEEK = 4 THEN 'Wed'
WHEN DAY_OF_WEEK = 5 THEN 'Thr'
WHEN DAY_OF_WEEK = 6 THEN 'Fri'
WHEN DAY_OF_WEEK = 7 THEN 'Sat'
END AS DAY_OF_WEEK
, A.FLOWCTLTIME AS "Flow Control Time"
, (A.FLOWCTLTIME / 1000) / A.SECS AS "FlowControl%"
, C.CPUUEXEC + C.CPUUSERV AS "CPUBusy"
, CPUIOWAIT AS "CPUWaitForIO"
, ((C.CPUUEXEC) / (C.CENTISECS * C.NCPUS)) * 100 AS "CPUEXEC%"
, ((C.CPUUSERV) / (C.CENTISECS * C.NCPUS)) * 100 AS "CPUSERV%"
, ((C.CPUIOWAIT) / (C.CENTISECS * C.NCPUS)) * 100 AS "WAITIO%"
, ((C.CPUIDLE) / (C.CENTISECS * C.NCPUS)) * 100 AS "IDLE%"
FROM DBC.RESUSAGESAWT A
INNER JOIN DBC.RESUSAGESVPR B
ON A.VPRID = B.VPRID
AND A.THETIME = B.THETIME
INNER JOIN DBC.RESUSAGESPMA C
ON A.NODEID = C.NODEID
AND A.THETIME = C.THETIME
AND A.THEDATE = C.THEDATE
INNER JOIN SYS_CALENDAR.CALENDAR D
ON C.THEDATE = D.CALENDAR_DATE
WHERE A.THEDATE BETWEEN :start_date AND :end_date
GROUP BY 1,2,3,4,5,6,7,8,9,10,11;
dba_featureUsage:
type: tool
description: "Get the user feature usage metrics for a specified date range."
parameters:
start_date:
description: "The start date for the query range in YYYY-MM-DD format."
end_date:
description: "The end date for the query range in YYYY-MM-DD format."
sql: |
SELECT
CAST(A.Starttime as Date) AS LogDate
,A.USERNAME as Username
,CAST(B.FEATURENAME AS VARCHAR(100)) AS FEATURENAME
,SUM(GETBIT(A.FEATUREUSAGE,(2047 - B.FEATUREBITPOS))) AS FeatureUseCount
,COUNT(*) AS RequestCount
,SUM(AMPCPUTIME) AS AMPCPUTIME
FROM DBC.DBQLOGTBL A,
DBC.QRYLOGFEATURELISTV B
WHERE CAST(A.Starttime as Date) BETWEEN :start_date AND :end_date
GROUP BY
LogDate,
USERNAME,
FeatureName having FeatureUseCount > 0
ORDER BY 1,2,3;
dba_userDelay:
type: tool
description: "Get the Teradata user delay metrics for a specified date range."
parameters:
start_date:
description: "The start date for the query range in YYYY-MM-DD format."
end_date:
description: "The end date for the query range in YYYY-MM-DD format."
sql: |
Select
CAST(a.Starttime as DATE) AS "Log Date"
,extract(hour from a.starttime) as "Log Hour"
,Username
,WDName
,Starttime
,a.firststeptime
,a.FirstRespTime
,Zeroifnull(DelayTime) as DelayTime
, (CAST(extract(hour
From ((a.firststeptime - a.StartTime) HOUR(2) TO SECOND(6) ) ) * 3600 + extract(minute
From ((a.firststeptime - a.StartTime) HOUR(2) TO SECOND(6) ) ) * 60 + extract(second
From ((a.firststeptime - a.StartTime) HOUR(2) TO SECOND(6) ) ) AS dec(8,2))) - zeroifnull(cast(delaytime as float)) (float) as PrsDctnryTime
, Zeroifnull(CAST(extract(hour
From ((a.firstresptime - a.firststepTime) HOUR(2) TO SECOND(6) ) ) * 3600 + extract(minute
From ((a.firstresptime - a.firststepTime) HOUR(2) TO SECOND(6) ) ) * 60 + extract(second
From ((a.firstresptime - a.firststepTime) HOUR(2) TO SECOND(6) ) ) AS INTEGER) ) as QryRespTime
, Zeroifnull(CAST(extract(hour
From ((a.firstresptime - a.StartTime) HOUR(2) TO SECOND(6) ) ) * 3600 + extract(minute
From ((a.firstresptime - a.StartTime) HOUR(2) TO SECOND(6) ) ) * 60 + extract(second
From ((a.firstresptime - a.StartTime) HOUR(2) TO SECOND(6) ) ) AS INTEGER) ) as TotalTime
,count(*) As NoOfQueries
from DBC.DBQLogTbl a
Where DelayTime > 0
AND CAST(a.Starttime as DATE) between :start_date AND :end_date
Group By 1,2,3,4,5,6,7,8,9,10,11;
dba_sessionInfo:
type: tool
description: "Get the Teradata session information for user."
sql: |
SELECT
UserName,
AccountName,
SessionNo,
DefaultDataBase,
LogonDate,
LogonTime,
LogonSource,
LogonAcct,
CurrentRole,
QueryBand,
ClientIpAddress,
ClientProgramName,
ClientSystemUserId,
ClientInterfaceVersion
FROM DBC.SessionInfoV
WHERE UserName = :user_name (NOT CASESPECIFIC) or :user_name='*';
parameters:
user_name:
description: "User name to analyze. User '*' to get all users."
type_hint: str
default: '*'
dba_systemSpace:
type: tool
description: "Get the Teradata total system database space usage."
sql: |
Select
SUM(CurrentPerm)/1024/1024/1024 AS SpaceUsed_GB
,SUM(MaxPerm)/1024/1024/1024 AS SpaceAllocated_GB
,SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used
,SpaceAllocated_GB- SpaceUsed_GB AS FreeSpace_GB
FROM DBC.DiskSpace;
dba_tableArchive:
type: prompt
description: "The following prompt is used to guide the Teradata DBA in finding opportunities for archiving data. (prompt_type: reporting)"
prompt: |
You are a Teradata DBA who is an expert in finding opportunities for archiving data.
## Phase 1 - Get the 10 largest user tables
Use dba_tableSpace tool with top_n=10 and exclude_system=true to get the 10 largest user tables.
Save the list of {DatabaseName, TableName} pairs for Phase 2.
## Phase 2 - Get DDL for each table
Cycle through the tables from Phase 1 (largest first).
For each table, use base_tableDDL tool to get its DDL definition.
## Phase 3 - Present archive SQL script
Using ALL DDL definitions collected in Phase 2, generate a single SQL archive script.
For each table:
1. Identify date or timestamp columns suitable for partitioning old vs new data
2. Write a Teradata SQL INSERT SELECT statement that archives rows older than 90 days into a table named hist_{original_table_name} in the same database
3. If the table has no date or timestamp column, skip it and add a comment explaining why
Present the complete SQL script as the final output.
dba_databaseLineage :
type: prompt
description: "You are a Teradata DBA who is an expert in finding the lineage of tables in a database. (prompt_type: context)"
prompt: |
You are a Teradata DBA who is an expert in finding the lineage of tables in a database.
You will be assessing the {database_name} database and all the tables in it.
## Phase 1 - Get a list of tables in the database
Use base_tableList tool with database_name={database_name} to get all tables.
Exclude any table named "All".
Save the list of table names for Phase 2.
## Phase 2 - Collect SQL history for each table
Cycle through the tables from Phase 1.
For each table, use dba_tableSqlList tool with no_days={number_days} to get recent SQL statements.
## Phase 3 - Present lineage analysis
Using ALL SQL history collected in Phase 2, analyze the data flow between tables:
1. For each SQL statement, identify source and target tables from INSERT/SELECT, CREATE TABLE AS, MERGE, and JOIN patterns
2. Create a distinct list of (source_database.source_table, target_database.target_table) tuples, removing duplicates
3. Present the lineage as the final output
parameters:
database_name:
name: database_name
description: "Name of the database to analyze for table lineage."
required: true
type_hint: str
number_days:
name: number_days
description: "Number of days to look back for SQL execution history (e.g., 7, 30, 90)."
required: true
type_hint: str
dba_tableDropImpact:
type: prompt
description: "You are a Teradata DBA who is an expert in finding the impact of dropping a table. (prompt_type: reporting)"
prompt: |
You will be assessing the {table_name} table in {database_name} database and all the SQL that has executed against it.
## Phase 1 - Get usage data
Get a list of sql that has executed against the table in the last {number_days} days using the dba_tableSqlList tool
Save this list for use in Phase 2 - you will need to reference each SQL statement in it.
## Phase 2 - Analyze Usage data
Using the SQL list collected in Phase 1:
1. Create two dictionaries:
- user_counts: to track distinct usernames and their query counts
- table_deps: to track other user tables referenced alongside {table_name} and their counts
2. For each SQL statement in the list:
- Extract and count the username who executed it (add to user_counts)
- Parse the SQL text for references to other user tables (add to table_deps).
Only count tables that belong to user databases — do NOT count any of the following:
- The target table itself ({database_name}.{table_name} in any case variation)
- Any DBC system object (DBC.QryLogV, DBC.QryLogSqlV, DBC.AllSpaceV, DBC.Tables, DBC.DBQLObjTbl, etc.)
- Teradata built-in analytic functions (TD_UnivariateStatistics, TD_ColumnSummary, TD_getRowsWithMissingValues, etc.)
If a table name does not have a database prefix, assume it belongs to {database_name}.
3. Merge case-insensitive duplicates (e.g. "Products" and "products" are the same table)
4. Keep these counts for use in Phase 3
## Phase 3 - Return results
Return the combined list of users and dependent tables.
## Communication guidelines:
- Be concise but informative in your explanations
- Clearly indicate which phase the process is currently in
- summarize the outcome of the phase before moving to the next phase
## Final output guidelines:
- Return a markdown table with the following columns:
| Type | Name | Usage Count |
|------|------|-------------|
| User | username1 | count |
| Table | tablename1 | count |
- Sort the results by Usage Count in descending order
- Include both users and dependent tables, with Type column indicating which is which
- The table must NOT contain DBC system objects or Teradata built-in functions
- Do not include any additional explanation of results
parameters:
table_name:
name: table_name
description: "Name of the table to analyze for drop impact."
required: true
type_hint: str
database_name:
name: database_name
description: "Name of the database containing the table."
required: true
type_hint: str
number_days:
name: number_days
description: "Number of days to look back for SQL execution history (e.g., 7, 30, 90)."
required: true
type_hint: str
dba_databaseHealthAssessment :
type: prompt
description: "You are a Teradata DBA who is an expert in assessing the health of a database. (prompt_type: reporting)"
prompt: |
Generate a comprehensive Teradata system health dashboard for the last 30 days, structured as an executive summary followed by detailed technical analysis.
## Phase 1 - Get list of databases and space metrics
Use base_databaseList tool with scope='{database_scope}' to get a list of databases.
Create a list of database names for the phases below.
Also use dba_databaseSpace tool to get space usage metrics across all databases.
Also use dba_tableSpace tool with top_n=10 and exclude_system=true to get the top 10 space-consuming tables.
## Phase 2 - Resource usage analysis for the last 30 days
Use dba_resusageSummary tool with no_days=30 to gather CPU, IO, and Memory resource usage patterns.
The tool uses no_days to calculate the date range internally - do NOT pass a date parameter.
Call the tool twice:
1. First call with dimensions=["LogDate"] for the daily summary trend
2. Second call with dimensions=["LogDate","hourOfDay"] for the 30-day heatmap (date x hour matrix)
## Phase 3 - Flow control analysis for the last 30 days
IMPORTANT: This is a SINGLE tool call, not a date iteration. Do NOT generate a date list or iterate per day.
Use dba_flowControl tool ONCE with start_date = 30 days before today and end_date = today, both in YYYY-MM-DD format.
Calculate these dates yourself and pass them directly as arguments.
## Phase 4 - User delay analysis for the last 30 days
IMPORTANT: This is a SINGLE tool call, not a date iteration. Do NOT generate a date list or iterate per day.
Use dba_userDelay tool ONCE with the same date range as Phase 3: start_date = 30 days before today, end_date = today, both in YYYY-MM-DD format.
Calculate these dates yourself and pass them directly as arguments.
## Phase 5 - Database-level detail
Cycle through the list of databases from Phase 1, for each database do the following steps in order:
Step 1. Use base_tableList tool to get a list of tables in the database
Step 2. Use base_tableUsage tool to get table activity and access patterns
## Phase 6 - Present results as a dashboard
Generate a comprehensive dashboard using the data from all phases above.
Executive Summary Section:
* System overview with key performance indicators (number of databases, number of tables, space utilization percentages)
* Critical alerts highlighting databases/tables approaching space limits (use red for >85% utilization, yellow for >70%)
* Top 5 resource consumption trends and usage patterns
Detailed Technical Analysis:
* Complete space utilization breakdown across all databases with visual charts
* Top 10 space-consuming tables with growth trends and utilization percentages
* CPU Resource usage heatmaps showing all dates in the 30-day period by hour of day (rows = dates, columns = hours 0-23)
* IO Resource usage heatmaps showing all dates in the 30-day period by hour of day (rows = dates, columns = hours 0-23)
* Flow control metrics and user delay analysis with performance bottleneck identification
* Database and table activity rankings showing most frequently accessed objects
Formatting Requirements:
* Use color coding: Red (critical/>85%), Yellow (warning/70-85%), Green (healthy/<70%)
* Include bar charts for space utilization and usage patterns
* Present data in sortable tables with key metrics highlighted
* Add trend indicators (arrows/percentages) for changing metrics
* Target audience: DBA management and Teradata system owners
* Focus on informational assessment rather than actionable recommendation
* Ensure that dashboard is mobile friendly and scales easily
parameters:
database_scope:
name: database_scope
description: "Scope of databases to include: 'user' for user-created databases only (excludes system databases), 'all' for all databases including system databases."
required: true
type_hint: str
dba_userActivityAnalysis:
type: prompt
description: "Analyze Teradata user activity patterns for the past 7 days (prompt_type: reporting)"
prompt: |
You are a Teradata DBA analyzing user activity patterns for the past 7 days.
IMPORTANT: Do NOT add phases for TDA_CurrentDate or TDA_DateRange. The tools below handle temporal logic internally via their no_days parameter.
## Phase 1 - Get resource usage data
Use dba_resusageSummary tool with dimensions=["LogDate","hourOfDay","UserName"] and no_days=7.
This returns CPU time, I/O operations, and memory usage broken down by date, hour, and user.
A single call returns all 7 days of data. Do NOT pass a date parameter.
## Phase 2 - Get recent SQL activity
Use dba_userSqlList tool with no arguments.
This tool defaults to 7 days for all users. Do NOT pass user_name.
## Phase 3 - Present user activity analysis
Using ALL data from Phase 1 and Phase 2, generate a comprehensive DBA dashboard report with the following sections:
1. System-wide resource consumption overview with hourly heatmaps for the 7-day period, color-coded by CPU usage levels
2. User ranking table: rank all users by CPU time (primary), I/O operations (secondary), memory usage (tertiary), showing percentage of total system resources. Include user names directly in all tables
3. Activity pattern analysis: each user's peak activity hours and workload distribution
4. Top 5 resource consumers deep dive:
- Individual user profiles with recent SQL activity logs and execution statistics
- Table access frequency analysis showing most queried objects per user
- Query complexity distribution (simple/medium/complex based on execution characteristics) and execution time patterns
- Hourly activity charts showing when each high-consumption user is most active
5. Output format: dashboard-style presentation with sortable tables, color-coded metrics to highlight resource usage levels, include specific query examples and table access patterns
Present the complete analysis as the final output
dba_systemVoice:
type: prompt
description: "Analyze Teradata user activity patterns for the past 7 days (prompt_type: reporting)"
prompt: |
You impersonate the Teradata system, when asked questions about "your" health, resource usage, users, flow control, etc... use the appropriate Teradata tools to get relevant system information.
Unless pecified, consider that the user is asking about today's metrics.
When asked about "your" health, use tools like the dba_resusageSummary (just use today's date as date parameter), dba_flowControl, dba_systemSpace,
When asked how "busy" you are use tools like the dba_currentUserActivity, dba_resusageSummary
When asked about "your" users, use tools like the dba_sessionInfo tool to get detailed session information for a specific user.
When asked about "your" tables or databases, use the dba_tableSpace and dba_databaseSpace tools to analyze space usage and growth patterns.
When asked about projections, check your current usage (perm space, cpu, queries) and past growth pattern. Ask clarifying questions as needed.
In text mode, use emoji as appropriate (eg. traffic lights, moods). Be friendly and professional.
No need to remind the user that you impersonate a Teradata system, they know it.
