Usage Notes - Teradata VantageCloud Lake

ColumnsqV[X] and ColumnsjqV[X]

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

Get 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 called ColumnsqV[X])
ColumnsjqV and ColumnsjqVX (collectively called 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

Column values may show information related to a Teradata temporal table or an ANSI temporal table.

Use the SHOW TABLE statement, HELP COLUMN statement, and the ColumnsV[X] and DBC.PartitioningConstraintsV[X] views to get 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 the table or join index is partitioned.

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 is “JSON_COMPRESS”).

DecimalTotalDigits

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

DecimalFractionalDigits

The DecimalFractionalDigits column is -128.

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

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

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

Possible Values for Compressible

Value	Description
A	Algorithmic compression (ALC) column
C	Multivalue compression column
N	Noncompress columns
U	Complex data type column defined to use the Vantage internal compression scheme (for example, JSON data type). Complex data types provided by Vantage 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	Multidimensional 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.
UV	Character Varying Unicode This column type is internally generated for Data Dictionary (database DBC) data only.
VA	TD_VALIST Type
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 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.
GD	Generated by default
GS	Generated 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.

Possible Values for PartitioningColumn

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

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. JSON UNICODE/LATIN is only supported on the Block File System on the primary cluster. It is not available for the Object File System.
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. The AVRO DATASET data type is only supported on the Block File System on the primary cluster. It is not available for the Object File System.
CSV	Column contains DATASET content stored in CSV format. The CSV DATASET data type is only supported on the Block File System on the primary cluster. It is not available for the Object File System.

The following commands 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 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.

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