Because the entire purpose of transform groups is to pass UDTs between the client and the Teradata Database platform transparently, this section describes some of the important points relative to how client software deals with UDTs. To be accurate, UDTs are not passed to the client. They are transformed into a predefined type and data having that predefined type is passed to the client.
This information is by no means comprehensive. To get the full picture of how various client utilities deal with UDTs, consult the appropriate Teradata Tools and Utilities user documentation.
The first thing to understand is that the Teradata Tools and Utilities are not UDT-aware. Client software does not see UDT values, but values having a predefined data type representing a UDT on the Teradata platform. How a platform UDT is transformed to a client predefined type is entirely at the discretion of the transform developer.
- For distinct UDTs, you must specify the underlying predefined (intrinsic) data. The Teradata Database handles all conversions to and from the UDT by means of transform groups. The Teradata Tools and Utilities do not know that a value will eventually reside in, or that it came from, a distinct UDT.
- For structured UDTs, you have two options:
- Specify the client representation of the UDT as a single field with a single predefined data type.For example, suppose you have a structured UDT called circle that is constructed from 3 attributes, each having the predefined FLOAT data type. The attributes are as follows:
- The x coordinate of the center of the circle.
- The y coordinate of the center of the circle.
- The radius of the circle.
Pursuing the first option, you would combine the attributes into a single field and represent the structured UDT on the client as circle BYTE(24).
The Teradata Database handles the conversion to and from the external and internal UDT representations by means of user-defined transforms.
- Specify the client representation of the UDT as 3 separate fields. Again using the circle example, you would specify 3 separate fields, each having the predefined FLOAT data type, for example:
- x_coord FLOAT
- y_coord FLOAT
- radius FLOAT
You would then construct the structured circle UDT on the Teradata platform side using the NEW expression in the VALUES clause of the INSERT, or the SET clause of the UPDATE. For more information, see Teradata Vantage™ SQL Data Manipulation Language , B035-1146 .
For example, for circle UDT, the syntax is the following:
- Specify the client representation of the UDT as a single field with a single predefined data type.
See the appropriate Teradata Tools and Utilities documents for details.