Function of UDT Transforms | CREATE TRANSFORM | Teradata Vantage - Function of UDT Transforms - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

The CREATE TRANSFORM statement associates a transform group with a UDT. You do not need to create a transform group for any Period or Geospatial data types.

There must be a transform group for each UDT you develop. Note that the system defines a transform group for you when you create a new distinct UDT by default, but you must explicitly define a transform group for all new structured UDTs.

A UDT must have its transform functionality completely defined to be used as the column type of any table, otherwise an attempt to create or alter a table to include a column having the UDT type aborts and the system returns an error to the requestor.

The transform group of a UDT performs the following mappings:
  • From a particular database UDT to the server form of a particular predefined client data type.

    A routine that performs this transform is referred to as a fromsql transform routine.

  • From a predefined client data type to a particular UDT in the database.

    A routine that performs this transform is referred to as a tosql transform routine.

Such a pair of transform mappings is called a transform group. A transform group defines a single tosql/fromsql pair. There must be one, and only one, transform group per UDT.

You can perform a valid CREATE TRANSFORM request that associates only a tosql routine or only a fromsql routine with a UDT, but you must define complete transform functionality for a UDT before you can specify it as the column data type of any table. If you do not, the system aborts the ALTER TABLE or CREATE TABLE request and returns an error to the requestor.

FOR this type of UDT … THE transform group functionality …
one-dimensional and multidimensional ARRAY and VARRAY is generated automatically by the database.

The format of the transformed output in a VARCHAR string is a string of each array element value, referred to as the transformed value string, separated by a comma and delimited by parentheses as indicated below. Assuming the array has n elements:

(<element_1>,<element_2>, … <element_n>)

The following rules apply.

  • BLOB, CLOB, and Geospatial element types are not supported.
  • If the element type is not CHARACTER, VARCHAR, or a UDT with a CHARACTER or VARCHAR attribute, the definition of the array constructor/transformed value string is VARCHAR(64000) CHARACTER SET LATIN.

    If the size of the transformed value string is greater than 64k when the array type is being created, the request aborts and the database returns an error to the requestor.

  • If the element type is CHARACTER, VARCHAR, or a UDT with a CHARACTER or VARCHAR attribute, the definition of the array transformed value string is VARCHAR(max) of the same character set as its element type.

    For the VARCHAR case, max signifies the largest numeric value possible for the size of a VARCHAR in the current character set.

    For a Latin character set, this is 64 KB.

    If the size of the transformed value string is greater than the value of max when the ARRAY type is being created, the request aborts and the database returns an error to the requestor.

  • The system does not report any uninitialized elements. If an element value has been initialized, then all of the elements before it will have also been initialized either to null or to a value.
  • Overflow avoidance.

    The size of an array transformed value string must be within the size limit of a VARCHAR, CHARACTER, and CLOB data types, particularly VARCHAR data type).

    If the size of an array transformed value string is bigger than the size limit of a VARCHAR variable when the ARRAY type is being created, the database aborts the request and returns an error to the requestor.

  • A null element must be indicated by a NULL literal. If the server character set is other than Latin, the null element is indicated by the corresponding encoding of a NULL literal in the server character set. The same applies on a null structured UDT value or a structured UDT with a null attribute.
 
  • The database ignores any pad, new line, or tab characters whether they precede the comma or follow it. The same is true if such characters precede the last APOSTROPHE character or follow the first APOSTROPHE character.

    If the element type is CHARACTER, VARCHAR, or UDT with CHARACTER or VARCHAR attributes, and if ARRAY/VARRAY elements contain many embedded APOSTROPHE characters, this requires an extra APOSTROPHE character to distinguish the embedded APOSTROPHE character. This limits the total size of the transform string that can be output when selecting the ARRAY/VARRAY column because it counts the embedded APOSTROPHE characters.

    If your ARRAY/VARRAY elements contain embedded APOSTROPHE characters, they are also be output when the ARRAY/VARRAY column is selected in transforms ON mode, using the fromsql transform.

    In the worst case, a string with all APOSTROPHE characters embedded, the max transform string is reduced by half. Therefore, if the total size of the transform string that could be generated for an ARRAY/VARRAY type exceeds the maximum row size, the CREATE TYPE request for that ARRAY/VARRAY type aborts and the database returns an error to the requestor.

distinct is generated automatically by the database.

If the system-generated transform group semantics is adequate, you need not define explicit tosql and fromsql transform functionality using the CREATE TRANSFORM statement.

If your applications require different or richer transform semantics, then you can specify explicit transform group functionality using CREATE TRANSFORM.

structured must be defined explicitly using the CREATE TRANSFORM statement.

The system invokes transforms implicitly whenever UDT data is imported to or exported from the database.

The system does not permit you to specify a transform group for the UDT parameter set of external routines. Instead, when a process invokes an external routine, the system passes a UDT handle to the external routine argument rather than passing the UDT value to it directly. The external routine can use this UDT handle to get or set the value of a UDT argument by means of a set of library functions provided by Teradata. For more information, see Teradata Vantage™ - SQL External Routine Programming, B035-1147.

The predefined type that is specified in the transform group routines of a UDT is referred to as the external type of the UDT. The external type is always in Teradata server format, meaning that the normal client format-to-database format transformations continue to take place in addition to UDT-specific tosql and fromsql operations.

For a tosql operation, this means the following actions occur in the sequence indicated.

  1. The system transforms the external type of the UDT from its client format to its database format.
  2. The system performs the tosql routine.

For a fromsql operation, this means the following actions occur in the sequence indicated:

  1. The system performs the fromsql routine to transform the UDT to its external type database form.
  2. The system transforms the external type from its database format to its client format.