Usage Notes - Advanced SQL Engine - Teradata Database

Data Dictionary

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-10-15
dita:mapPath
yoz1556235523563.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1092
lifecycle
previous
Product Category
Teradata Vantage™

ColumnsqV[X] and ColumnsjqV[X]

When querying DBC.Columns for a view, information on column attributes (for example, length and type) is NULL. Because column attributes correspond to the table for which they were defined, they are not stored in the Data Dictionary and are not accessible through this view.

Obtain view column information using DBC.Columnsq and DBC.Columnsjq. Access the information in the following views from a SELECT statement, and the result set is joinable to information in other views and tables:
  • ColumnsqV and ColumnsqVX (collectively referred to as ColumnsqV[X])
  • ColumnsjqV and ColumnsjqVX (collectively referred to as ColumnsjqV[X])

ColumnsqV[X] views provide the same column information as ColumnsV[X], including all the information for view columns. ColumnsjqV[X] provides the same information as ColumnsqV[X], but only for tables, NoPI tables, and views.

ANSI Temporal Table Support

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 .

Use the SHOW TABLE statement, HELP COLUMN statement, and the ColumnsV[X] and DBC.PartitioningConstraintsV[X] views to obtain partitioning information for a table or join index.

The HELP INDEX statement Ordered or Partitioned attribute shows if an index is partitioned. For a NoPI table, the HELP INDEX statement cannot be used to determine whether or not the table or join index is partitioned.

ArrayColElementUdtName

If the object is an ARRAY data type the name is only recorded in the ArrayColElementUdtName column if the element type for it is a UDT.

The ArrayColElementUdt column is only included in the associated views when:
  • TVFields.FieldType = A1 or AN
  • UDTInfo.TypeId = TVFields.TableId

ColumnPartitionNumber

This column is set to the column partition number of the column partition in which the column belongs. 0 indicates the column is not partitioned.

Columns of a table or join index with the same column partition number belong to the same column partition.

CompressValueList

The column contains the name of the compression routine where any complex data type column is defined to use the complex data type internal compression (for example, for a JSON data type, the CompressValueList column value would be “JSON_COMPRESS”).

DecimalTotalDigits

A value of -128 for the DecimalTotalDigits column indicates that the default is used.

DecimalFractionalDigits

The DecimalFractionalDigits column is always -128.

InlineLength

Returns the inline length of a UDT; specifically, this column stores the inline storage size for ST_GEOMETRY, DATASET, XML, and JSON. This column is NULL for all other UDTs and types.

TimeDimension

For information about the possible values of the TimeDimension column, see "TimeDimension Column."

Possible Values for ArrayColElementType

Value Description
A1 One dimensional ARRAY data type
AN Multidimensional ARRAY data type
The ArrayColElementType column is only included in the associated views when:
  • TVFields.FieldType = A1 or AN
  • UDTInfo.TypeId = TVFields.TableId

Possible Values for ArrayColNumberOfDimensions

The range of possible values is 1 through 5.

The ArrayColNumberOfDimensions column is only included in the view when all of the following are true:
  • TVFields.FieldType = A1 or AN
  • UDTInfo.TypeId = TVFields.TableId

For more information about ARRAY data type dimensions, see Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ Data Types and Literals, B035-1143.

Possible Values for ArrayColScope

The ArrayColScope column is only populated when the data type is created. The bound values for each dimension are a string in the [n:m] format.

The ArrayColScope column is only included in the view when all of the following are true:
  • TVFields.FieldType = A1 or AN
  • UDTInfo.TypeId = TVFields.TableId

Possible Values for Compressible

Value Description
A Algorithmic compression (ALC) column
C Multi-value compression column
N Non-compress columns
U Any complex data type column defined to use the Vantage internal compression scheme (for example, the JSON data type). Complex data types are provided by Vantage. They are similar in functionality to user-defined types because they follow an object-oriented model.

Possible Values for CharType

Value Description
1 Latin
2 Unicode
3 KanjiSJIS
4 Graphic
5 Kanji1
0 All other data types

To get information about the storage format of a JSON type column, see Possible Values for StorageFormat.

Possible Values for ColumnPartitionAC

Value Description
NA Not column partitioned
NC No auto compress
AC Auto compress

Possible Values for ColumnPartitionFormat

Value Description
NA Not column partitioned
CS System-determined COLUMN format
CU User-specified COLUMN format
RS System-determined ROW format
RU User-determined ROW format

Possible Values for ColumnType

UF, UV, LF, and LV are internally generated column types for Data Dictionary (database DBC) data only. For character user data, the field type is set to CF or CV.
Value Description
++ TD_ANYTYPE
A1 One dimensional ARRAY data type
AN Multi-dimensional ARRAY data type
AT ANSI Time
BF BYTE Fixed
BO Byte Large Object
BV Byte Varying
CF Character Fixed
CO Character Large Object
CV Character Varying Latin
D Decimal
DA Date
DH Interval Day To Hour
DM Interval Day To Minute
DS Interval Day To Second
DT DATASET type
DY Interval Day
F Float
HM Interval Hour To Minute
HR Interval Hour
HS Interval Hour To Second
I1 1 Byte Integer
I2 2 Byte Integer
I8 8 Byte Integer
I 4 Byte integer
JN JSON document
LF Pre-TD12.0 Character Fixed Locale (Kanji1 or Latin)
This column type is internally generated for Data Dictionary (database DBC) data only.
LV Pre-TD12.0 Character Varying Locale (Kanji1 or Latin)
MI Interval Minute
MO Interval Month
MS Interval Minute To Second
N Number
PD PERIOD(DATE)
PM PERIOD(TIMESTAMP(n) WITH TIMEZONE)
PS PERIOD(TIMESTAMP (n))
PT PERIOD(TIME(n))
PZ PERIOD (TIME(n) WITH TIME ZONE)
SC Interval Second
SZ Timestamp With Time Zone
TS Timestamp
TZ ANSI Time With Time Zone
UF Character Fixed Unicode
This column type is internally generated for Data Dictionary (database DBC) data only.
UT UDT Type
UV Character Varying Unicode
This column type is internally generated for Data Dictionary (database DBC) data only.
XM XML document
YM Interval Year To Month
YR Interval Year

Possible Values for IdColType

Value Description
NULL Non-identity column
GA Generated always
GE Generated always as row end. The required CREATE/ALTER TABLE (ANSI system-time table form) column attribute that defines the ending bound of a system-time period.

For more information about the CREATE/ALTER TABLE (ANSI system-time table form) statement, see Teradata Vantage™ ANSI Temporal Table Support , B035-1186 .

GD Generated by default
GS Generated always as row start. The required CREATE/ALTER TABLE (ANSI system-time table form) column attribute that defines the beginning bound of a system-time period.

For more information about the CREATE/ALTER TABLE (ANSI system-time table form) statement, see Teradata Vantage™ ANSI Temporal Table Support , B035-1186 .

Possible Values for PartitioningColumn

Value Description
N Not a partitioning column
Y Partitioning column of a partitioning expression

Possible Values for PseudoUDTFieldType

Value Description
PB Start column of the derived period column
PE End column of the derived period column
PP Derived period column
NULL The column is not a start or end column of a derived period column or is a derived period column.

Possible Values for SPParameterType

Value Description
B INOUT parameter (for stored procedure only)
C Column for TABLE function
E Result type of the external function or method
I Input parameter of a function or method
O OUT parameter of a function or method
S SELF parameter

Possible Values for StorageFormat

StorageFormat specifies the storage format of any column that has multiple storage formats (such as a JSON column), or NULL if the column does not have this characteristic.

Value Description
NULL A column that does not have multiple storage formats.
TEXT Column contains JSON content in JSON CHARACTER SET LATIN or JSON CHARACTER SET UNICODE.
BSON Column contains JSON content in Binary JSON format.
UBJSON Column contains JSON content in Universal Binary JSON format.
AVRO Column contains DATASET content stored in AVRO format.
CSV Column contains DATASET content stored in CSV format.
There are several commands that provide storage format information:
  • HELP TABLE
  • HELP COLUMN
  • HELP CAST
  • HELP TYPE
  • SHOW TABLE

HELP COLUMN returns information about the storage format of the JSON type; for example, NULL (for a column that does not have multiple storage formats), JSON stored as LATIN or UNICODE text, Binary JSON, or Universal Binary JSON format. Note, when running these commands the data type format is always JN for a JSON column, regardless of the storage format.

HELP TABLE provides information about the character set of the JSON type.

HELP COLUMN returns information about the storage format of the DATASET data type; for example, AVRO.

HELP TYPE is allowed on the DATASET type, but only when referenced using the name 'DATASET' followed by one of the storage formats of the type.

For more information see Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.

Possible Values for TSColumnType

Value Description
TB TD_TIMEBUCKET column
TC TD_TIMECODE column
TN TD_SEQNO column
NULL Any columns that are not the TD_TIMEBUCKET, TD_TIMECODE, or TD_SEQNO column from a time series table.

Possible Values for TTCheckType

Value Description
A ANSIQUALIFIER
The TTCheckType column returns the value A when the qualifier is ANSIQUALIFIER for column-level CHECK constraints.
NULL No transaction-time dimension
C CURRENT TRANSACTIONTIME

Possible Values for UpperCaseFlag

Case flags U, C, and B are valid only for CHAR, VARCHAR, and LONG VARCHAR columns.
Value Description
U Uppercase, not specific
C Not uppercase, specific
N Not uppercase, not specific
B Both

Possible Values for VTCheckType

Value Description
A ANSIQUALIFIER.
The VTCheckType column returns the value A when the qualifier is ANSIQUALIFIER for column-level CHECK constraints.
NULL No valid-time dimension
C CURRENT VALIDTIME
S SEQUENCED VALIDTIME
N NONSEQUENCED VALIDTIME