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