Working with Attributes in a Structured UDT | ALTER TYPE | Teradata Vantage - Adding Attributes To a Structured UDT - Analytics Database

Working with Attributes in a Structured UDT | ALTER TYPE | Teradata Vantage - Adding Attributes To a Structured UDT - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment

VantageCloud

VantageCore

Edition

Enterprise

IntelliFlex

VMware

Product

Analytics Database

Teradata Vantage

Release Number

17.20

Published

June 2022

Language

English (United States)

Last Update

2023-07-11

dita:mapPath

vuk1628111288877.ditamap

dita:ditavalPath

qkf1628213546010.ditaval

dita:id

B035-1184

lifecycle

latest

Product Category

Teradata Vantage™

The system automatically generates an associated observer and mutator method for each attribute you add to a UDT.

The following rules apply to adding attributes.

The UDT that you specify must be a structured UDT.
The specified UDT must not be referenced by any other database object.
You cannot add an attribute to a UDT being referenced by some other database object, such as being used as the column data type of any table.
This includes:
- A table in any database for which the data type of one of the columns is the specified UDT.
- A structured UDT for which the data type of one of the attributes is the specified UDT.
- The specified UDT is referenced in a user-defined cast (see CREATE CAST and REPLACE CAST).
- A method or UDF in any database that references the specified UDT.
- An ordering or transform group defined for the UDT (see CREATE ORDERING and REPLACE ORDERING and CREATE TRANSFORM and REPLACE TRANSFORM).
The name of an added attribute must be different than all existing attribute names for the specified UDT.
The database creates a default observer and mutator method for each new attribute that you add.
The method signatures of the observer and mutator must be different than all existing method signatures for the specified UDT.
You cannot add a character data type with a server data set of KANJI1 to a structured data type. Otherwise, the database returns an error to the requestor.

This means that you, as a UDT developer, must perform all the necessary cleanup on all of the following database objects before you add any attributes to a structured UDT.

Casts associated with the UDT.
See CREATE CAST and REPLACE CAST.
Orderings for the UDT.
See CREATE ORDERING and REPLACE ORDERING.
Transforms for the UDT.
See CREATE TRANSFORM and REPLACE TRANSFORM.
Tables whose columns are typed with the UDT.
See CREATE TABLE.
Structured UDTs that use the UDT.
See CREATE TYPE (Structured Form).
UDFs and methods that use the UDT as a parameter type.
See CREATE FUNCTION and REPLACE FUNCTION (External Form) and CREATE METHOD.

You must also remove any dependent objects before you add attributes to a structured type. To assist you in this task, you can use a system-installed macro found in the SYSUDTLIB database that lists all dependencies and the order in which they must be dropped.

To do this, type the following request.

EXEC SYSUDTLIB.HelpDependencies('UDT_name');

where UDT_name is the name of the structured UDT to which you are adding an attribute set.

The ability to add multiple attributes using the ADD ATTRIBUTE option is a Teradata extension to the ANSI SQL:2011 standard. You have the option of either submitting ALTER TYPE requests that comply with the ANSI SQL standard by adding only one attribute at a time, or of adding multiple attributes simultaneously using the Teradata extension to the ANSI SQL:2011 syntax.

You cannot add more than 512 attributes at a time to a structured UDT.