Protected and Unprotected Execution Modes | CREATE/REPLACE FUNCTION | Vantage - 17.10 - Protected and Unprotected Execution Modes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

By default, all UDFs are created to run in protected mode. Protected and secure modes are states in which each instance of a UDF runs in a separate process. The difference between a protected mode server and a secure mode server is that a protected mode server process runs under the predefined OS user tdatuser, while a secure server process runs under the OS user specified by the UDF in its EXTERNAL SECURITY clause. The two processes are otherwise identical.

This is done to protect the system from many common programming errors such as non-valid pointers, corrupted stacks, and illegal computations such as divide-by-zero errors that would otherwise crash the database, produce problems with memory leakage, or cause other potentially damaging results.

These problems all cause the database to crash if they occur in unprotected mode. UDFs can also cause the database to crash in protected mode if they corrupt the shared data areas between the database and the protected mode UDF.

Protected mode is designed to be used for the following purposes only:
  • Testing all UDFs that are in development.
  • Running Java UDFs.

    Java UDFs must be run in protected mode at all times. Because of this, they are slower than the equivalent C or C++ functions.

  • Running any UDFs that cause the OS to consume system resources. This includes anything that causes the OS to allocate system context, including open files, pipes, semaphores, tokens, threads (processes), and so on.

Do not use protected mode for production-ready C or C++ functions that do not make OS system calls. This is because protected mode places additional processing burdens on the system that often result in performance degradation, while UDFs in unprotected mode run in the context of the AMP worker task that is already being used by that query step. If you run C or C++ UDFs in protected mode, they run more slowly.

The best practice is to develop and test your UDFs on a non-production test system. You should run any newly created UDF several hundred times, both to ensure that it does not crash the system and to determine any performance issues that could be avoided by alternate function design and better coding techniques. Poorly designed UDFs can be a significant drain on system performance.

Table cardinality is an important factor in UDF performance, so any tests run against smaller test tables on a non-production system might not scale to the same level of performance when run against significantly larger tables in production databases.

You can use the cufconfig utility to expand the number of protected mode servers from the default value of 2 to a maximum of 20 per AMP or PE vprocs. For more information, see Teradata Vantage™ - Database Utilities, B035-1102 and Teradata Vantage™ - SQL External Routine Programming, B035-1147. The minimum is 0.

Protected mode servers consume disk resources for C and C++ functions as follows:


Total swap space required equation

In unprotected mode, a UDF is called directly by the database rather than running as a separate process. You should only alter a new C or C++ function that does not require the OS to allocate system context to run in unprotected mode after you have thoroughly tested and evaluated its robustness and performance impact. Once the newly created CPU-operations-only C or C++ UDF has passed your quality measures and is ready to be put into production use, you should alter it to run in unprotected mode.

Each Java server for UDFs requires roughly 30 MB of memory for swap space, and there can be two such Java servers per node. A Java UDF multithreaded server for non-secure mode Java UDFs uses a minimum of an additional 30 MB (the amount required can be larger, depending on the size of the JARs for a user), so each node requires approximately 100 MB of swap space if all server flavors are used.