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 database server must support 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.
a list of restrictions that are associated with external routines
About this task
- 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.
- 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 SQL data types.
- 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:
- Build your code into a library or class file.
- 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
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.
In a Big SQL deployment, the database function directory is typically not located on a shared file system. Therefore, you must manually copy the assembly or library to the local database function directory on each database 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. In a Big SQL deployment, it is recommended that the Java implementation of LANGUAGE JAVA routines is packaged in a JAR file which is installed by the SQLJ.INSTALL_JAR procedure.
- Execute either dynamically or statically the appropriate
SQL language CREATE statement for the routine type: CREATE PROCEDURE
or CREATE FUNCTION.
- Specify the
LANGUAGEclause with the appropriate value for the chosen API or programming language. Examples include: C, JAVA.
- Specify the
PARAMETER STYLEclause with the name of the supported parameter style that was implemented in the routine code.
- Specify the
EXTERNALclause 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.
- Specify the
What to do next
To call your external routine, see Routine invocation