Housekeeping on an Ad-Hoc Basis - Teradata Database

You should occasionally clean out rows or reset the fields in the following tables as needed:

When a user or database is created, there is a default number of privileges added to DBC.AccessRights. These entries should be reviewed and obsolete or redundant entries deleted as necessary. Otherwise, this table can grow very large and can greatly impact space limits negatively. This table may also potentially become skewed.

In addition, when an object is dropped, the system may do a full table scan against DBC.AccessRights. If DBC.AccessRights is large, this can adversely affect the performance of the drop operation. The smaller this table can be made, the better.

To reduce the size of the DBC.AccessRights table:

.set retlimit 10

.set ret cancel

LOCKING ROW FOR ACCESS	

SELECT COUNT(*) (TITLE '# of//Entries', FORMAT 'ZZZZZZ9'),

 a.userid|| a.databaseid (TITLE 'UserID|DatabaseID'),

 dbase.databasenamei (TITLE 'UserName')

FROM accessrights a , dbase

WHERE a.userid = dbase.databaseid

GROUP BY 2,3 ORDER BY 1 DESC,2 ;

If you receive a 3610 error, the query text may be saved to the DBC.TSETQueryText table for use by the Teradata Customer Support Center. After the problem is resolved, you may need to manually delete the rows where the “Handled” column is set to 1 or has a timestamp that indicates the information is no longer needed.

When associated removable media is expired and over-written, clean out the following:

Resetting values can help you do better resource usage analysis, and keep cleaner historical records. The following fields should be reset when necessary:

EXEC DBC.ClearPeakDisk;

UPDATE DBC.AMPUsageV 

SET CPUTime = 0, 

DiskIO = 0

ALL;