17.00 - Usage Notes - 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 source of many of the columns in TablesV[X] is the DBC.TVM table. DBC.TVM contains one row for each table, view, stored procedure, join index, macro, UDT, UDM, or UDF.

For information about the possible values for JournalFlag or TableKind columns, see "JournalFlag Column" and "TableKind Column."

Some of the column values show information related to a Teradata temporal table or an ANSI temporal table. For more information about these tables, see Teradata Vantage™ ANSI Temporal Table Support , B035-1186 and Teradata Vantage™ Temporal Table Support , B035-1182 .

Corresponding Tables

The X view references these additional tables:
  • DBC.AccessRights
  • DBC.Owners
  • DBC.RoleGrants
  • DBC.Roles

BlockSize

BlockSize is specified in the DATABLOCKSIZE clause of the CREATE TABLE or ALTER TABLE statements. The value is in bytes and is rounded upward to the nearest multiple of the sector size in bytes. That rounded value is not rounded up to a 4KB page boundary. NULL is inserted when a DATABLOCKSIZE clause is not specified for the table, or does not apply to the object (for example, a view).

ColocationName

ColocationName is the colocation name for an object with a sparse map. It is null for an object with a contiguous map and for a non-mapped object.

Objects with the same sparse map reside on the same AMPs if those objects have the same colocation names.

CurrentLoadId

A positive, even integer that indicates the last committed load value for a load isolated table or join index. Its initial value is 0 and it is incremented by 1when a load is committed. This column is NULL for objects that are not load isolated.

FreeSpacePercent

FreeSpacePercent is specified in the FREESPACE clause of the CREATE TABLE or ALTER TABLE statements. The value is in the range 0 to 75, inclusive.

NULL is inserted when a FREESPACE clause is not specified for the table, or does not apply to the object (for example, a view).

MapName

MapName is the name of the map in which the corresponding table resides. MapNames start with TD_, such as TD_Map1, for contiguous maps. The MapName for sparse maps created by CREATE MAP is not allowed to start with TD_.

MergeBlockRatio

MergeBlockRatio is specified in the MERGEBLOCKRATIO clause of the CREATE TABLE or ALTER TABLE statements. The value is in the range 0 to 100, inclusive. NULL is inserted when a MERGEBLOCKRATIO clause is not specified for the table, or does not apply to the object (for example, a view).

RequestText

The RequestText data reflects the definitions specified by the user. This may not always match the data returned by the SHOW TABLE statement, which reflects the reconstructed definitions as they exist in the Data Dictionary.

For example, when obsolete syntax that is still supported is converted internally to current syntax, RequestText returns the submitted (obsolete) syntax, while SHOW TABLE returns the converted (current) syntax.

If the table is renamed, the RequestText still contains the old table name. The new table name is not put in the RequestText.

Possible Values for BlockCompression

BlockCompression is specified in the BLOCKCOMPRESSION clause of the CREATE TABLE, CREATE JOIN INDEX, CREATE HASH INDEX, or ALTER TABLE statements. Valid values are:

Value Description
0 DEFAULT
1 MANUAL
2 AUTOTEMP
3 NEVER
4 ALWAYS
NULL BlockCompression is NULL if not specified or if it does not apply to the object.

Possible Values for BlockCompressionAlgorithn

BlockCompressionAlgorithm is specified in the BLOCKCOMPRESSIONALGORITHM clause of the CREATE TABLE, CREATE JOIN INDEX, CREATE HASH INDEX, or ALTER TABLE statements. Valid values are:

Value Description
0 DEFAULT
1 ZLIB
2 ELZS_H
NULL BlockCompressionAlgorithm is NULL if not specified or if it does not apply to the object.

Possible Values for BlockCompressionLevel

BlockCompressionLevel is specified in the BLOCKCOMPRESSIONLEVEL clause of the CREATE TABLE, CREATE JOIN INDEX, CREATE HASH INDEX, or ALTER TABLE statements. The integer values stored are: 0 (DEFAULT), or 1 through 9.

A value of 0 (DEFAULT) indicates that the attribute of the table is the current system default setting in DBS Control. If the DBS Control setting changes this column is not updated.

An integer value of 4 is stored corresponding to the string token ‘ALWAYS’ specified in the BLOCKCOMPRESSION clause of the CREATE TABLE, CREATE JOIN INDEX, CREATE HASH INDEX, or ALTER TABLE statements.

BlockCompressionLevel is NULL when a BLOCKCOMPRESSIONLEVEL clause is not specified for the table, join index, hash index, or does not apply to the object (for example, a view).

Possible Values for CheckSum

CheckSum is specified in the CHECKSUM clause of the CREATE TABLE, CREATE JOIN INDEX, CREATE HASH INDEX, or ALTER TABLE statements. The values stored are:

Value Description
N OFF
Y ON
NULL NULL is inserted when a CHECKSUM clause is not specified for the table, join index, or hash index, or does not apply to the object (for example, a view).

Note, the old keywords indicating Checksums enabled (VERSION, LOW, MEDIUM, HIGH, and ALL) are mapped internally to the keyword ON, and the old keyword indicating Checksums disabled (NONE) is mapped internally to the keyword OFF. The old external keywords are no longer visible.

Possible Values for LoadIdLayout

Value Description
NULL Tables that are not load isolated.
R Load isolated table with row partitioning or with no partitioning.
C Load isolated table with column partitioning.

Possible Values for LoadProperty

Value Description
NULL Tables or join indexes that are not load isolated.
I Concurrent load isolation for INSERTs only on a load isolated table.
A Concurrent load isolation for all Data Manipulation Language statements on a load isolated table.
D Temporarily disabled the concurrent load isolation on a load isolated table.
S A join index defined on a load isolated table.

Possible Values for PrimaryKeyIndexId

Value Description
1 Primary key is implemented using a unique primary index for the table.
Multiple of 4 (that is, a number between 4 and 128) Primary key is implemented using a unique secondary index for the table. The number is the index ID for the unique secondary index.
NULL Primary key is not defined for the table.

Possible Values for PIColumnCount

PIColumnCount is set to the number of columns in the primary index or primary AMP index. 0 indicates there is neither a primary index nor a primary AMP index.

For a NoPI table, TableKind is 'O' if the table is not partitioned and is 'T' if the table is partitioned. For a join index, TableKind is 'I'.

To determine if a table or join index (with a nonzero PIColumnCount) has a primary index or a primary AMP index, the DBC.IndicesV[X] system views can be queried. If PIColumnCount is zero, the table or join index has neither a primary index nor a primary AMP index (that is, it is NoPI) and there are no rows in the DBC.IndicesV[X] views with an IndexNumber of 1. Note that a hash index always has a primary index.

Possible Values for PartitioningLevels

Value Description
Between 1 and 62 (inclusive) The number of partitioning levels for the table or join index.
0 The table or join index is not partitioned.

Possible Values for RowSizeFormat

RowSizeFormat describes the size format used to the store base table, join index, or hash index rows for this object at the time the last DDL statement was completed for this object. Valid values are:

Value Description
0 A row size format of up to 64KB
1 A row size format of up to 1MB
NULL NULL is inserted when object the does not have any base table rows (for example, a view).

Possible Values for ResolvedCurrent_TimeStamp and ResolvedCurrent_Date

  • This is the last reconciled timestamp or date if the object is a join index or a table that is defined using:
    • CURRENT_TIMESTAMP
    • CURRENT_DATE or DATE

      Either the partition, JI definition, or temporal table has a system-defined join index.

  • NULL in all other cases.

Possible Values for SystemDefinedJI

Value Description
Y The entry is corresponding to a system-defined join index.
NULL Any other objects in the system.

Possible Values for TableHeaderFormat

TableHeaderFormat describes the format used to process and store the table header for this object at the time the last DDL statement was completed for this object. Note that this field is not updated when the table header format changes during non-DDL operations (for example, Down AMP regions). Valid values are:

Value Description
0 Indicates a thin format of up to 64KB in-memory and as a single row on disk.
1 Indicates a fat format of up to 1MB in-memory and up to 16 64KB rows on disk.
NULL NULL is inserted when the object the does not have a table header (for example, a view).

Possible Values for TemporalProperty

Value Description
S A system-versioned system-time table.
U A bitemporal (system-versioned system-time and valid-time) table.
W A nontemporal table that contains a system-time derived period column but that is not system versioned.
X A valid-time temporal table that contains a system-time derived period column but that is not system versioned.

Possible Values for TVMFlavor

TVMFlavor is used in conjunction with TableKind of O or T to identify the unique table kind. Possible values are:

Value Description
D Direct Access foreign table.
M Materialized foreign table.
S Time Series table.

Possible Values for Version and UtilVersion

The UtilVersion column may be zero or null for tables created prior to V2R6.0. For tables created in V2R6.0 or later, both Version and UtilVersion are initially one.

The following table shows when the UtilVersion column is modified, and how it is modified:

UtilVersion Current Value Data Definition Change New Value or UtilVersion
Any value Significant change (by an ALTER TABLE statement) to keep an archive from being restored or copied. = Version
NULL or zero Not significant Version -1
Any other value Not significant No change

The Version column is increased by one for any data definition change other than enabling or disabling journaling for the table. Version in the above table is the version after it has been incremented.

The following table shows how the UtilVersion column is checked when selected partitions are restored or copied:

UtilVersion Value Requirements
NULL or zero The Version of the archive must match the Version of the table
Any other value If the archive UtilVersion is zero, the Version of the Archive must match the UtilVersion of the table; otherwise, the UtilVersion column of the archive must equal the UtilVersion of the table
To restore or copy selected partitions, the table must already exist.