Find queries executed in specific time range in Teradata database

Teradata enables you to check last query executed in specific time range

Notes

To log query information you need to create rules for logs. To do this use BEGIN QUERY LOGGING statement. You can find out how to use it in teradata documentation. Example statement to log all queries:

BEGIN QUERY LOGGING ON ALL;

Query

Query below lists queries executed between six and three days from current time.

SELECT  ProcID,
        UserName,
        QueryText,
        LockLevel,
        StartTime,
        FirstRespTime,
        ClientID,
        ClientAddr,
        ErrorCode,
        ErrorText,
        TotalIOCount,
        NumResultRows
FROM    DBC.QryLogV
WHERE   StartTime > CURRENT_TIMESTAMP - INTERVAL '6' DAY
AND StartTime < CURRENT_TIMESTAMP - INTERVAL '3' DAY
ORDER BY    StartTime DESC;

Columns

  • ProcID - process ID of the dispatcher
  • UserName - name of the user who executed the query.
  • QueryText - query text. The default size is 200 characters.
  • LockLevel - highest level lock associated with this request
  • StartTime - time the query is submitted
  • FirstRespTime - first response time
  • ClientID - id of client
  • ClientAddr - client address
  • ErrorCode - error code if query fail
  • ErrorText - error text if query fail
  • TotalIOCount - number of IOs from AMPs that were generated by the query
  • NumResultRows - total number of rows returned for the query.

Rows

  • One row represents one query executed in specifiic time range
  • Scope of rows: all queries executed in specifiic time range
  • Ordered by start time of query

Sample results

sample results