Performance considerations for developing routines

One of the most significant benefits of developing routines, instead of expanding client applications, is performance.

Consider the following performance impacts when you are choosing an approach for routine implementation.

NOT FENCED mode

A NOT FENCED routine runs in the same process as the database manager. In general, running your routine as NOT FENCED results in better performance as compared with running it in FENCED mode because FENCED routines run in a special database manager process outside of the engine's address space.

While you can expect improved routine performance when you are running routines in NOT FENCED mode, user code can accidentally or maliciously corrupt the database or damage the database control structures. You can use NOT FENCED routines when you need to maximize the performance benefits, and if you deem the routine to be secure. For information on assessing and mitigating the risks of registering C/C++ routines as NOT FENCED, see the topic, "Security considerations for routines". If the routine is not safe enough to run in the database manager's process, use the FENCED clause when you are registering the routine. To limit the creation and running of potentially unsafe code, the database manager requires that a user have a special privilege, CREATE_NOT_FENCED_ROUTINE to create NOT FENCED routines.

If an abnormal termination occurs while you are running a NOT FENCED routine, the database manager attempts an appropriate recovery if the routine is registered as NO SQL. However, for routines that are not defined as NO SQL, the database manager fails.

NOT FENCED routines must be precompiled with the WCHARTYPE NOCONVERT option if the routine uses GRAPHIC or DBCLOB data.

FENCED THREADSAFE mode

FENCED THREADSAFE routines run in the same process as other routines. Routines other than Java™ routines share one process, while Java routines share another process. The separation of Java routines process from other routine process protects Java routines from the potentially more error prone routines that are written in other languages. Also, the process for Java routines contains a JVM, which incurs a high memory cost and is not used by other routine types. Multiple invocations of FENCED THREADSAFE routines share resources, and therefore incur less system overhead than FENCED NOT THREADSAFE routines, which each run in their own dedicated process.

If your routine is safe enough to run in the same process as other routines, use the THREADSAFE clause when you are registering it. As with NOT FENCED routines, information on assessing and mitigating the risks of registering C/C++ routines as FENCED THREADSAFE is in the topic, "Security considerations for routines".

If a FENCED THREADSAFE routine abends, only the thread that is running this routine is terminated. Other routines in the process continue running. However, the failure that caused this thread to abend can adversely affect other routine threads in the process, causing them to trap, hang, or have damaged data. After one thread abends, the process is no longer used for new routine invocations. Once all the active users complete their jobs in this process, it is terminated.

When you register Java routines, they are deemed THREADSAFE unless you indicate otherwise. All other LANGUAGE types are NOT THREADSAFE by default.

NOT FENCED routines must be THREADSAFE. It is not possible to register a routine as NOT FENCED NOT THREADSAFE (SQLCODE -104).

If you do not intend to issue SQL in your user-defined function, we recommend that you register it as NO SQL. The database manager uses additional performance enhancements when a FENCED NO SQL routine is invoked.

FENCED NOT THREADSAFE mode

FENCED NOT THREADSAFE routines each run in their own dedicated process. If you are running numerous routines, this can have a detrimental effect on database system performance. If the routine is not safe enough to run in the same process as other routines, use the NOT THREADSAFE clause when you are registering the routine.

Java routines

If you intend to run a Java routine with large memory requirements, you can register the Java routine as FENCED NOT THREADSAFE. For FENCED THREADSAFE Java routine invocations, the database manager attempts to choose a threaded Java fenced mode process with a Java heap that is large enough to run the routine. Failure to isolate large heap consumers in their own process can result in-out-of-Java-heap errors in multithreaded Java database fenced mode process (FMP) processes. If your Java routine does not fall into this category, FENCED routines run better in threadsafe mode where they can share a small number of JVMs.

NOT FENCED Java routines are currently not supported. A Java routine that is defined as NOT FENCED is treated as a FENCED THREADSAFE Java routine.

In Big SQL, PARAMETER STYLE HIVE user-defined functions (UDFs) provide the fastest LANGUAGE JAVA performance since they can be run at the data layer in the BigSQL I/O engine.

C/C++ routines

C or C++ routines are generally faster than Java routines, but are more prone to errors, memory corruption, and crashes. For these reasons, the ability to perform memory operations makes C or C++ routines risky candidates for THREADSAFE or NOT FENCED mode registration. These risks can be mitigated by adhering to programming practices for secure routines (see the topic, "Security considerations for routines"), and thoroughly testing your routine.

SQL routines

SQL routines, particularly SQL procedures, are also generally faster than Java routines, and usually share comparable performance with C routines. SQL routines always run in NOT FENCED mode, providing a further performance benefit over external routines. UDFs that contain complex logic generally run more quickly if written in C than in SQL. If the logic is simple, then an SQL UDF is comparable to any external UDF.

Scratchpads

A scratchpad is a block of memory that can be assigned to UDFs and methods. The scratchpad applies only to the individual reference to the routine in an SQL statement. If there are multiple references to a routine in a statement, each reference has its own scratchpad. A scratchpad enables a UDF or method to save its state from one invocation to the next.

For UDFs and methods with complex initializations, you can use scratchpads to store any values that are required in the first invocation for use in all future invocations. The logic of other UDFs and methods might also require that intermediate values be saved from invocation to invocation.

Use VARCHAR parameters instead of CHAR parameters

You can improve the performance of your routines by using VARCHAR parameters instead of CHAR parameters in the routine definition. Using VARCHAR data types instead of CHAR data types prevents the database manager from padding parameters with spaces before passing the parameter and decreases the amount of time that is required to transmit the parameter across a network.

For example, if your client application passes the string "A SHORT STRING" to a routine that expects a CHAR(200) parameter, the database manager must pad the parameter with 186 spaces, null-terminate the string, then send the entire 200 character string and null-terminator across the network to the routine.

In comparison, passing the same string, "A SHORT STRING," to a routine that expects a VARCHAR(200) parameter results in the database manager simply passing the 14 character string and a null terminator across the network.