15.00 - SYSUDTLIB Database - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference


SYSUDTLIB is a system database that contain all UDTs, their methods, cast routines, ordering routines, transform routines, and any UDFs that are used as cast routines, ordering routines, or transform routines. Because SYSUDTLIB is a system database, it is created by a DIP script. SYSUDTLIB is created as a database, not a user, so you cannot log on as user SYSUDTLIB.

When the system resolves SQL requests that involve UDTs, it only searches SYSUDTLIB for UDTs and UDT‑related database objects. Because of this, you need not explicitly specify a database name for UDTs. If you do specify a database name, however, it must be SYSUDTLIB.

You should not place non-UDT‑related objects in SYSUDTLIB. The database requires space for every UDT you define as well as for every method or UDT‑related UDF created within it. SYSUDTLIB space usage is dependent primarily on the space required to store the code files implementing the UDM or UDF, or the space required to contain internally generated source code that is used to acquire an instance of a UDT.

The initial space allocation for SYSUDTLIB is 0. The DBA must modify the definition for SYSUDTLIB to assign enough space that UDTs, UDMs, and UDT‑related UDFs can be created within it. If you attempt to create a UDT‑related object in SYSUDTLIB when there is insufficient space to do so, the request aborts and the system returns an error to the requestor. As time passes, you might need to increase the storage associated with SYSUDTLIB to accommodate the growing number of UDTs, UDMs, and UDT‑related UDFs.

For details on modifying the definition for SYSUDTLIB, see “MODIFY DATABASE” on page 827.

Beginning with the PSM-96 standard (ANSI/ISO/IEC 9075-4:1995, Information technology—Database Languages—SQL—Part 4: Persistent Stored Modules (SQL/PSM). The PSM-96 standard dealt only with stored procedures), the ANSI SQL committee defines the following family of SQL‑invoked routines:

  • Stored procedures.
  • User‑defined functions.
  • User‑defined methods.
  • The ANSI SQL:2008 standard categorizes these SQL-invoked routines as either external routines or SQL routines. UDMs, like external UDFs and external stored procedures, are external routines.

    An external routine is a user-defined function (UDF), method (UDM), or external stored procedure that is written in an external (meaning non‑SQL) language such as C or C++, while SQL routines are written entirely in the SQL language.

    When an external routine associated with a UDT is invoked, the system passes a handle to the UDT argument instead of passing the UDT value. Given the handle, the external routine can either retrieve or set the value of a UDT argument by means of a set of library functions provided by Teradata for this purpose (see SQL External Routine Programming for details).