15.00 - When To Run External Procedures in Unprotected Mode - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

When To Run External Procedures in Unprotected Mode

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)” on page 24) 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 Utilities: Volume 1 (A-K) and SQL External Routine Programming) 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.