dba_tableUsageImpact

Instructions

Implementation Reference

• src/teradata_mcp_server/tools/dba/dba_tools.py:347-425 (handler)

  Handler function for the dba_tableUsageImpact tool. Executes SQL query on DBQL tables to compute table usage statistics including query counts, first/last query timestamps, and usage frequency categories (High/Medium/Low). Supports optional filtering by database_name or user_name.

  def handle_dba_tableUsageImpact(conn: TeradataConnection, database_name: str | None = None, user_name: str | None = None, *args, **kwargs):
      """
      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
  
      """
      logger.debug(f"Tool: handle_dba_tableUsageImpact: Args: database_name: {database_name}, user_name: {user_name}")
      database_name_filter = f"AND objectdatabasename = '{database_name}'" if database_name else ""
      user_name_filter = f"AND username = '{user_name}'" if user_name else ""
      table_usage_sql="""
      LOCKING ROW for ACCESS
      sel
      DatabaseName
      ,TableName
      ,UserName
      ,Weight as "QueryCount"
      ,100*"Weight" / sum("Weight") over(partition by 1) PercentTotal
      ,case
          when PercentTotal >=10 then 'High'
          when PercentTotal >=5 then 'Medium'
          else 'Low'
      end (char(6)) usage_freq
      ,FirstQueryDaysAgo
      ,LastQueryDaysAgo
  
      from
      (
          SELECT   TRIM(QTU1.TableName)  AS "TableName"
                  , TRIM(QTU1.DatabaseName)  AS "DatabaseName"
                  ,UserName as "UserName"
                  ,max((current_timestamp - CollectTimeStamp) day(4)) as "FirstQueryDaysAgo"
                  ,min((current_timestamp - CollectTimeStamp) day(4)) as "LastQueryDaysAgo"
                  , COUNT(DISTINCT QTU1.QueryID) as "Weight"
          FROM    (
                      SELECT   objectdatabasename AS DatabaseName
                          , ObjectTableName AS TableName
                          , ob.QueryId
                      FROM DBC.DBQLObjTbl ob /* uncomment for DBC */
                      WHERE Objecttype in ('Tab', 'Viw')
                      {database_name_filter}
                      AND ObjectTableName IS NOT NULL
                      AND ObjectColumnName IS NULL
                      -- AND LogDate BETWEEN '2017-01-01' AND '2017-08-01' /* uncomment for PDCR */
                      --	AND LogDate BETWEEN current_date - 90 AND current_date - 1 /* uncomment for PDCR */
                      GROUP BY 1,2,3
                          ) AS QTU1
          INNER JOIN DBC.DBQLogTbl QU /* uncomment for DBC */
          ON QTU1.QueryID=QU.QueryID
          AND (QU.AMPCPUTime + QU.ParserCPUTime) > 0
          {user_name_filter}
  
          GROUP BY 1,2, 3
      ) a
      order by PercentTotal desc
      qualify PercentTotal>0
      ;
  
      """
      logger.debug(f"Tool: handle_dba_tableUsageImpact: table_usage_sql: {table_usage_sql}")
      with conn.cursor() as cur:
          logger.debug("Database version information requested.")
          rows = cur.execute(table_usage_sql.format(database_name_filter=database_name_filter, user_name_filter=user_name_filter))
          data = rows_to_json(cur.description, rows.fetchall())
      if len(data):
          info=f'This data contains the list of tables most frequently queried objects in database schema {database_name}'
      else:
          info=f'No tables have recently been queried in the database schema {database_name}.'
      metadata = {
          "tool_name": "handle_dba_tableUsageImpact",
          "database": database_name,
          "table_count": len(data),
          "comment": info,
          "rows": len(data)
      }
      logger.debug(f"Tool: handle_dba_tableUsageImpact: metadata: {metadata}")
      return create_response(data, metadata)