Housekeeping on an Ad-Hoc Basis - Teradata Database - Teradata Vantage NewSQL Engine

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

• DBC.AccessRights table

  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:

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

  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.

• Archive and recovery tables

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

  Table	Purpose
  DBC.RCConfiguration	archive or restore configuration
  DBC.RCMedia	VolSerial for archive or restore
  DBC.RCEvent	archive or restore events

• Accumulated values in Data Dictionary tables

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

  Fields	Purpose
  PeakPermSpace, PeakSpoolSpace, and PeakTempSpace in the DBC.DataBaseSpace table.	Contains database and table space accounting information. To reset the values, execute the macro: EXEC DBC.ClearPeakDisk; Due to the importance of tracking space usage in your system, you should try to run this macro regularly. Or, you can submit an UPDATE statement yourself.
  CPUTime and DiskIO of the DBC.AMPUsage view (which corresponds to the DBC.Acctg table).	Holds resource usage data by Acct/User. To reset the values, submit the following: UPDATE DBC.AMPUsageV SET CPUTime = 0, DiskIO = 0 ALL;
  AccessCount and LastAccessTimeStamp of the following views: DBC.ColumnsV DBC.DatabasesV DBC.IndicesV DBC.TablesV DBC.UsersV	Tracks the number of times and the last time a particular object was accessed. To reset these fields, use one of these macros: ClearAllDatabaseUseCount ClearDatabaseUseCount ClearTVMUseCount