| 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 DB2® instances, however these routines
will not perform as well as 64-bit COBOL procedures within a 64-bit DB2 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 DB2 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.
|
DB2 Version 9.7 for Linux, UNIX, and Windows