15.10 - Compiling AMPUsage Statistics - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

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.

    Note: You can reset the maximum and peak DiskSpace counters to zero using the ClearPeakDisk macro, which is provided on the release tape.

    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.
  •  

    For more information on ...

    See ...

    PM/API requests

    Application Programming Reference.

    resource usage

    Resource Usage Macros and Tables.