Search code examples
sqlteradata

Teradata : Query to find CPU utilization user level (History data)

I have the query to find the current CPU and IO utilization at user level

Query

SELECT ACCOUNTNAME, USERNAME, SUM(CPUTIME) AS CPU, SUM(DISKIO) AS DISKIO FROM DBC.AMPUSAGE GROUP BY 1,2 ORDER BY 3 DESC

But I want to check the history data( date and time) of CPU/IO utilization at user level

AccountName|UserName|CPU|DISKIO|Date/Time

Big Picture

A utility will be created which will fetch data from Teradata and try to generate graphs for the same on daily basis. The report will provide all the utilization details for the whole day which will help us to plot graph. The whole utility will be schedule to run once on daily basis.

Restrictions:

Being developer . we are not allowed to use Teradata Manager

Solution

  • Normally data in AmpUsage will be historized, i.e. a daily job INSERTs the result of your query into a history table and then DELETEs all from AmpUsage.

    Depending on the Account strings used there might be "account string expansion" (ASE) in place, e.g. ..._&D_&H results in one row per AMP per user per hour like ..._131025_09 which can produce hourly usage data.