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

RETURNS DECIMAL(11,2)                                        

LANGUAGE SQL                                                 

IS DETERMINISTIC                                             

READS SQL DATA                                               

NO EXTERNAL ACTION                                           

BEGIN                                                        

DECLARE ordertot DECIMAL(11,2);                              

SELECT SUM(QTYORD * PRICE) INTO ordertot FROM CBB2E/ORDDETAIL

WHERE ORDNO = ordnuminp;                                     

RETURN ordertot;                                             

END

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         

0005.01  *------------------------------------------------  

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.04  *                                                  

0007.05 D ONHND           S             11P 2               

0007.06  /free                                              

0007.07   ONHND = UPBAL + ISSUE + AJUST;                    

0007.08   RETURN ONHND;                                     

0007.09  /end-free                                          

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.

Final Note

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.