User Defined Functions

Restriction: This topic applies to Windows environments (local development) only.

A program containing a reference to a User Defined Function (UDF) causes a separate module to be invoked; it contains user-supplied code which returns an appropriate value or values. The UDF code itself does not contain any SQL.

Running a program containing embedded SQL statements causes DB2 to be invoked and this in turn may invoke the UDF module. The declaration of the UDF should specify the language this module is written in. DB2 currently allows this to be C only, although on some platforms it is possible to write the module in COBOL. The following section demonstrates by use of example how this may be achieved. More complete descriptions of User Defined Functions and parameter descriptions are provided in the DB2 documentation.

User Defined Functions written in COBOL are not currently supported on UNIX.

Note: In a client/server configuration, the UDF module is invoked on the server and these restrictions apply to the server only - any client can access UDFs if the server is suitable.

The entry points in the UDF should be defined using C calling conventions. The following sample code segments show the use and definition of a simple UDF to calculate an exponent:

Program 1 declares the function to DB2. This program must be compiled and executed before program 2 can be compiled.

exec sql
   create function mfexp(integer, integer)
      returns integer
      external name 'db2v2fun!mfexp'
      not variant
      no sql
      parameter style db2sql
      language cobol
      no external action

Note the LANGUAGE COBOL clause. This is provided by Micro Focus COBOL as an extension to the DB2 syntax. It is equivalent to LANGUAGE C and, regardless of which is used, the called module should conform to the C calling convention. The EXTERNAL NAME clause specifies, in this case, that the called module is called db2v2fun.dll and the entry point within this is mfexp.

Program 2 uses the UDF:

     move 2 to hv-integer
     move 3 to hv-integer-2
     exec sql
         values (mfexp(:hv-integer, :hv-integer-2))
          into :hv-integer-3

Program 3 is a pure COBOL program containing the UDF itself.

$set case
     call-convention 0 is cc.
 linkage section.
 01  a pic s9(9) comp-5.
 01  b pic s9(9) comp-5.
 01  c pic s9(9) comp-5.
 01  an pic s9(4) comp-5.
 01  bn pic s9(4) comp-5.
 01  cn pic s9(4) comp-5.
 01  udf-sqlstate pic x(6).
 01  udf-fname pic x(28).
 01  udf-fspecname pic x(19).
 01  udf-msgtext pic x(71).
 procedure division cc.
 entry "mfexp" cc
     using a b c an bn cn
     if an not = 0 or bn not = 0
         move -1 to cn
         compute c = a ** b
         move 0 to cn

This module should be compiled to create a dynamically loadable executable (dll) and placed somewhere where the operating system can locate it (on the PATH).

Note: Entry-point names are case sensitive on all systems. Care should be exercised in matching case names, and the CASE Compiler directive should be specified (as per the $SET statement in the example program above).