15.10 - Flag Setting Combinations - Teradata Database

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.

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.

Following are examples of ARRAY data type values when the ArrayTransformsOff flag is set to Y. These examples focus on the combinations most commonly used by ODBC/.NET and JDBC

UDTTransformsOff set to Y, PeriodStructOn set to Y, and ArrayTransformsOff set to Y (YYY in the examples)

UDTTransformsOff set to N, PeriodStructOn set to N, and ArrayTransformsOff set to Y (NNY in the examples)

Example 1: One-Dimensional Array With One Null Element

/*Oracle-compatible and TD syntax respectively: */

Create Type intarray as VARRAY(3) OF Int;

Create Type intarray as Int Array[3];

Create Table tab1 (id int, phonenumber intarray);

Ins into tab1(10, NEW intarray(111222333, NULL, 123456789));

Sel phonenumber from tab1;

For both YYY and NNY, the parcel body fields of the data parcels for the select query contain:

1 The null indicator bit array is (n+7)/8 = (1+7)/8 = 1 byte and appears as "00000000".

2 The length field is a 2-byte SMALLINT of 17.

3 The Current Cardinality field is a 4-byte unsigned integer of 3.

4 The Elements-Null-Indicator Bits is (3+7)/8= 1 byte and appears as "01000000", because the second element is NULL.

5 The Array Elements Data is the binary representation of integer 111222333, followed by the dummy null value of integer zero, followed by integer 123456789.

Example 2: One-Dimensional Array With UDT Element Type

This example is a one-dimensional array with a UDT element type with a null attribute.

CREATE TYPE employee AS (name VARCHAR(10), 

                         employee_id INTEGER)

...;

/*Oracle-compatible and TD syntax respectively: */

CREATE TYPE emparray AS VARRAY(20) OF employee;

CREATE TYPE emparray AS employee ARRAY[20];

Create Table tab2 (dept_no int, dept_emps emparray);

Ins into tab2(10, NEW emparray (NEW employee('Mike', NULL), NEW 

employee('Mark', 101)));

Sel dept_emps from tab2;

For YYY, the parcel body fields of the data parcels for the select query contain:

1 The null indicator bit array is (n+7)/8 = (1+7)/8 = 1 byte and appears as "00000000".

2 The length field is a 2-byte SMALLINT of 25.

3 The Current Cardinality field is a 4-byte unsigned integer of 2.

4 The Elements-Null-Indicator Bits is (3+3+7)/8= 1 byte and appears as "00100000", because the second attribute of the first element is NULL.

5 The Array Elements Data is the binary representation of string 'Mike', followed by the dummy NULL value of integer zero, followed by string 'Mark' which is followed by integer 101.

For NNY, assuming NEW employee ('Mike', NULL) is transformed to 'Mike-NULL' and NEW employee ('Mark', 101)) is transformed to 'Mark-101', the parcel body fields of the data parcels for the select query contain:

1 The null indicator bit array is (n+7)/8 = (1+7)/8 = 1 byte and appears as "00000000".

2 The length field is a 2-byte SMALLINT of 26.

3 The Current Cardinality field is a 4-byte unsigned integer of 2.

4 The Elements-Null-Indicator Bits is (2+7)/8= 1 byte and appears as "00000000".

5 The Array Elements Data is the binary representation of string 'Mike-NULL', followed by string 'Mark-101'.

Example 3: Multidimensional Array With One Null Element

/*Oracle-compatible and TD syntax respectively: */

Create Type TwoD_intarray as VARRAY(2)(2) OF Integer;

Create Type TwoD_intarray as Integer Array[2][2];

Create Table tab3 (id int, simulation TwoD_intarray);

Ins into tab3(10, NEW TwoD_intarray(111222333, 123456789, NULL, 777777777));

Sel simulation from tab3;

For both YYY and NNY, the parcel body fields of the data parcels for the above query contain:

1 The null indicator bit array is (n+7)/8 = (1+7)/8 = 1 byte and appears as "00000000".

2 The length field is a 2-byte SMALLINT of 21.

3 The Current Cardinality field is a 4-byte unsigned integer of 4.

4 The Elements-Null-Indicator Bits is (4+7)/8= 1 byte and appears as "00100000".

5 The Array Elements Data is the binary representation of integer 111222333, followed by integer 123456789, followed by the dummy null value of integer zero which is followed by integer 777777777.