When to Specify Unprotected Mode | ALTER FUNCTION | Teradata Vantage - When to Specify Unprotected Mode - 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™

To protect the system from new external routines that have not passed your quality metrics, the default execution mode for all external routines is EXECUTE PROTECTED. If the default specification for CREATE/REPLACE FUNCTION were EXECUTE NOT PROTECTED, then an error in the created function could crash the system.

Protected mode is a database state that traps on memory address violations, corrupted data structures, and illegal computations, such as divide-by-zero, to ensure they do not crash the system. Protected mode does not detect or protect against CPU and memory loops or operating system I/Os such as opening external files to read from or write to them.

This execution mode isolates all of the data the external routine might access as a separate process, or “platform” in its own local work space (see Teradata Vantage™ - SQL External Routine Programming, B035-1147 for details). If any memory violation or other system error occurs, then the error is localized to the routine and the transaction executing it, not the entire database (see Protected and Unprotected Execution Modes for more information).

Because this isolation also makes the C or C++ routine run slower, you should change its protection mode to unprotected once it has passed all your quality metrics for being put into production use. You change the execution mode for the routine with a simple ALTER FUNCTION request and specifying the EXECUTE NOT PROTECTED option.

Both Java and row-level security constraint UDFs must run in protected mode and cannot be altered to run in unprotected mode (see Special Considerations for Java UDFs).

Do not make this change for any UDF that makes OS system calls. Any such UDF should always be run in protected mode. You do not need to recompile or relink the external routine after changing its protection mode.

As a rule, the DBA should only specify direct execution mode (assigned by the EXECUTE NOT PROTECTED option) for a function that performs CPU-only operations after it has been thoroughly debugged. No other user than the DBA, not even the software engineer that writes a UDF, should be granted the privileges to alter it to run in unprotected mode. Do not run external routines that cause the OS to consume system resources in unprotected mode. This includes anything that causes the OS to allocate system context, including open files, pipes, semaphores, tokens, threads (processes), and so on.

The following table summarizes how the UDF protection mode options should be used:

IF … THEN specify …
you are still developing and debugging a function EXECUTE PROTECTED.
the function is written in Java EXECUTE PROTECTED.

You cannot alter a Java function to run in unprotected mode.

the function opens a file or uses another operating system resource that requires tracking by the operating system

This includes anything that causes the OS to allocate system context, including open files, pipes, semaphores, tokens, threads (processes), and so on.

EXECUTE PROTECTED.

Running such a function in unprotected mode could interfere with the proper operation of the database.

the function is a computational function that does not use any operating system resources EXECUTE NOT PROTECTED.

Running a UDF in unprotected mode speeds up the processing of the function considerably.

Use this option only after thoroughly debugging the function and making sure it produces the correct output.