Functions Wizard (DB2 LUW)

From DBArtisan
Jump to: navigation, search

Go Up to IBM DB2 for Linux, Unix, and Windows 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. The Function Wizard lets you create a function without knowing the underlying commands.

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. Finally, use the Execute button to create the object.

Functions (DB2 LUW) - Properties

When creating or editing a foreign key, this tab/panel lets you work with the following settings:

Setting Description

Schema, Name, and Specific Name

Let you 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

Select the type of function: External Scalar - written in a programming language and returns a scalar value. External Table - written in a programming language and returns a complete table. OLEDB - accesses OLE DB data in user-defined OLE DB external tables. Sourced - another function is invoked to implement the function you are creating. SQL Language - written in SQL and returns a table, scalar value, or single row. Template: - this is a partial function and can only be invoked from a federated datasource. For more information, see About function types.

Language

If you chose a Function Type of EXTERNAL SCALAR or EXTERNAL TABLE, specify a language of C, JAVA, or OLE. For more information, see About function types.

Return Type

For a Function Type of SQL, select ROW, TABLE, or SCALAR. For other Function Type choices, this indicates the default return type for that choice.

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.

Functions (DB2 LUW) - Advanced

This tab is only available after clicking the Advanced button on the Function wizard’s Properties panel. It lets you work with the Threadsafe, Fenced, Scratchpad, Scratchpad Length, Allow Parallel, Final Call, Parameter Style, Inherit Special Registers, DBINFO, Deterministic, External Action, Called on Null Input, and Parameter CCSID properties.


Functions (DB2 LUW) - Source

Note: This panel is only available for a Function Type of SOURCED.

Select the Schema, Name, and Specific Name of the source function. Function Parameters and Return Type for the selected function are displayed. For more information, see About function types.

Functions (DB2 LUW) - Parameters

Note: This panel is only available for a Function Type of SQL, SOURCED, or TEMPLATE.

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, and Size options.

Functions (DB2 LUW) - 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 LUW) - Return Columns

Note: This panel is 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 LUW) - Body

Enter the return statement for the function.

About function types

External Scalar/Table/OLE DB Function

External scalar user-defined functions are implemented in an external programming language. The functions are executed on the server and can read SQL data but cannot make changes to the data. These functions are often used to extend the set of built-in functions for DB2, perform logic inside a SQL query that SQL can’t perform on its own, and, encapsulate a scalar query that is often used as a subquery in SQL statements, for example, if given an ingredient, search a table for a recipe that uses that ingredient.

When specifying the Specific Name for one of these function types:

  • If you are using C language, specify the full library path and the function name, otherwise IBM DB2 Database Manager assumes the function is under the IBM DB2 library.
  • If you are using Java script, specify the Class ID and the function name, otherwise IBM DB2 Database Manager assumes the function is under the IBM DB2 library.
  • If you are using OLE language, specify the full library path and the function name, otherwise IBM DB2 Database Manager assumes the function is under the IBM DB2 library.

Sourced Functions

When you create a sourced function, the new function you are creating will be implemented by a preexisting (source) function that’s known to the database manager. The source function can be a built-in function or a previously created user-defined scalar function.

When you select the appropriate schema, you are really specifying an implicit schema privilege. In other words, you’re selecting a schema/function that belongs to a user with DBAdmin privileges. If you want to use a built-in function, you must specify the function’s specific name.

SQL Language Function

The function you are creating is written in SQL. A table, scalar value, or single row is returned.

Template Function

A template function is better thought of as a template for a function. It’s a partial function that has no executable code. You create a function template for mapping it to a datasource function. When the mapping is created, you can specify the function template be used in queries submitted to the federated server. When the query is processed, the federated server invokes datasource function where the template is mapped and returns the appropriate values.

Retrieved from "https://docwiki.embarcadero.com/DBArtisan/171/e/index.php?title=Functions_Wizard_(DB2_LUW)&oldid=3034"