Creating Models › Target Servers for Physical Models › Database Property Editors › DB2 LUW Property Editors › Define a DB2 LUW Function

Define a DB2 LUW 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, SQL Table, SQL Row, External Scalar, External Table, or Sourced.

   Generate As Pre-Script

   Specifies the location of the function with respect to the table definition in the Forward Engineering script. Select from the drop-down list; valid values are Post-Creation and Pre-Creation.

   For example, if you select Post-Creation, the function appears after the table definition.

   Use Replace Syntax

   Specifies whether or not to use the CREATE or REPLACE command instead of the CREATE command during Forward Engineering.

   Note: This field applies to DB2 for LUW version 9.7 only.

   Attach To New

   Specifies whether or not you want to attach this function automatically to a new table.

   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 receives.

   Physical Data Type

   Specifies the physical data type of the parameter.

   Character Data Type

   Specifies whether the data type is for binary data.

   Anchored to Type

   Specifies another object that is used to define a parameter data type.

   Global Variable

   Specifies the name of the global variable that is anchored to the selected parameter. This field appears when you select the value for Anchored to Type as Variable or Cursor Variable.

   Abstract Entity

   Specifies the abstract entity for a column. This field appears when you select the value for Anchored to Type as Column or Table, or View Row.

   Column

   Specifies the name of the column anchored to the selected parameter. This field appears when you select the value for Anchored to Type as Column.

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 is returned. Select from the drop-down list; valid values are For Bit Data.

   Return Data Type Anchored To Type

   Specifies the data type to which the return data type is anchored to.

   Global Variable

   Specifies the name of the global variable that is anchored to the selected parameter. This field appears when you select the value for Return Data Type Anchored to Type as Variable or Cursor Variable.

   Abstract Entity

   Specifies the abstract entity for a column. This field appears when you select the value for Return Data Type Anchored to Type as Column or Table Row.

   Column

   Specifies the name of the column anchored to the selected parameter. This field appears when you select the value for Return Data Type Anchored to Type as Column.

   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.

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

   Language

   Specifies the language in which the function is written. Select from the drop-down list. Valid values are: C, Java, OLE, OLE DB, CLR, and SQL.

   Coded Character Set Identifier

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

   Is Deterministic

   Specifies whether the function always returns the same results for given argument values.

   Is External Action Allowed

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

   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. The function can return a null value or a non-null value. Select a value from the drop-down list; valid values are Called and 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.

   Is Lock Allowed with Isolation

   Specifies whether or not a lock request can be associated with the isolation-clause of the statement when the function inherits the isolation level of the statement that invokes the function.

   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.

   Is Thread Safe

   Specifies whether the function is considered safe to run in the same process as other routines.

   Use Scratch Pad

   Indicates that when the function is invoked for the first time, DB2 allocates memory for a scratchpad.

   Scratch Pad Length

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

   Make Final Call

   Specifies whether a final call is 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.

   Use DB Info

   Specifies whether or not status information known by DB2 is passed to the stored procedure when it is invoked.

   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 the name of the function or function template.

   Source Specific Function Name

   Specifies the specific name of the sourced function.

   Source Specific Schema

   Specifies the schema for the sourced function. Select a schema name from the drop-down list.

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

   External Name

   Identifies the user-written code that implements the function.

   File

   Specifies the file name of the file containing the function. Select from the drop-down list or create a file using the DB2 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 Predicate tab and work with the following options:

   Predicate Context Operator

   Specifies the operator used compare the use of the function in the predicate. The operator can be ″=″, ″<″, ″>″, ″>=″, ″<=″, or ″<>″.

   Predicate Specification Type

   Specifies whether the function is being compared with a constant or an expression.

   Predicate Context Expression

   Specifies the expression name, which can be used as a search function argument.

   Predicate Data Filter

   Specifies an external function or a case expression, which can be used for additional filtering of the result table.

   Predicate Use Index Extension Exact Lookup

   Indicates that the index lookup is exact in terms of the predicate evaluation. Using EXACT means that neither the original user-defined predicate function nor the filter is applied after the index lookup.

   Predicate Index Extension

   Specifies the index extension that you want to use with the predicate. Select from the drop-down list or create an extension using the DB2 for LUW Index Extension Editor.

8. Click the Predicate Exploitation Rules tab and work with the following options:

   Name

   Specifies a set of rules in terms of the search method of an index extension that can be used to exploit the index.

   Method Name

   Specifies which search method to use from those defined in the index extension. You can select from the list or create a method using the DB2 for LUW Index Extension Exploitation Rule Editor.

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

    The DB2 for LUW Function Editor closes.