Deploying SQL User Defined Functions

Prerequisites

  • The source file must contain a single CREATE FUNCTION statement. If multiple are present, only the first is deployed.

To deploy a user-defined functions (UDF), do these steps:

  1. Open the UDF file in the SQL editor. The file must have a .udfsql extension and include a valid CREATE FUNCTION statement.
  2. Right-click anywhere in the SQL editor and select Db2 for z/OS > Deploy Routine or use the Deploy Routine toolbar button. The Deploy Routine wizard opens.
  3. Specify deployment and routine options on the wizard pages, and then click Deploy. For descriptions of the options you can specify in this wizard, see the list at the end of this procedure.
  4. The results of the deploy operation are shown in the Execution Status dialog, where you can see any warnings or errors and link to more information about SQL codes and status.
Deployment options
Option Description
Db2 Connection Select a Db2 connection where the UDF should be deployed. The default is the active connection for the .udfsql file, if one is defined.
Target Schema Set the schema for unqualified user-defined functions. The wizard suggests an initial value that is based on the associated Db2 connection. If you leave this field empty, the JDBC property currentSchema is used. If the JDBC currentSchema property is not set, the JDBC connection username is used. If the user-defined function content declares the target schema, it appears in this field and cannot be changed.
Build Owner Specify the owner of the user-defined function. The wizard suggests an initial value that is based on the associated Db2 connection. If you leave this field empty, the JDBC property currentSQLID is used. If currentSQLID is not set, the JDBC connection username is used.
Default Path Specify one or more schemas for resolving an unqualified data type, function, or procedure that is referenced by the function being deployed. Separate multiple schemas with commas.
Example: "ADMF002","ADMF003","ADMF004".
Duplicate Handling Use the Duplicate Handling option to specify the behavior of the deployment if the UDF already exists:
  • The Alter Duplicates option modifies the CREATE FUNCTION DDL to ALTER FUNCTION if the UDF already exists. You can optionally choose not to activate the UDF upon deployment; otherwise, it’s activated by default. If VERSION is not specified in the DDL, the Alter Duplicates option is not available.
  • The Drop Duplicates option calls DROP FUNCTION before running the CREATE FUNCTION if the UDF already exists, then creates the UDF, and activates it upon deployment. By default, Db2 activates the first version of a UDF.
  • The Treat Duplicate Deployments as Errors option returns an error if the UDF already exists.
Activate the Deployed Version Select this check box to activate the function upon deployment. By default, Db2 activates the first version of a function. This checkbox is enabled only when the Duplicate Handling option is set to Alter Duplicates.
Routine options
Option description
ASU Time Limit Specify the number of CPU seconds permitted for each SQL statement. The default value of 0 means no time limit. If the source file declares an ASU time limit, it is used as the default and this field is disabled.
Additional Routine Options Specify any additional options that you want to include. Separate each option with a semicolon. Example: QUALIFIER ADMF002; ISOLATION LEVEL RS.

Mapping remote files

IBM® Developer for z/OS® defines the SQL editor as the default editor for files with the extension .udfsql. Files or partitioned data sets that define SQL UDFs must have this file extension.

The product provides a z/OS data set mapping definition called **UDFSQL, that maps members of partitioned data sets with the UDFSQL low-level qualifier to workstation files with the .udfsql extension. This mapping ensures that the default editor association is recognized for data set members.

To ensure that the file mapping functions correctly, verify that the **UDFSQL z/OS file system mapping precedes the **SQL mapping in the z/OS File System Mapping view. To change the order of the **SQL and **UDFSQL mappings, right-click the **UDFSQL mapping and select Raise Priority.

z/OS File System Mapping views