| SQL (includes SQL PL) |
- SQL is a high level language that is easy to learn and use, which
makes implementation go quickly.
- SQL Procedural Language (SQL PL) elements allow for control-flow
logic around SQL operations and queries.
|
- Very good.
- SQL routines perform better than Java™ routines.
- SQL routines perform as well as C and C++ external routines created
with the NOT FENCED clause.
|
- Very safe.
- SQL procedures always run in the same memory as the database manager.
This corresponds to the routine being created by default with the
keywords NOT FENCED.
|
|
- Cannot access the database server file system.
- Cannot invoke applications that reside outside of the database.
|
| Embedded SQL (includes C and C++) |
- Low level, but powerful programming language.
|
- Very good.
- C and C++ routines perform better than Java routines.
- C and C++ routines created with the NOT FENCED clause perform
as well as SQL routines.
|
- C and C++ routines are prone to programming errors.
- Programmers must be proficient in C to avoid making common memory
and pointer manipulation errors which make routine implementation
more tedious and time consuming.
- C and C++ routines should be created with the FENCED clause and
the NOT THREADSAFE clause to avoid the disruption of the database
manager should an exception occur in the routine at run time. These
are default clauses. The use of these clauses can somewhat negatively
impact performance, but ensure safe execution. See: Security of routines .
|
- Scalability is reduced when C and C++ routines are created with
the FENCED and NOT THREADSAFE clauses. These routines are run in
an isolated db2fmp process apart from the database
manager process. One db2fmp process is required
per concurrently executed routine.
|
- There are multiple supported parameter passing styles which can
be confusing. Users should use parameter style SQL as much as possible.
|
| Embedded SQL (COBOL) |
- High-level programming language good for developing business,
typically file oriented, applications.
- Pervasively used in the past for production business applications,
although its popularity is decreasing.
- COBOL does not contain pointer support and is a linear iterative
programming language.
|
- COBOL routines do not perform as well as routines created with
any of the other external routine implementation options.
|
- No information at this time.
|
- No information at this time.
|
- You can create and invoke 32-bit COBOL procedures in 64-bit database instances, however these routines will
not perform as well as 64-bit COBOL procedures within a 64-bit database instance.
|
| JDBC (Java) and SQLJ (Java) |
- High-level object-oriented programming language suitable for developing
standalone applications, applets, and servlets.
- Java objects and data types
facilitate the establishment of database connections, execution of
SQL statements, and manipulation of data.
|
- Java routines do not perform
as well as C and C++ routines or SQL routines.
|
- Java routines are safer
than C and C++ routines, because the control of dangerous operations
is handled by the Java Virtual
Machine (JVM). This increases reliability and makes it very difficult
for the code of one Java routine
to harm another routine running in the same process.
|
- Good scalability
- Java routines created with
the FENCED THREADSAFE clause (the default) 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.
|
- To avoid potentially dangerous operations, Java Native Interface (JNI) calls from Java routines are not permitted.
|
| .NET common language runtime supported languages (includes C#, Visual Basic, and
others) |
- Part of the Microsoft .NET model of managed
code.
- Source code is compiled into intermediate language (IL) byte code that can be interpreted by the
Microsoft .NET Framework common language runtime.
- CLR assemblies can be built up from sub-assemblies that were compiled from different .NET
programming language source code, which allows users to re-use and integrate code modules written in
various languages.
|
- CLR routines can only be created with the FENCED NOT THREADSAFE clause so as to minimize the
possibility of database manager interruption at runtime. This can somewhat negatively impact
performance
- Use of the default clause values minimizes the possibility of database manager interruption at
runtime; however because CLR routines must run as FENCED, they might perform slightly more slowly
than other external routines that can be specified as NOT FENCED.
|
- CLR routines can only be created with the FENCED NOT THREADSAFE clause. They are therefore safe
because they will be run outside of the database manager in a separate db2fmp
process.
|
- No information available.
|
- Refer to the topic, "Restrictions on .NET CLR routines".
|
|
|
- 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, and therefore OLE automated routines do
not scale well.
|
- No information available.
|
- No information available.
|
- No information available.
|
|
|
- OLE DB can be used to create user-defined table functions.
- OLE DB functions connect to external OLE DB data sources.
|
- Performance of OLE DB functions depends on the OLE DB provider, however in general OLE DB
functions perform better than logically equivalent Java
functions, but slower than logically equivalent C, C++, or SQL 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 the database manager has to process, which
can frequently result in improved performance.
|
- No information available.
|
- No information available.
|
- OLE DB can only be used to create user-defined table functions.
|