Function, Designer

Function Identification

In this section, you define the function identification properties.

Function name

Type a name for the new function. If you do not enter a value, the designer generates a default value. The name cannot exceed 128 characters.

Schema name

By default, the value for this field is the name of the schema that is obtained from the current data connection. You can change the value of this field to another schema, or leave it blank and the designer uses the default value. Use quotation marks for delimited names.

Owner

Specify the user ID of the person to own the function.

Specific name

Type a unique name for the new function. The server uses a unique name to identify the function. This field automatically fills in as you type in the Function name field.

If you do not specify a unique name, the server generates one for you.

If you do not specify a name in this field, the server does not generate one for you.

Comment

Type a comment for the new function. Comments for the function are appended after the CREATE FUNCTION declaration in the generated function.

Output type

(DB2^® for Linux^®, UNIX^®, and Windows^®) Select the type of output that you want the function to return. A scalar function returns scalar result sets. A table function returns columns.

(DB2 for z/OS^®, DB2 for i) Shows the value Scalar, which is the only allowable type of output for functions on these data servers.

Comment

Type your comment, such as a description of this function. The comments are applied to the DOCUMENT clause. Anyone with access to the database can query the sysprocbody system catalog table to obtain a description of one or all of the UDRs that are stored in the database.

Routine modifier

Specifies the routine modifier to apply to this UDR. Routine modifiers tell the database server about attributes of the UDR. Select one of the following choices:

• VARIANT: Can return different results when invoked with the same arguments.
• NOT VARIANT: Might return cached results.

REFERENCING

Type the REFERENCING clause in this field, or click , and declare the information for the clause in the Declare REFERENCING Clause window.

Listing file name

Specify a full directory path and file name where compile time warnings are sent. After you compile a UDR, the listing file contains warning messages that were generated. The file name that you specify is sent with the WITH LISTING IN clause.

Function Parameters

In this section, you define the parameters for the function.

Parameters

Specify the parameters in the order that they appear in the generated code. You can use the arrow buttons to move a selected parameter up or down in the list.

Name

Type a name for the parameter. A parameter requires a unique identifier. No duplicate parameter names are allowed in a function. Maximum length of a parameter name is 18.

Mode

Select the mode for the parameter. The default value for the mode is IN. You can select IN (indicating that the parameter is an input only parameter), OUT (indicating that the parameter is an output only parameter), or INOUT (indicating that the parameter is both an input and output parameter).

Type

Select the data type for the parameter. The default data type is VARCHAR with a length of 256 characters.
If you selected a data type of DECIMAL, type the scale value for the parameter in Scale property in the Parameter Properties list.

(DB2 Version 9.7 for Linux, UNIX, and Windows, or later; DB2 for z/OS)
Optional: If you selected a data type of TIMESTAMP or TIMESTAMP WITH TIMEZONE [DB2 for z/OS Version 10 new function mode (NFM) only], type the precision value for the timestamp in the Precision property in the Parameter Properties list. The default value is 6 microseconds.

Length

If you selected a data type of CHARACTER, VARCHAR, BLOB, CLOB, DBCLOB, GRAPHIC, or VARGRAPHIC, type the length (byte count) of the parameter.
If you selected a data type of DECIMAL or FLOAT, type the precision of the parameter.

Opens the Import window so that you can import columns from one or more tables into the function as parameters.

Parameter properties

Set the property values for the parameter that is selected in the Parameters list. An unavailable property is a limitation of the database that you are using.

You can specify the data type of a parameter in three different ways:

• Specify the SQL data type directly
• Specify a LIKE clause
• Use a REFERENCES clause

Use the Data Type Style property to indicate how you want to specify the data type, and then specify values for the corresponding properties. The properties that are available depend on the value that you select for this property.

Select a property to see its description in the box under the properties list. If the description exceeds the size of the box, hover the mouse pointer over the last line of text in the box. After a brief pause, the full description is shown in a ScreenTip.

SQL Body

In this section, enter an SQL or SPL statement or expression in the editor.

(DB2 for Linux, UNIX, and Windows)

The designer allows only SQL SELECT statements or expressions (scalar functions only), and it checks for correct syntax. You can include multiple SELECT statements in your function definition.

To use the Visual Studio Query Builder to create a SELECT statement, right-click in the editor, and then select Query Builder on the shortcut menu. When you close the Query Builder, the SQL code in the Query Builder replaces all of the existing code in the editor.

(DB2 for z/OS, DB2 for i) The designer allows only expressions.

Additional information is available in your DB2 documentation. You can also find information in DB2 SQL documentation that is available through The SQL Reference for Cross-Platform Development Web page on developerWorks.

Uncomment and complete one or more of the following SPL clauses that is shown in the editor:

DEFINE

Use to declare local variables that an SPL routine uses, or to declare global variables that several SPL routines can share.

ON EXCEPTION

Use to specify the actions to be taken for any error, or for a list or one or more specified errors, during execution of a statement block.

EXECUTE FUNCTION

Use to run a user-defined function.

EXECUTE PROCEDURE

Use to invoke a user-defined procedure.

You can include any of the following SPL statements in the statement block of your SPL function:

CALL CONTINUE EXIT

FOR FOREACH IF

LET RAISE EXCEPTION RETURN

SYSTEM TRACE WHILE

Function Return Type

In this section, you define the data types or columns that the function returns, based on the function type and the SQL or SPL that you specify in the SQL body of the function.

You can explicitly define the return types, or you can discover the return types, based on the SQL statement that you specify in the SQL body.

For scalar functions, the designer recommends a return data type, and it lists any compatible data types for you to select. For table functions, the designer lists the columns and suggests return data types for each column.

You can specify the data type for a return type directly, or you can specify the data type by using the REFERENCES clause. You can also return function parameters. Naming return parameters is optional. However, if any return value of the function has a name, all of the return values must have names.

Returned columns

Specify the column or columns that are returned by the function in the order that they appear in the generated code. You can specify only one function return type for the following functions:

• DB2 for Linux, UNIX, and Windows scalar functions
• DB2 for z/OS functions
• DB2 for i functions

When you specify more than one function return type, you can use the arrow buttons to move a selected column up or down in the list.

Name  (DB2 for Linux, UNIX, and Windows table function)

Specify a new value for the name of the selected column.

Type

Shows the recommended data type for the column. You can change this to another data type that is compatible for the column.

Length

Shows the default length for the data type. You can change this value.

Determines the result set columns that are returned, based on the SQL statement that you specified in the SQL Body section

(DB2 for Linux, UNIX, and Windows table function)

Opens the Import window so that you can import columns from one or more tables into the function as return types.

Return type

Set the property values for the return type that is selected in the Returned columns list. An unavailable property is a limitation of the database that you are using.

Select a property to see its description in the box under the properties list. If the description exceeds the size of the box, hover the mouse pointer over the last line of text in the box. After a brief pause, the full description is shown in a ScreenTip.