To protect the system from new external routines that have not yet passed your quality metrics, the default run mode for all external routines is EXECUTE PROTECTED. If the default specification for CREATE/REPLACE PROCEDURE were EXECUTE NOT PROTECTED, an error in the created function may stop 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 make sure they do not stop 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 data the external routine may access as a separate process, or "server" in its own local work space. If any memory violation or other system error occurs, then the error is localized to the routine and the transaction running it, not the entire database (see "Protected and Unprotected Execution Modes in CREATE FUNCTION and REPLACE FUNCTION (External Form)" in General Usage Guidelines: CREATE FUNCTION and REPLACE FUNCTION (External Form) for more information).
Because this isolation also makes the routine run slower, change its protection mode to unprotected after it has passed your quality metrics for being put into production use. You change the execution mode for the routine with ALTER PROCEDURE (see General Usage Guidelines for ALTER PROCEDURE (External Form)), specifying EXECUTE NOT PROTECTED.
Do not make this change for any procedure that makes OS system calls. Any such procedure must run in protected mode. You need not recompile or relink the external routine after changing its protection mode.
The DBA must only specify direct run mode (assigned by the EXECUTE NOT PROTECTED option) for a procedure that performs CPU-only operations after it has been thoroughly debugged. No other user than the DBA, not even the software engineer who writes a UDF, must be granted the privileges to alter it to run in unprotected mode. Never 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 external procedure protection mode options must be used.
| Situation | Protection Mode |
|---|---|
| You are developing and debugging a procedure. | EXECUTE PROTECTED. |
| The procedure uses a resource that the operating system must track—anything that causes the OS to allocate system context (open files, pipes, semaphores, tokens, threads, processes, and so on). | EXECUTE PROTECTED. Unprotected mode can interfere with proper database operation. |
| The procedure is linked with CLIv2 or written in Java. | EXECUTE PROTECTED. |
| The procedure uses no operating system resources and has been debugged. | EXECUTE NOT PROTECTED. Running an external procedure in unprotected mode speeds up the processing of the function considerably. |