15.00 - Miscellaneous UDT Information Related To Client Applications and Transforms - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Miscellaneous UDT Information Related To Client Applications and Transforms

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.

Because of this, the following things are true about defining input record layouts (such as the FIELD command in a MultiLoad or TPump record layout):

  • 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 (see SQL Functions, Operators, Expressions, and Predicates).

    For example, for circle UDT, the syntax is the following:

         NEW circle(:x,:y,:r)

    See the appropriate Teradata Tools and Utilities manuals for details.