Sample User-Defined Function (UDF) Code
Every database has its own rules for languages which may be used to write User Defined Functions (UDFs). Here are two examples, one written in SQL and another in RPG, showing how to create functions for DB2/400.
Example 1: Writing a UDF with SQL
We are going to build a function that sums the product of Price and Ordered Quantity for all the detail records of a given order. This UDF can then be used in a calculated fields or, even more powerfully, in logical fields across m-Power.
CREATE FUNCTION CBB2E/ORDERTOT1( ordnuminp DECIMAL(6,0) )
READS SQL DATA
NO EXTERNAL ACTION
DECLARE ordertot DECIMAL(11,2);
SELECT SUM(QTYORD * PRICE) INTO ordertot FROM CBB2E/ORDDETAIL
WHERE ORDNO = ordnuminp;
The create function statement first names the UDF and puts it somewhere. It then tells the database that we will be passing in one parameter which is numeric 6,0. The function returns an 11,2 field. The SQL statement is encapsulated between a BEGIN and an END statement.
When the create function statement is run, SQL will note any syntax errors. If there are no errors, the function ORDERTOT1 will be created in library CBB2E.
The user-defined function can now be registered through the Manage User-Defined Functions screen within the Data Dictionary tab in m-Power.
Example 2: Writing a UDF with RPG
We are going to build a function that sums several data fields to determine onhand inventory. This UDF is written in RPG.
0001.00 H NOMAIN
0001.01 D* Prototype for procedure: ONHAND
0002.00 D ONHAND PR 11P 2
0003.00 D UPBAL 7P 0 CONST
0004.00 D ISSUE 7P 0 CONST
0005.00 D AJUST 7P 0 CONST
0006.00 P ONHAND B EXPORT
0006.01 D ONHAND PI 11P 2
0007.00 D UPBAL 7P 0 CONST
0007.01 D ISSUE 7P 0 CONST
0007.02 D AJUST 7P 0 CONST
0007.05 D ONHND S 11P 2
0007.07 ONHND = UPBAL + ISSUE + AJUST;
0007.08 RETURN ONHND;
0008.00 P ONHAND E
Use option 15 (in Work with Members Using PDM) to compile this source into a RPGLE module. Use CRTSRVPGM SRVPGM(LIBRARYNAME/ONHAND) EXPORT(*ALL) to compile the module into a service program.
The user-defined function can now be registered and created through the Manage User-Defined Functions screen within the Data Dictionary tab in m-Power.
Remember, different databases let you write your own user-defined functions in a variety of languages. You are not restricted to SQL or RPG. This means you can write code in the language you know and use it through m-Power.
For information on how to use UDFs within m-Power, read How to Create and Register User-Defined Functions.