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

Teradata Vantage™ - Data Types and Literals
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

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

SQL Engine 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 SQL Engine 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.