Compiling AMPUsage Statistics - Advanced SQL Engine - Teradata Database

You can use the AMPUsage view to build and maintain a history table of CPU time and disk I/O statistics for each username/accountname.

To create the history table, enter:

CREATE TABLE AMPUseHist
   ( AccountName VARCHAR(30),
   UserName VARCHAR(30),
   CPUtime INTEGER,
   DiskIO INTEGER,
   Date DATE,Time FLOAT )
   PRIMARY INDEX  (UserName, AccountName);

Periodically, collect usage statistics using this procedure:

1. Select statistics from the AMPUsage view and insert them in the history table.
2. Reset AMPUsage counters to zero for the next collection period.

This procedure can be carried out using the BTEQ script:

.LOGON username, password
    
INSERT INTO AMPUseHist
SELECT AccountName, UserName,SUM(CPUtime), SUM(DiskIO), DATE, TIME
   FROM DBC.AMPUsage
   GROUP BY AccountName, UserName, DATE, TIME;
   UPDATE DBC.AMPUsageV 
   SET CPUTime = 0, DiskIO = 0 ALL;
.QUIT

The units in which Disk I/O are measured represent data block accesses. CPU time is measured in seconds.

Refer to the DiskSpaceV View to determine how you can use the DiskSpaceV view to build and maintain a table of disk space usage.

After a collection period, you may select AMPUsageV and DiskSpaceV statistics from the history tables to query the data or to archive the data on a client system. You can use the selected data in sequential data sets on the host computer for subsequent analysis.

For example, you can use a BTEQ script to:

• Create a client-resident file
• Use the BTEQ .EXPORT command to save the data being selected into that file
• Select all rows from the DiskSpaceV history table

Create the disk space history table.

CREATE TABLE DiskSpaceHist (DataBaseName VARCHAR(128) CHARACTER SET UNICODE,
   AccountName VARCHAR(128) CHARACTER SET UNICODE,
   MaxPerm FLOAT,
   MaxSpool FLOAT,
   CurrentPerm FLOAT,
   PeakPerm FLOAT,
   PeakSpool FLOAT,
   CollectDate DATE,
   CollectTime FLOAT )
PRIMARY INDEX (DataBaseName, AccountName);

Periodically, you can collect usage statistics using the following procedure:

1. Select statistics from the DiskSpaceV[X] view and insert them in the history table.
2. Reset DiskSpace counters to zero for the next collection period.

This procedure can be carried out using the following BTEQ script:

.LOGON username, password
    
INSERT INTO DiskSpaceHist
   SELECT DataBaseName, AccountName,
   SUM(MaxPerm),
   SUM(MaxSpool),
   SUM(CurrentPerm),
   SUM(PeakPerm),
   SUM(PeakSpool),
   DATE, TIME
   FROM DBC.DiskSpaceV
   GROUP BY DataBaseName, AccountName, DATE, TIME;
    
   EXECUTE DBC.ClearPeakDisk;
    
   .QUIT

The following example shows how a BTEQ job is used to select data from the DiskSpace history table.

//JOBNAME JOB   jobcard
//EXTRACT EXEC  PGM=ITBMAIN
//STEPLIB  DD   DSN=TERADATA.APPLOAD,DISP=SHR
//SYSPRINT DD  SYSOUT=*
//SYSABEND DD  SYSOUT=*
//SAVEDATA DD DSN=ACC.SAVEDATA.DATA,DISP=(NEW,CATLG)
//               UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE),
//               DCB=(LRECL=80,RECFM=FB,BLKSIZE=800)
//SYSIN     DD    DATA,DLM=##
.LOGON somebody,password
.EXPORT DATA DDNAME=SAVEDATA
SELECT * FROM DiskSpaceHist
ORDER BY Date,Time;
.QUIT
##

After it is selected and stored, historical data can be used for analysis, as follows:

• Client-resident software packages such as SAS can be used to perform analysis and other statistical manipulation on the data.
• Graphic software packages can be used to display the data.