UDT Data Type Syntax | Data Types and Literals | Teradata Vantage - UDT Data Type Syntax - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zsn1556242031050.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantage™
[SYSUDTLIB.] udt_name [ attribute [...] ]
SYSUDTLIB.
The name of the database in which all UDTs are created.
udt_name
The name of a UDT that was created with a CREATE TYPE statement.
attribute
Appropriate data type attributes.
A UDT column supports the following attributes:
  • NULL
  • NOT NULL
  • FORMAT
  • TITLE
  • NAMED
  • DEFAULT NULL
For details on using data type attributes with UDTs, see:

A UDT column does not support column storage or column constraint attributes.

ANSI Compliance

UDTs are ANSI SQL:2011 compliant.

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 “CREATE TRANSFORM” in 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.

Authorization

To create a table that has a UDT column, you must have the UDTUSAGE, UDTTYPE, or UDTMETHOD privilege on the SYSUDTLIB database, or have the UDTUSAGE privilege on the specified UDT.

To perform a query on a UDT column, you must have the UDTUSAGE privilege on the specified UDT.

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.

Related Topics

FOR information on … SEE …
functions and operators that support UDTs Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
implementing the functionality for UDFs, UDMs, and external stored procedures that operate on UDTs Teradata Vantage™ - SQL External Routine Programming , B035-1147 .
creating UDT definitions “CREATE TYPE” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
creating and dropping transform definitions
  • “CREATE TRANSFORM and REPLACE TRANSFORM” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • “DROP TRANSFORM” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
specifying nondefault transform group settings for a user “CREATE/MODIFY PROFILE” and “CREATE/MODIFY USER” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
creating cast definitions “CREATE CAST” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
creating tables with UDT columns “CREATE TABLE” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
DisableUDTImplCastForSysFuncOp DBS Control field Teradata Vantage™ - Database Utilities , B035-1102 .