Example of creating and using a user-defined scalar function

You can create a user-defined scalar function that gets input from a table and puts the output in a table.

Suppose that your organization needs a user-defined scalar function that calculates the bonus that each employee receives. All employee data, including salaries, commissions, and bonuses, is kept in the employee table, EMP. The input fields for the bonus calculation function are the values of the SALARY and COMM columns. The output from the function goes into the BONUS column. Because this function gets its input from a DB2® table and puts the output in a DB2 table, a convenient way to manipulate the data is through a user-defined function.

The user-defined function's definer and invoker determine that this new user-defined function should have these characteristics:
  • The user-defined function name is CALC_BONUS.
  • The two input fields are of type DECIMAL(9,2).
  • The output field is of type DECIMAL(9,2).
  • The program for the user-defined function is written in COBOL and has a load module name of CBONUS.
Because no built-in function or user-defined function exists on which to build a sourced user-defined function, the function implementer must code an external user-defined function. The implementer performs the following steps:
  • Writes the user-defined function, which is a COBOL program
  • Precompiles, compiles, and links the program
  • Binds a package if the user-defined function contains SQL statements
  • Tests the program thoroughly
  • Grants execute authority on the user-defined function package to the definer
The user-defined function definer executes this CREATE FUNCTION statement to register CALC_BONUS to DB2: 
CREATE FUNCTION CALC_BONUS(DECIMAL(9,2),DECIMAL(9,2))
  RETURNS DECIMAL(9,2)
  EXTERNAL NAME 'CBONUS'
  PARAMETER STYLE SQL
  LANGUAGE COBOL;

The definer then grants execute authority on CALC_BONUS to all invokers.

User-defined function invokers write and prepare application programs that invoke CALC_BONUS. An invoker might write a statement like this, which uses the user-defined function to update the BONUS field in the employee table: 
UPDATE EMP
  SET BONUS = CALC_BONUS(SALARY,COMM);
An invoker can execute this statement either statically or dynamically.