17.00 - Overview - Teradata Database

Teradata Vantage™ - Data Dictionary

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Administration
Programming Reference
featnum
B035-1092-170K

The following topics describe all the Data Dictionary views in alphabetical order. For views with a VX view, the columns shown in the tables are from the VX view and the title of the section is <viewname>V[X]; otherwise, the title indicates the exact view from which the columns are listed, such as AccessLogV.

You can use Teradata Studio or Teradata Studio Express to view the Data Dictionary.

To see the source table for a column, query the definition for the view with the following SQL statement: show view <viewname>;. For example:
show view tablesvx;

DBC.UserDB and DBC.OwnerDB system views are not covered in this document because they are not usually referenced directly by users. These views are used only to join other system tables and views (especially the X version of views).

Data Dictionary views fall into the following categories:

Type Information Stored
Operations Internal database operations
Database Database instance and owner
Schema Database schema (tables, columns, and so on)
Integrity Data integrity (constraints and so on)
Security Data security (roles, grants, access, and so on)
Query Database Query Log
Accounting Database usage (accounts, space, and so on)
Optimizer Statistics Statistics collected on indexed, non-indexed columns (including row and column PARTITION statistics) and expressions on permanent tables, base and materialized global temporary tables (for the current session) and join and hash indexes.
TDMaps Stores metadata that is used by the system when moving tables to new maps.
The results shown in the examples in the following sections are for illustration purposes only. You can use utilities and tools, such as BTEQ or other third-party products, to enter queries and format the results differently.

TDMaps Views

The TDMaps database, and its tables and views, stores metadata that is used by the system when moving tables to new maps. TDMaps also contains SQL procedures that perform tasks related to system expansion.

The procedures and tables defined within TDMaps fall into two general categories: Advisor and Mover. Advisor procedures are responsible for analyzing user tables within logged query plans to make recommendations for moving a set of tables onto new maps. The recommendations can optionally be customized by callers and then input to the Mover. Mover procedures are responsible for moving the data for a specified list of tables onto new maps.

Users can issue SELECT statements to view the data in the views. For example, select all the rows from the action history view:

SELECT * FROM TDMaps.ActionHistoryVX;
The user must have been granted SELECT on TDMaps to execute this statement; for example, GRANT SELECT ON TDMaps to <user>;
TDMaps provides the following views used by Advisor procedures:
  • ActionsV[X] – list of planned map actions for a table or database. The advisor populates the ActionsTbl and the mover reads the actions from the table and schedules them for execution. Available actions are: move a table to a new map or do not perform any action on the table for the specified destination map. This is a is a secure zone constrained view. ActionsVX returns only objects to which the user has access.
  • ExclusionListsV[X] – list of databases, tables, join indexes, and hash indexes that are not to be moved. The CreateExclusionList table is created by calling CreateExclusionListSP. This is a secure zone constrained view. ExclusionListsVX returns only the objects to which the user has access.
  • MapListsV[X] – list of maps and the maps included within each list. This is a secure zone constrained view. MapListsVX returns only the maps to which the user has access.
  • ObjectListsV[X] – list of databases, tables, join indexes, and hash indexes. This is a secure zone constrained view. ObjectListsVX returns only the objects to which the user has access.
  • SettingsV – stores settings that are used by the stored procedures and views. Please contact Teradata Support for assistance before making changes to SettingsTbl.
  • TableToSparseMapSizingV[X] – identifies which map the Advisor thinks a table should be in. This view can be joined with the ObjectUseCountV view to get object usage information: the DatabaseName and TableName columns in TableToSparseMapSizingV can be joined with the DatabaseName and ObjectName columns in ObjectUseCountV. This is a is a secure zone constrained view. TableToSparseMapSizingVX returns only objects to which the user has access.
TDMaps provides the following view used by Mover procedures:
  • ActionHistoryV[X] – lists map related actions that are running or have completed. The ActionHistoryTbl is updated by the mover procedure. After the mover takes an action from the ActionsTbl, it inserts a row into ActionHistoryTbl and sets its status to In Progress. After the action completes, the mover updates the status to Complete. This is a secure zone constrained view. ActionHistoryVX returns only the objects the user has access to.