Transform and PeriodStruct Flag Combinations | Teradata Vantage - Flag Setting Combinations - Advanced SQL Engine

Transform and PeriodStruct Flag Combinations | Teradata Vantage - Flag Setting Combinations - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

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

zsn1556242031050.ditamap

dita:ditavalPath

lze1555437562152.ditaval

dita:id

B035-1143

lifecycle

Product Category

Teradata Vantage™

Set the UDTTransformsOff flag to Y to turn on the PeriodStructOn flag.

Teradata Database returns an error if the UDTTransformsOff flag is set to N and the PeriodStructOn flag is set to Y.

The ArrayTransformsOff flag can be set independent of the UDTTransformsOff and PeriodStructOn flags.

The following table provides information on flag setting combinations.

UDTTransformsOff	PeriodStructOn	ArrayTransformsOff	Description	Applicable to
N	N	N	Binary representation for primitive/native data types (for example, Decimal, Integer). UDTs are transformed. Binary representation for Period data types. Arrays are transformed to VARCHAR and the wire-protocol representations in the above bullets are no longer applied to the array elements.	CLI. Default mode.
N	N	Y	Binary representation for primitive/native data types (for example, Decimal, Integer). UDTs are transformed. Binary representation for Period data types. Arrays are untransformed and the wire-protocol representations in the above bullets are applied to the array elements.	.NET, ODBC, and third-party or in-house CLI applications.
N	Y	N or Y	Teradata Database returns an error.
Y	N	Y	Binary representation for primitive/native data types (for example, Decimal, Integer). UDTs are untransformed. Binary representation for Period data types. Arrays are untransformed and the wire-protocol representations in the above bullets are applied to the array elements.	.NET Data Provider uses this combination to add provider-specific support for UDT and UDT to .NET-Object mappings. The application chooses UDT-Transform On and Off.
Y	N	N	Binary representation for primitive/native data types (for example, Decimal, Integer). UDTs are untransformed. Binary representation for Period data types. Arrays are transformed to VARCHAR and the wire-protocol representations in the above bullets are no longer applied to the array elements.	Third-party CLI-based applications. Some existing applications support pass-through SQL. That is, these applications are not limited to a specific physical database model.
Y	Y	Y	Binary representation for primitive/native data types (for example, Decimal, Integer). UDTs are untransformed. Period data types are transmitted as structure with BEGIN and END period (for example, Date, Time). Arrays are untransformed and the wire-protocol representations in the above bullets are applied to the array elements.	JDBC.
Y	Y	N	Binary representation for primitive/native data types (for example, Decimal, Integer). UDTs are untransformed. Period data types are transmitted as structure with BEGIN and END period (for example, Date, Time). Arrays are transformed to VARCHAR and the wire-protocol representations in the above bullets are no longer applied to the array elements.	Third-party CLI based applications. Some existing applications support pass-through SQL. That is, these applications are not limited to a specific physical database model.

ARRAY Data Values Format with Transforms Off

The one-dimensional and multidimensional ARRAY data values with the ArrayTransformsOff flag set to Y can appear in various data parcels, including:

IndicData (parcel flavor number 68)
MultipartIndicData parcel (flavor 142)
Record (in Indicator mode) (parcel flavor number 10)
MultipartRecord (parcel flavor number 144)

One null indicator bit for the ARRAY data type is inside the null indicator bit array.

The following table describes wire-protocol representations of the array value.

Item	Description
2-byte (16-bit SMALLINT) length field	The length excluding itself in bytes.
4-byte (32-bit unsigned integer)	Current Cardinality, which indicates the total number of array elements returned.
Elements-Null-Indicator Bits	A variable-length byte array, where: length = (Current-Cardinality * (number-bits-per-element) + 7) / 8 If the array element is a UDT and the UDTTransformsOff flag is set to Y, the Elements-Null-Indicator Bits for this element is expanded to 1 null indicator bit for the UDT followed by 1 null indicator bit for each attribute of the UDT from left to right in depth-first order. If the array element is a Period data type and the PeriodStructOn flag is set to Y, the Elements-Null-Indicator Bits for this element is expanded to 1 null indicator bit for the Period data type followed by 1 null indicator bit for BEGIN and another null indicator bit for END.
Array Elements Data	Actual data values for each element in row-major order. If the array element is a UDT and the UDTTransformsOff flag is set to Y, each element value is expanded to data values of each attribute of the UDT from left to right in depth-first order. If the array element is a Period data type and the PeriodStructOn flag is set to Y, each element value is expanded to BEGIN value followed by END value.

For a null array with the ArrayTransformsOff flag set to Y, the null indicator bit for the array is set and the 2-byte length field is set to zero. No other value are sent.