Revision history [back]

There are a few different methods to determine the count of delay, active, and idle sessions in Teradata for the previous week, but one possible approach is as follows:

Connect to the Teradata system using a tool such as Teradata SQL Assistant or Teradata Studio.
Execute a query against the DBC.SessionInfo table, which contains information about all active sessions on the system. You can filter the results to only include sessions from the previous week using a WHERE clause that compares the StartTime column to a specific date range. For example:

SELECT * FROM DBC.SessionInfo WHERE StartTime >= DATE '2022-01-09' AND StartTime < DATE '2022-01-16';

This query will return all sessions that started between January 9th and January 15th, which represents the previous week.

To determine the number of active sessions, you can simply count the number of rows returned by the query:

SELECT COUNT(*) FROM DBC.SessionInfo WHERE StartTime >= DATE '2022-01-09' AND StartTime < DATE '2022-01-16';

This will give you the total count of active sessions during the previous week.

To determine the number of idle sessions, you can look for sessions that have been inactive for a certain amount of time using the LastResponseTimeStamp column in the DBC.SessionInfo table. For example, you could define an idle session as one that has been inactive for more than 1 hour:

SELECT COUNT(*) FROM DBC.SessionInfo WHERE StartTime >= DATE '2022-01-09' AND StartTime < DATE '2022-01-16' AND (CURRENT_TIMESTAMP - LastResponseTimeStamp) HOUR > 1;

This will give you the total count of idle sessions during the previous week.

To determine the number of delayed sessions, you could define a delayed session as one that has been running for more than a certain amount of time. For example, you could consider any session that has been running for more than 1 hour as delayed:

SELECT COUNT(*) FROM DBC.SessionInfo WHERE StartTime >= DATE '2022-01-09' AND StartTime < DATE '2022-01-16' AND (CURRENT_TIMESTAMP - StartTime) HOUR > 1;

This will give you the total count of delayed sessions during the previous week.