Creating a user defined function (sourced)

A sourced function is implemented by invoking another function (either built-in, external, or sourced) that is already registered in the database.

Authorities and privileges

To create a user defined function (sourced):

1. Open the Create User Defined Function (Sourced) window.

2. In the Function schema field, specify a schema for the function or click the push button to select from an available list of schemas. A user-defined function resides in the schema in which it was registered. The schema cannot be SYSIBM.

3. Type a Function name (a long SQL identifier) to identify the function. The name is implicitly or explicitly qualified by a schema name. The combination of name, schema name, the number of parameters, and the data type of each parameter (without regard for any length, precision, scale, subtype, or encoding scheme attributes of the data type) must not identify a user-defined function that exists at the current server.

   If the function has more than 30 parameters, the combination of name, schema name, and the number of parameters must not identify a user-defined function that exists at the current server.

   If the function is sourced on an existing function to enable the use of the existing function with a distinct type, the name can be the same name as the existing function. You can use the same name for more than one function if the function signature of each function is unique.

4. Optional: In the Specific name field, type a name (a long SQL identifier) for an external user-defined function. This is the specific name for that user-defined function. DB2 can generate the name.

5. In the Source function fields, identify the source function (either another user-defined function or a built-in function) that will implement the function that you are creating:
   1. Specify the schema name from which to get the function or click on the push button to select from an available list of schemas.

   2. In the Specific name field, type a name (a long SQL identifier) of a function that will implement this function. You can also click on the push button to select from an available list of functions. This is the function to be used as the source function

   When you have selected a specific function name for the source, you see a list of source and input parameters that will be used for the function that you are creating. The number of parameters for this function must match between the source function and the function you are creating, but you can make data type changes that are compatible with the source data types.

6. Click on the Change push button to open the Change Parameter window to modify the parameter data types of the sourced function. The result of your change appears in the Input parameters column of the table. These are now your input parameters.

7. In the Return data type field, click the push button to define an output parameter. This opens the Change Data Type window.

8. Optional: In the Comment field, type any comments you want to include with the function. This adds a COMMENT ON statement which adds comments in the description of the user-defined function in the REMARKS column of the SYSIBM.SYSROUTINES catalog table for the row that describes the function. The maximum number of characters is 254.

9. Click OK to add the sourced function.

Related information

• Creating a User Defined Function (External Scalar)
• Creating a User Defined Function (External Table)