Teradata UDFs

This page provides a detailed list of User Defined Functions (UDFs) for general information, protection, and unprotection of data with different data types.

It is recommended to run the sample queries in BTEQ (Basic Teradata Query). For more information, refer to Sample Scripts provided in the Teradata Data Warehouse Protector package at the default location, /opt/protegrity/databaseprotector/teradata/sqlscripts/.

Protegrity UDFs can support the JSON format for protection and unprotection. It is not possible to mask data stored in XML or JSON (JavaScript Object Notation) formats. While executing the Unprotect UDFs for these formats, clear data is returned with an error message. Masking is supported only with the Varchar UDFs.

Teradata UDFs for Protection

This section provides a detailed list of User Defined Functions (UDFs) for general information, and protection, unprotection, and tokenization of data with different data types.

Teradata UDFs - Deterministic and Non-deterministic clauses

Teradata supports the following two optional clauses to categorize if the UDF returns identical results for identical inputs or not.

• DETERMINISTIC - specifies that the UDF function returns the same results for identical inputs. The de-tokenization and decryption UDFs are defined with the DETERMINISTIC clause.
• NOT DETERMINISTIC - specifies that the UDF function returns non-identical results for identical inputs. This is the default option. The tokenization and encryption UDFs are defined with the NOT DETERMINISTIC clause.

Risk

In case of a query with constant arguments to the DETERMINISTIC UDF call, Teradata may cache the result of the evaluated UDF, as designed. During subsequent query execution, the results may be fetched from the Teradata internal cache without evaluating the UDF.

This is a risk because it can cause unauthorized access to the protected data due to lack of authorization check during the UDF execution. In addition, altering the clause to NOT DETERMINISTIC may cause performance issues as the UDFs defined with the DETERMINISTIC clause execute faster in comparison to the UDFs defined with the NOT DETERMINISTIC clause.

As per usage, if you are not using any constants in the UDF call, then you can recreate the UDF with the DETERMINISTIC clause to ensure faster performance.

Important: For all the Teradata UDFs, the communicationid and scid parameters are no longer used and are retained for compatibility purposes only. It is recommended to set the values for these parameters as zero.

• General UDFs
• Access Check UDFs
• Varchar Latin UDFs
• Varchar Unicode UDF
• Float UDFs
• Small Integer UDFs
• Integer UDFs
• Big Integer UDFs
• Date UDFs
• 8-Byte AND 16-Byte Decimal UDFs
• JSON UDFs
• XML UDFs

Teradata UDFs for No Encryption

This section provides a list of User Defined Functions (UDFs) that can be used with No Encryption data elements.

• Float UDFs for No Encryption
• Date UDFs for No Encryption
• 8-Byte and 16-Byte Decimal UDFs for No Encryption