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:
- Select statistics from the AMPUsage view and insert them in the history table.
- 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:
- Select statistics from the DiskSpaceV[X] view and insert them in the history table.
- 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.