User Defined Functions

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.

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
      fenced
      external name 'db2v2fun!mfexp'
      not variant
      no sql
      parameter style db2sql
      language cobol
      no external action
end-exec

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
     end-exec

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

$set case
 special-names.
     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.
    goback
    .
 entry "mfexp" cc
     using a b c an bn cn
           udf-sqlstate
           udf-fname
           udf-fspecname
           udf-msgtext.
     if an not = 0 or bn not = 0
         move -1 to cn
     else
         compute c = a ** b
         move 0 to cn
     end-if 
     goback
     .

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).