Creating the Teradata User Defined Types (UDTs)

The Teradata Data Warehouse Protector automatically creates the To-SQL and From-SQL transform, the ordering, and the necessary casts for a distinct UDT once the CREATE TYPE statement is issued.

On the Data Warehouse Protector installation, the /databaseprotector/teradata/udt/ directory is created with the following files:

generate_udt_scripts.sh is an executable file that generates UDT scripts
pepteradataudt.plm is a library that contains protect and unprotect functions for UDT usage.

The generate_udt_scripts.sh generates UDT scripts using the following command:

/opt/protegrity/databaseprotector/teradata/udt # ./generate_udt_scripts.sh --help

Protegrity Data Security Platform - Teradata UDT Scripts
Usage: generate_udt_scripts udtname dataelement scid dbtype
udtname    : UDT Name
dataelement: Data Element
scid       : Security Coordinate ID
dbtype     : Database data type, must be one of: bigint,date,float,integer,varchar

The following are some limitations for the UDT arguments:

Udtname – any applicable name
Dataelement – DE deployed
Scid – applicable security coordinate (0 by default)
Dbtype – one of the data types bigint, date, float, integer, varchar.

Important: The scid parameter is no longer used and is retained for compatibility purpose only.

To create the User Defined Types:

Log in to the server as the user with the required permissions.
Navigate to the /opt/protegrity/databaseprotector/teradata/udt/ directory.
To view the files and directories in the ../udt/ directory, run the following command:
```
/opt/protegrity/databaseprotector/udt # ls 
```

Press ENTER.

The list of available content appears.

/opt/protegrity/databaseprotector/teradata/udt # ls
generate_udt_scripts.sh  pepteradataudt.plm  sqlscripts

To generate the UDT scripts required for creating the UDTs, run the following command:

./generate_udt_scripts.sh <udtname> <dataelement> <scid> <dbtype>

For example:

./generate_udt_scripts.sh UDT_VARCHAR AES128 0 varchar

Press ENTER.
The script generates the following .sql queries for the UDTs in the /opt/protegrity/databaseprotector/teradata/udt directory.
```
create_UDT_VARCHAR.sql
drop_UDT_VARCHAR.sql
```
It is recommended to use the data element names in the upper-case.
You can modify the .sql queries using the bteq utility for error handling.

To start the bteq utility, run the following command:

/opt/protegrity/databaseprotector/teradata/sqlscripts # bteq

Press ENTER.
The prompt to log in to the database appears.
```
Enter your logon or BTEQ command:
```
To log in to the database, run the following command:
```
.logon <username>
```
Press ENTER.
The prompt to enter the database password appears.
```
Password: 
```
To proceed, type the database password.
Press ENTER.
The connection to the Teradata Data Warehouse is completed successfully.
```
*** Logon successfully completed.
```
To create the UDTs, run the following query:
```
.run file=create_UDT_VARCHAR.sql
```
Press ENTER.
The query creates the UDTs and the following message for each of the created UDT appears.
```
*** Function has been created. 
*** Warning: 5607 Check output for possible warnings encountered in compiling and/or linking UDF/XSP/UDM/UDT.
*** Total elapsed time was 1 second.
```
It is recommended to create only one UDT for each data type.
Creating an additional UDT, with a basic data type that is used by an existing UDT, results in a linked error.
To grant the access permissions to the UDTs, execute the following SQL statements using the bteq utility.
1. To provide the execute access to the UDTs, run the following command:
```
chmod 755 create_UDT_VARCHAR.sql
```
2. Press ENTER.
3. To provide the UDTUSAGE access for the UDTs to public with a GRANT option, run the following query:
```
GRANT UDTUSAGE ON SYSUDTLIB TO PUBLIC WITH GRANT OPTION;
```
4. Press ENTER.
5. To provide the execute function for all the UDTs to public with a GRANT option, run the following query:
```
GRANT ALL ON TYPE SYSUDTLIB.UDT_VARCHAR TO PUBLIC WITH GRANT OPTION;
```
  The protect/unprotect operations for the UDTs must be performed using the bteq utility.
6. Press ENTER.
The script creates the UDTs and grants access permissions using the SQL statements.

Feedback

Was this page helpful?

Last modified : February 20, 2026