Creating external routines

External routines including procedures and functions are created in a similar way as routines with other implementations, however there are a few more steps required, because the routine implementation requires the coding, compilation, and deployment of source code.

Before you begin

  • The IBM® Data Server Client must be installed.
  • The database server must be running an operating system that supports the chosen implementation programming language compilers and development software.
  • The required compilers and runtime support for the chosen programming language must be installed on the database server
  • Authority to execute the CREATE PROCEDURE, CREATE FUNCTION, or CREATE METHOD statement.


Restrictions

For a list of restrictions that are associated with external routines see:

About this task

You would choose to implement an external routine if:
  • You want to encapsulate complex logic into a routine that accesses the database or that performs an action outside of the database.
  • You require the encapsulated logic to be invoked from any of: multiple applications, the CLP, another routine (procedure, function (UDF), or method), or a trigger.
  • You are most comfortable coding this logic in a programming language rather than using SQL and SQL PL statements.
  • You require the routine logic to perform operations external to the database such as writing or reading to a file on the database server, the running of another application, or logic that cannot be represented with SQL and SQL PL statements.

Procedure

  1. Code the routine logic in the chosen programming language.
    • For general information about external routines, routine features, and routine feature implementation, see the topics that are referenced in the Prerequisites section.
    • Use or import any header files that are required to support the execution of SQL statements.
    • Declare variables and parameters correctly using programming language data types that map to Db2® SQL data types.
  2. Parameters must be declared in accordance with the format required by the parameter style for the chosen programming language. For more on parameters and prototype declarations see:
  3. Build your code into a library or class file.
  4. Copy the library or class file into the function directory on the database server. You can store assemblies or libraries that are associated with routines in the function directory. To find out more about the function directory, see the EXTERNAL clause of either of the following statements: CREATE PROCEDURE or CREATE FUNCTION.

    You can copy the assembly to another directory on the server if you wish, but to successfully invoke the routine you must note the fully qualified path name of your assembly as you will require it for the next step.

    The assembly or library must be accessible on all physical hosts that are listed in the db2nodes.cfg file. If you copied the assembly or library to a directory that is not located on a shared file system, which is accessible to all hosts, then you must manually copy the file to the same location on each host.

    If your routine is written in Java™, consider using the SQLJ.INSTALL_JAR built-in procedure to manage the deployment of the routine implementation.

  5. Execute either dynamically or statically the appropriate SQL language CREATE statement for the routine type: CREATE PROCEDURE or CREATE FUNCTION.
    • Specify the LANGUAGE clause with the appropriate value for the chosen API or programming language. Examples include: CLR, C, JAVA.
    • Specify the PARAMETER STYLE clause with the name of the supported parameter style that was implemented in the routine code.
    • Specify the EXTERNAL clause with the name of the library, class, or assembly file to be associated with the routine using one of the following values:
      • the fully qualified path name of the routine library, class, or assembly file .
      • the relative path name of the routine library, class, or assembly file relative to the function directory.

      The database manager searches for the library, class, or assembly file by name in the function directory unless a fully qualified or relative path name for it is specified in the EXTERNAL clause.

    • Specify DYNAMIC RESULT SETS with a numeric value if your routine is a procedure that returns one or more result sets to the caller.
    • Specify any other clauses that are required to characterize the routine.

What to do next

To call your external routine, see Routine invocation