Rational Developer for System z
PL/I for Windows, Version 8.0, Programming Guide

User defined functions sample programs

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.

MyAdd
Adds two integers and returns the result in a third integer.
MyDiv
Divides two integers and returns the result in a third integer.
MyUpper
Changes all lowercase occurrences of a,e,i,o,u to uppercase.
MyCount
Simple implementation of counter function using a scratchpad.
ClobUpper
Changes all lowercase occurrences of a,e,i,o,u in a CLOB to uppercase then writes them out to a file.

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:

UDFMYADD.PLI
Fetches ID and Dept from the STAFF table then adds them together by calling MyAdd UDF. Use the command file bldmyadd to compile and link it.
UDFMYDIV.PLI
Fetches ID and Dept from the STAFF table then divides them by calling MyDiv UDF. Use the command file bldmydiv to compile and link it.
UDFMYUP.PLI
Fetches Name from the STAFF table then calls MyUpper to change the vowels to uppercase. Use the command file bldmyup to compile and link it.
UDFMYCNT.PLI
Fetches ID from the STAFF table, outputs the count of the call, then divides ID by the count. Use the command file bldmycnt to compile and link it.
UDFCLOB.PLI
Fetches the resume for employee '000150' then calls ClobUpper to change the vowels to uppercase. Use the command file bldclobu to compile and link it. After this program is run, look in the file udfclob.txt for the results.

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.

Rational Developer for System z
PL/I for Windows, Version 8.0, Programming Guide