16.20 - When To Run External Procedures in Unprotected Mode - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

You should develop your external procedures in protected mode, which is the default when you create a new external procedure. Protected and secure modes are states in which each instance of a procedure 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 2 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 Teradata Database, produce problems with memory leakage, or cause other potentially damaging results.

These problems all cause the Teradata Database to crash if they occur in unprotected mode. External procedures can also cause the database to crash in protected and secure modes if they corrupt the shared data areas between the database and the protected or secure mode procedure.

Protected and secure modes are designed to be used for the following purposes only.
  • Testing all external procedures that are in development.
  • Running any external procedures 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.

  • Running Java procedures.

If a procedure does not perform operations that cause the OS to consume system resources, then you should change its protection mode to EXECUTE NOT PROTECTED (see ALTER PROCEDURE (External Form)) after it has met your qualification standards for production use.

If a procedure does perform operations that cause the OS to consume system resources, then you should always run it in protected mode, even after it has been thoroughly debugged and meets all your production-level quality standards.

The following table summarizes this information for production-ready external procedures.

IF an external procedure … THEN you should run it in this mode …
does not cause the OS to consume system resources unprotected.
performs operations that cause the OS to consume system resources
  • protected under the predefined OS user tdatuser.

or

  • secure under either of the following users.

    tdatuser

    the OS user specified by the EXTERNAL SECURITY clause.

is written in Java protected.

This is not optional. Procedures written in Java must always be run in protected mode.

The best practice is to develop and test your external procedures on a non-production test system. You should run any newly created external procedure 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 procedure design and better coding techniques.

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

Protected mode servers consume disk resources as follows.



In unprotected mode, an external procedure is called directly by the Teradata Database rather than running as a separate process. You should only alter a new procedure 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 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 2 such Java servers per node. A Java multithreaded server for non-secure mode Java procedures 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.