Defining a user-defined function

You can define a user-defined function to perform specific operations by issuing the CREATE FUNCTION statement.

To define a user-defined function:

1. Issue the CREATE FUNCTION statement. For example, the following CREATE FUNCTION statement defines the user-defined function SALARY_CHANGE to DB2®:

   CREATE FUNCTION
     SALARY_CHANGE(
       VARCHAR( 6 )
       DECIMAL( 5,2 ) )
     RETURNS
       DECIMAL( 9,2 )
     SPECIFIC schema.SALCHANGE
     LANGUAGE C
     DETERMINISTIC
     MODIFIES SQL DATA
     EXTERNAL NAME C_SALARY
     PARAMETER STYLE DB2SQL
     RETURNS NULL ON NULL CALL
     NO EXTERNAL ACTION
     NO SCRATCHPAD
     NO FINAL CALL
     ALLOW PARALLEL
     NO COLLID
     ASUTIME LIMIT 1
     STAY RESIDENT NO
     PROGRAM TYPE SUB
     WLM ENVIRONMENT WLMENV
     SECURITY DB2
     NO DBINFO;

   After issuing the CREATE FUNCTION statement, the person who defined the function owns the user-defined function. This person (the definer) can execute the user-defined function package. In this case, the owner of the user-defined function package (the implementer) granted to the definer the EXECUTE privilege on the package that contains the user-defined function.

2. The definer grants the EXECUTE privilege on SALARY_CHANGE to all function invokers.

   GRANT EXECUTE ON FUNCTION SALARY_CHANGE
      TO invoker1, invoker2, invoker3, invoker4