Housekeeping on an Ad Hoc Basis | VantageCloud Lake - Housekeeping on an Ad Hoc Basis - Teradata Vantage

DBC.AccessRights

When a user or database is created, there is a default number of privileges added to DBC.AccessRights. Review these entries and delete obsolete or redundant entries. Otherwise, this table can grow large and negatively impact space limits, and may 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:

• Determine which users have the most entries. The following query checks for the userid/database ID that has the most rows that hash to the same value:

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

• REVOKE rights at the table level and GRANT rights at the database level to reduce the number of entries.
• Convert individual privileges to role privileges where possible.

DBC.AccessRights and DBC.TSETQueryText

If you receive a 3610 error, the query text may be saved to the DBC.TSETQueryText table for use by Teradata Support. 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.

Archive and Recovery Tables

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

Accumulated Values in Data Dictionary Tables

Resetting values can help you do better resource usage analysis, and keep cleaner historical records. Reset the following fields when necessary:

EXEC DBC.ClearPeakDisk;

UPDATE DBC.AMPUsageV
SET CPUTime = 0,
DiskIO = 0
ALL;