Physical Modeling › DB2 z/OS Property Editors › Define a DB2 z/OS Function

Define a DB2 z/OS Function

You can use the Function editor to create and maintain the user-defined functions in a DB2 physical model.

To define a DB2 function

1. Right-click a Function in the Model Explorer and select Properties.

   The Function Editor opens.

2. Select a function and work with the following options:

   Note: Click New on the toolbar to create a function. Use the Enter filter text text box to filter a large list of functions to locate the one you want to work with.

   Name

   Specifies the name of the function.

   Schema

   Specifies the schema to associate with the function. Select a schema name from the drop-down list.

   Type

   Specifies if the function is at the Model Level or Table Level. Select a type from the drop-down list.

   Function Type

   Specifies the type of function. Select a value from the drop-down list: SQL Scalar, External Scalar, External Table, or Sourced.

   Attach To New

   Specifies if upon creating a new (table-level only) table, this function will automatically be attached to it.

   Generate

   Specifies whether or not to generate DDL for this object during Forward Engineering.

3. Click the Parameters tab and work with the following options:

   Parameter

   Specifies the input parameters of the function. Specify one entry for each parameter that the function will receive.

   Physical Data Type

   Specifies the physical data type of the parameter.

   Character Type

   Specifies whether the data type is for binary data.

   CCSId

   Specifies the coded character set identifier (CCSId) for the Character or Graphic data types. Select from the drop-down list; valid values are ASCII, EBCDIC, and UNICODE.

4. Complete the following fields on the General tab:

   Specific Name

   Specifies a unique name for the instance of the function that is being defined. This name can be used when sourcing on this function, dropping the function, or commenting on the function. However, this name cannot be used to invoke the function.

   Return Options:

   Return Data Type

   Specifies the data type for the value returned by the function. Select a data type from the drop-down list.

   Return Character Type

   Specifies the character type that will be returned. Make a selection from the drop-down list: For Bit Data, For Mixed Data, or For Single Byte Character Set.

   Return Coded Character Set Identifier

   Specifies the coded character set identifier. Select from the drop-down list. Valid values are ASCII, EBCDIC, and UNICODE.

   Return Table Definition

   Specifies the name of the external table, when the type is set to External Table. Select a table name from the drop-down list.

   Return Cast Data Type

   Specifies the data type to which the result returned from the function is casted. This option appears when the type is set to External Scalar.

   Return Cast Character Type

   Specifies the character type of the casted data type returned by the function. This option displays when the type is set to External Scalar.

   Return Cast Coded Character Set Identifier

   Specifies the coded character set identifier. Select from the drop-down list; valid values are ASCII, EBCDIC, and UNICODE.

5. Click the Options tab and work with the following parameters:

   Remain Resident in Memory

   Specifies whether or not the load module for the function is to stay in memory when the function ends. This option is ignored if you have specified JAVA for Language.

   Program Type

   Specifies the type of the function. Select from the drop-down list; valid values are Main Routine and Subroutine.

   Language

   Specifies the language in which the function is written. Select from the drop-down list; valid values are ASSEMBLE, C, COBOL, Java, and PLI.

   CCSId

   Specifies the coded character set identifier. Select from the drop-down list; valid values are ASCII and UNICODE.

   Parameter Varchar Form

   Specifies that the representation of the values of varying length character string-parameters, including, if applicable, the output of the function, for functions that specify LANGUAGE C.

   Is Deterministic

   Specifies whether the function always returns the same results for given argument values. Clear the check box to indicate NOT DETERMINISTIC.

   SQL Data Access

   Specifies the type of SQL statements that can be executed. Select a value from the drop-down list; valid values are Modifies SQL Data, Reads SQL Data, and Contains SQL Data.

   Use Static Dispatch

   Specifies whether or not at function resolution time, DB2 chooses a function based on the static types (declared types) of the parameters of the function.

   Null Input

   Specifies that the function is called regardless of whether any of its arguments are null. It can return a null value or a non-null value. Select a value from the drop-down list. Valid values are Called or Returns Null.

   Special Register Usage

   Specifies how special registers are set on entry to the routine. Select a value from the drop-down list: Inherit (Special Registers) or Default (Special Registers).

   Parameter Style

   Specifies the conventions used for passing parameters to and returning the value from functions. Select a value from the drop-down list. Valid values are DB2 General, Java, SQL and DB2 SQL.

   Is Fenced

   Specifies that the function runs in an external address space to prevent user programs from corrupting DB2 storage.

   Make Final Call

   Specifies whether a final call is to be made to an external function. This option enables the external function to free any system resources it is using.

   Is Parallel Processing Allowed

   Specifies that parallel processing is allowed. Clear the check box to indicate NO.

   Use DB Info

   Specifies whether or not status information known by DB2 is passed to the stored procedure when it is invoked. Clear the check box to indicate NO DBINFO.

   Cardinality Value

   Specifies an estimate of the expected number of rows that the function returns. Enter an integer in the text box.

   Source Function

   Specifies that the new function is being defined as a source function.

   Source Specific Function Name

   Specifies a unique name for the instance of the function that is being defined.

   Source Specific Schema

   Specifies the schema to associate with the function. Select a schema name from the drop-down list.

   CPU Service Time Limit

   Specifies the maximum amount of processor time, in CPU service units, that a single invocation of the function can run.

   Runtime Options

   Specifies the Language Environment runtime options that you want to use for the function. Type a character string in this field.

   Failure Stop Type

   Specifies whether or not the function should be stopped executing after a certain type or number of failures. Select from the drop-down list; valid values are System Default, Specific Number, and Continue.

   Failure Limit

   Specifies the number of failures after which the routine should be stopped executing.

   Static Register Usage

   Specifies how special registers are set upon entry to the routine. Select from the drop-down list; valid values are Inherit and Default.

   Use Static Dispatch

   Specifies whether or not you want to use the static types of function parameters to resolve the function.

6. When you work with the External function type, you must work with the following options on the External Name tab:

   External Name

   Identifies the user-written code that implements the stored procedure.

   File

   Specifies the file name of the file containing the function. Select from the drop-down list or create a file using the DBE for LUW File Editor.

   Class Name

   Specifies the class identifier, which the database manager invokes to execute the user-defined function being created.

   Method Name

   Specifies the method identifier, which the database manager invokes to execute the user-defined function being created.

7. Click the Environment and Package tab and work with the following options:

   Use Scratch Pad

   Indicates that when the function is invoked for the first time, DB2 allocates memory for a scratchpad. Clear the check box to indicate NO SCRATCHPAD.

   Scratch Pad Length

   When using the Scratch Pad option, indicates the SCRATCHPAD length.

   Workload Manager Environment

   Specifies the workload manager (WLM) environment. Enter the WLM name in the text box.

   Use Calling Procedure Environment

   Specifies whether or not to use the environment of the procedure that calls this function.

   External Security Type

   Specifies how the stored procedure interacts with an external security product. Select from the drop-down list; valid values are DB2, User, and Definer.

   Is External Action Allowed

   Specifies whether or not the function can change the state of an object that the database manager does not manage.

   Use Package Collection

   Indicates to use the package collection id indicated in the next field.

   Package Collection Id

   Identifies the package collection to be used when the stored procedure is executed. Enter the collection id in the text box.

   Use Package Path

   Indicates to use the package path entered in the following field.

   Package Path

   Indicates the package path.

8. (Optional) Click the Code tab to review or assign code (including macros) to your function.
9. (Optional) Click the Expanded tab to display fully expanded code that may be shown as code fragments or partial routines on other tabs.
10. (Optional) Click the Permission tab and grant permissions to users or roles on the selected function.
11. (Optional) Click the Comment tab and enter any comments you want associated with the function.
12. (Optional) Click the Object Creation Order tab and change the order in which you want the functions to be created.
13. (Optional) Click the Where Used tab to view where a function is used in the model.
14. (Optional) Click the UDP tab to work with user-defined properties for the function.
15. (Optional) Click the Notes tab to view history information and view or edit user notes.
16. Click Close.

    The DB2 for z/OS Function Editor closes.