Functions Wizard (DB2 Z/OS)

From DBArtisan
Jump to: navigation, search

Go Up to IBM DB2 for z/OS Object Wizards

The Functions Wizard 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

  1. Open a creation wizard for a function. For details, see Opening an Object Wizard.
  2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
  3. Click Execute to create the object.

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. 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 do not 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. This entry is 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

Notepad blue icon 2.pngNote: 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.

Retrieved from "https://docwiki.embarcadero.com/DBArtisan/190/e/index.php?title=Functions_Wizard_(DB2_Z/OS)&oldid=7652"