You must install the following items to access the User Defined Function (UDF) sample programs:
Several PL/I programs have been included to show how to code and use UDFs. Here is a short description of how to use them.
The file UDFDLL.PLI contains five sample UDFs. While these are simple in nature, they show basic concepts of UDFs.
Use the command file bldudfdll to compile and link it into the udfdll library.
After the udfdll library has been compiled and linked, copy it to the user defined function directory for your database instance.
Before the functions can be used they must be defined to DB2. This is done using the CREATE FUNCTION command. The sample program, addudf.pli, has been provided to perform the CREATE FUNCTION calls for each UDF. CREATE FUNCTION calls would look something like the following:
CREATE FUNCTION MyAdd ( INT, INT ) RETURNS INT NO SQL
LANGUAGE C FENCED VARIANT NO EXTERNAL ACTION PARAMETER
STYLE DB2SQL EXTERNAL NAME 'udfdll!MyAdd'
CREATE FUNCTION MyDiv ( INT, INT ) RETURNS INT NO SQL
LANGUAGE C FENCED VARIANT NO EXTERNAL ACTION PARAMETER
STYLE DB2SQL EXTERNAL NAME 'udfdll!MyDiv'
CREATE FUNCTION MyUpper ( VARCHAR(61) ) RETURNS VARCHAR(61) NO SQL
LANGUAGE C FENCED VARIANT NO EXTERNAL ACTION PARAMETER
STYLE DB2SQL EXTERNAL NAME 'udfdll!MyUpper'
CREATE FUNCTION MyCount ( ) RETURNS INT NO SQL
LANGUAGE C FENCED VARIANT NO EXTERNAL ACTION PARAMETER
STYLE DB2SQL EXTERNAL NAME 'udfdll!MyCount'
SCRATCHPAD
CREATE FUNCTION ClobUpper ( CLOB(5K) ) RETURNS CLOB(5K) NO SQL
LANGUAGE C FENCED VARIANT NO EXTERNAL ACTION PARAMETER
STYLE DB2SQL EXTERNAL NAME 'udfdll!ClobUpper'
These are just sample CREATE FUNCTION commands. Consult your DB2 manuals for more information or refinement.
Use the command file bldaddudf to compile and link the addudf.pli program. After it is compiled and linked, run it to define the user defined functions to your database.
Several sample PL/I programs are provided that call the user defined functions you have just created and added to the database:
Once these sample programs are compiled, linked, and the UDFs defined to DB2, the PL/I programs can be run from the command line.
These UDFs may also be called from the DB2 Command Line just line any other builtin DB2 function. For further information on how to customize and get the most out of your UDFs, please refer to your DB2 manuals.