DB2 Version 9.7 for Linux, UNIX, and Windows

Supported routine programming languages

In general, routines are used to improve overall performance of the database management system by enabling application functionality to be performed on the database server. The amount of gain realized by these efforts is limited, to some degree, by the language chosen to write a routine.

Some of the issues you should consider before implementing routines in a certain language are:

The available skills for developing a routine in a particular language and environment.
The reliability and safety of a language's implemented code.
The scalability of routines written in a particular language.

To help assess the preceding criteria, here are some characteristics of various supported languages:

SQL

SQL routines are faster than Java™ routines, and roughly equivalent in performance to NOT FENCED C/C++ routines.
SQL routines are written completely in SQL, and can include elements of SQL Procedural Language (SQL PL), which contains SQL control-statements that can be used to implement logic.
SQL routines are considered 'safe' by DB2® as they consist entirely of SQL statements. SQL routines always run directly in the database engine, giving them good performance, and scalability.

C/C++

Both C/C++ embedded SQL and DB2 CLI routines are faster than Java routines. They are roughly equivalent in performance to SQL routines when run in NOT FENCED mode.
C/C++ routines are prone to error. It is recommended that you register C/C++ routines as FENCED NOT THREADSAFE, because routines in these languages are the most likely to disrupt the functioning of DB2's database engine by causing memory corruption. Running in FENCED NOT THREADSAFE mode, while safer, incurs performance overhead.
For information on assessing and mitigating the risks of registering C/C++ routines as NOT FENCED or FENCED THREADSAFE, see the topic, "Security considerations for routines".
By default, C/C++ routines run in FENCED NOT THREADSAFE mode to isolate them from damaging the execution of other routines. Because of this, you will have one db2fmp process per concurrently executing C/C++ routine on the database server. This can result in scalability problems on some systems.

Java

Java routines are slower than C/C++ or SQL routines.
Java routines are safer than C/C++ routines because control of dangerous operations is handled by the JVM. Because of this, reliability is increased, as it is difficult for a Java routine to damage another routine running in the same process.
Note: To avoid potentially dangerous operations, Java Native Interface (JNI) calls from Java routines are not permitted. If you need to invoke C/C++ code from a Java routine, you can do so by invoking a separately cataloged C/C++ routine.
When run in FENCED THREADSAFE mode (the default), Java routines scale well. All FENCED Java routines will share a few JVMs (more than one JVM might be in use on the system if the Java heap of a particular db2fmp process is approaching exhaustion).
NOT FENCED Java routines are currently not supported. A Java routine defined as NOT FENCED will be invoked as if it had been defined as FENCED THREADSAFE.

.NET common language runtime languages

.NET common language runtime (CLR) routines are routines that are compiled into intermediate language (IL) byte code that can be interpreted by the CLR of the .NET Framework. The source code for a CLR routine can be written in any .NET Framework supported language.
Working with .NET CLR routines allows the user the flexibility to code in the .NET CLR supported programming language of their choice.
CLR assemblies can be built up from sub-assemblies that were compiled from different .NET programming language source code, which allows the user to re-use and integrate code modules written in various languages.
CLR routines can only be created as FENCED NOT THREADSAFE routines. This minimizes the possibility of engine corruption, but also means that these routines cannot benefit from the performance opportunity that can be had with NOT FENCED routines.

OLE

OLE routines can be implemented in Visual C++, Visual Basic and other languages supported by OLE.
The speed of OLE automated routines depends on the language used to implement them. In general, they are slower than non-OLE C/C++ routines.
OLE routines can only run in FENCED NOT THREADSAFE mode. This minimizes the chance of engine corruption. This also means that OLE automated routines do not scale well.

OLE DB

OLE DB can only be used to define table functions.
OLE DB table functions connect to a external OLE DB data source.
Depending on the OLE DB provider, OLE DB table functions are generally faster than Java table functions, but slower than C/C++ or SQL-bodied table functions. However, some predicates from the query where the function is invoked might be evaluated at the OLE DB provider, therefore reducing the number of rows that DB2 has to process. This frequently results in improved performance.
OLE DB routines can only run in FENCED NOT THREADSAFE mode. This minimizes the chance of engine corruption. This also means that OLE DB automated table functions do not scale well.