Working with Attributes in a Structured UDT | ALTER TYPE | Teradata Vantage - Adding Attributes To a Structured UDT - 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™

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:
  • 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.

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.