UDT Information Related To Client Applications and Transforms | Teradata Vantage - Miscellaneous UDT Information Related To Client Applications and Transforms - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

Because the entire purpose of transform groups is to pass UDTs between the client and the 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 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 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:

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

See the appropriate Teradata Tools and Utilities documents for details.