17.10 - Usage Notes - 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)

Types of UDTs

Vantage supports distinct and structured UDTs.

UDT Type Description Example
Distinct A UDT that is based on a single predefined data type, such as INTEGER or VARCHAR. A distinct UDT named euro that is based on a DECIMAL(8,2) data type can store monetary data.
Structured A collection of one or more fields called attributes, where each attribute is defined as a predefined data type or other UDT (nesting is supported). A structured UDT named circle can consist of x-coordinate, y-coordinate, and radius attributes.

UDTs can further be classified as LOB UDTs or non-LOB UDTs.

IF the UDT is … AND … THEN the UDT is a …
distinct the predefined data type on which it is based is a CLOB or BLOB LOB UDT.
the predefined data type on which it is based is not a LOB non-LOB UDT.
structured the data type of at least one attribute is CLOB, BLOB, or LOB UDT LOB UDT.
none of the attributes has a LOB or LOB UDT data type non-LOB UDT.

You can create user-defined methods (UDMs) that operate on distinct and structured UDTs. For example, for a distinct UDT named euro, you can define a method that converts the value to a US dollar amount. Similarly, for a structured UDT named circle, you can define a method that computes the area of the circle using the radius attribute.

External Representation

Every UDT has a corresponding predefined SQL data type that Vantage uses for import and export operations between client applications and the server. The predefined data type that maps to a particular UDT is specified in the transform definition associated with the UDT.

IF the UDT is … THEN …
distinct Vantage automatically generates a default transform definition for import and export operations that maps the UDT to the predefined data type on which it is based.

For example, consider a distinct UDT named euro that is based on a DECIMAL(8,2) data type.

The transform definition that Vantage automatically generates allows a client application to load data into a euro column using the external representation of DECIMAL(8,2). A client application can also perform queries on the euro column and receive values in the same format as DECIMAL(8,2).

structured a transform definition must be created for the UDT using the CREATE TRANSFORM statement.

The transform definition specifies a predefined SQL data type that acts as a container to import and export attribute values of the structured UDT.

For example, consider a structured UDT named circle that consists of x-coordinate, y-coordinate, and radius attributes. Suppose the data type of each attribute is FLOAT.

The transform definition could map a BYTE(24) predefined type to the circle UDT, allowing eight bytes for each FLOAT attribute. The client application can load data into a circle column using the external representation of BYTE(24). Similarly, the application can perform queries on the circle column and receive the attribute values in a BYTE(24) container.

The logic for import operations that extracts the values from a predefined SQL data type and sets the corresponding UDT attributes is implemented as a UDF that is specified in the CREATE TRANSFORM statement. Similarly, the logic for export operations that packs the UDT attribute values into the predefined SQL data type is implemented as a UDF or UDM.

For more information on transform definitions, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

For more information about external representations for UDTs, see External Representations for UDTs.

Support for Multiple Transform Groups

You can create multiple transform groups for each UDT by using the CREATE TRANSFORM or REPLACE TRANSFORM statements. CREATE TRANSFORM also allows you to add transform groups for a UDT that already has existing transform groups. REPLACE TRANSFORM drops all existing transform groups for a UDT and creates new transform groups for the UDT. You can also use DROP TRANSFORM to delete transform groups from a UDT.

The maximum number of transform groups allowed for a particular UDT is 16.

You cannot use CREATE TRANSFORM or REPLACE TRANSFORM to create new transforms for complex data types (CDTs). You can only create new transforms for structured and distinct user-defined types (UDTs).

When using the CREATE TRANSFORM statement, it is possible to only specify either a from-sql or to-sql function for a transform. However, in order to create a table with a UDT column, the default transform group for the UDT must contain both from-sql and to-sql functions in the transform group.

You can use the TRANSFORM option in the CREATE PROFILE/MODIFY PROFILE or CREATE USER/MODIFY USER statements to specify for a user the particular transform group that will be used for a given data type.

You can use the following macros to find the transform group for a UDT (or CDT), or the transform group settings for a user, profile, or current session.

Macro Description
SYSUDTLIB.HelpCurrentUserTransforms Lists the transform group settings of the current logon user.
SYSUDTLIB.HelpCurrentSessionTransforms Lists the transform group settings of the current session.
SYSUDTLIB.HelpUserTransforms(User) Lists the transform group settings for a specific user.
SYSUDTLIB.HelpCurrentUDTTransform(UDT)

Lists the transform group settings of the current session for the specified UDT.

SYSUDTLIB.HelpUDTTransform(User,UDT) Lists the transform group for a UDT for a user.
SYSUDTLIB.HelpProfileTransforms(Profile) Lists the transform group settings for a specific profile.

SYSUDTLIB.HelpProfileTransform(Profile,UDT)

Lists the transform group for a UDT for a profile.

For more information about these macros, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

Restrictions

  • You can declare a primary or secondary index on a UDT column when you create an indexed table, join index, or hash index. However, the following UDT types are not supported for any form of primary or secondary index.
    • LOB UDTs
    • VARIANT_TYPE UDT
  • Restrictions that apply to CLOB and BLOB data types also apply to LOB UDTs:
    • A table can have a maximum combination of 32 CLOB, BLOB, or LOB UDT columns.
    • Queue tables cannot have CLOB, BLOB, or LOB UDT columns.
  • If you have existing UDTs with non-ASCII characters in the name, you cannot use the ASCII session character set to create new UDTs with LATIN characters in the name. You must use the UTF8 or UTF16 session character sets instead.

Functions That Operate on UDTs

You can specify UDTs as parameters and return types for UDFs written in C, C++, or Java. This includes scalar and aggregate UDFs, table functions, and table operators.

You can specify UDTs as IN, INOUT, and OUT parameters of stored procedures and external stored procedures written in C, C++, or Java.

FNC functions and Java classes and methods are provided to enable a UDF or external stored procedure to access and set the value of a UDT parameter, or to get information about the UDT parameter. For information about these functions and methods, see Teradata Vantage™ - SQL External Routine Programming, B035-1147.

You can use UDTs with most SQL functions and operators, with the exception of ordered analytical functions, provided that the following is true:
  • An implicit cast definition exists that casts the UDT to a predefined type that is accepted by the function or operator.
  • The DisableUDTImplCastForSysFuncOp DBS Control field is set to zero.

For more information on using UDTs with SQL functions and operators, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.