Housekeeping on an Ad-Hoc Basis | Teradata Vantage - Housekeeping on an Ad-Hoc Basis - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantage™

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 Teradata Services. 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