Functions Wizard (DB2 Z/OS)
Go Up to IBM DB2 for z/OS Object Wizards
The Functions Wizrd lets you create a relationship between one set of values and another. You can develop reusable subroutines so you can control, access, and manipulate the data that underlies an object. As you complete the Function Wizard process, a CREATE FUNCTION statement is generated based on the information that you supply.
- Note: To create a user-defined function, you need CREATE ANY privileges or IMPLICIT_SCHEMA authority on the database if the schema does not already exist.
To create a new function using a wizard:
- Open a creation wizard for a function. For details, see Opening an Object Wizard.
- Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
- Properties panel - for details, seeFunctions (DB2 z/OS) - Properties.
- Source panel - (Only available for a Function Type of SOURCED.) for details, see Functions (DB2 z/OS) - Source.
- Parameters panel - for details, see Functions (DB2 z/OS) - Parameters.
- Return Scalar panel - for details, see Functions (DB2 z/OS) - Return Scalar.
- Return Columns panel - (Only available when you choose a Function Type of EXTERNAL TABLE.) for details, see Functions (DB2 z/OS) - Return Columns.
- Body panel - for details, see Functions (DB2 z/OS) - Body.
- DDL View panel - for details, see Previewing the DDL Generated to Create the New Object.
- Finally, use the Execute button to create the object.
Contents
Functions (DB2 z/OS) - Properties
When creating or editing a function, this tab/panel lets you work with the following settings:
Setting | Description |
---|---|
Schema, Name, and Specific Name |
Select the owner of the function, provide a name for the function, and provide the Specific name to be used by some SQL statements and DB2 commands for this function. |
Function Type |
External Scalar: This allows you to extend the function by adding your own or another party’s definition for the function. External Table: Use this to create a function that is written in ASSEMBLE, C, COBOL, or PLI to return a table after it is deployed. Sourced: Here you are creating a function that is based on an existing scalar or table function with an application server. SQL: This type of function returns a single value when the function is invoked if the SQL statement that defines it is valid. |
Language |
If you chose a Function Type of EXTERNAL SCALAR or EXTERNAL TABLE, specify a language of ASSEMBLE, C, COBOL, or PLI. |
Return Type |
Identifies the return type of the function. |
External Name |
Provide the External Name of the function. |
SQL Access Level |
Indicates whether the function can execute SQL statements. CONTAINS SQL: Statements that don’t read or modify SQL can be executed. NO SQL: No SQL statements can be executed. READS SQL: Statements that cannot modify SQL can be executed. |
WLM Environment |
Specify a Workload Management Environment (Required if Language is JAVA/COMPJAVA/REXX, the Procedure contains a LOB parameter, Security is set to ‘USER’ or ‘DEFINER’, or program type is ‘SUB’). |
WLM For Nested |
Self-explanatory |
Functions (DB2 z/OS) - Source
- Note: Only available for a Function Type of SOURCED.
Select the Schema, Name, and Specific Name of the source function.
Functions (DB2 z/OS) - Parameters
For each parameter for this function, use the New button to add a new parameter, provide a name for the parameter, and in the Attributes area, select a Type, and if appropriate, the Precision, Scale, Size, and As Locator options.
Functions (DB2 z/OS) - Return Scalar
Under Return Datatype, select a Type and depending on your choice, provide or select Precision, Scale, Size, and As Locator options.
To make use of a CAST FROM clause, under Cast Datatype set Enabled to True, select a Type, and if appropriate, the Scale, Size, and As Locator options
Functions (DB2 z/OS) - Return Columns
- Note: Only available when you choose a Function Type of EXTERNAL TABLE.
For each column returned by this function, use the New button to add a new parameter, provide a name for the parameter, and in the Attributes area, select a Type, and if appropriate, the Precision, Scale, Size, and As Locator options.
Functions (DB2 z/OS) - Body
Enter the return statement for the function.