Online Documentation for SQL Manager for DB2

Creating/editing function


Use the Edit tab of Function Editor to create/edit a function and specify its definition.

 

Function Editor - Editing function definition

 

 

Function type

Select the type of the function being defined: EXTERNAL, SQL, OLEDB or SOURCE:

  • An EXTERNAL function is defined to the database with a reference to a load module that is executed when the function is invoked.
  • The definition of an SQL function includes a RETURN statement.
  • The OLEDB type is used to register a user-defined OLE DB external table function to access data from an OLE DB provider.
  • A SOURCED function is defined to the database with a reference to a built-in function or another user-defined function.

 

Returns

This option identifies the output of the function. Possible values are: SCALAR, TABLE.

 

Function schema

Use this drop-down list to select the schema for the function.

 

Name

Enter a name for the function being defined. It should be an unqualified name that designates the function.

 

Specific name

Provide a unique name for the instance of the function that is being defined. This specific name can be used when sourcing on this

function, dropping the function, or commenting on the function, but it cannot be used to invoke the function.

 

External name (for external functions)

Use this field to identify the name of the written code that implements the function.

 

Source function (for source functions)

Define the implemented function for the source function.

 

Environment options

 

Language

Use this drop-down list to specify the language interface convention to which the function body is written.

 

SQL data

Use the drop-down list to indicate what type of SQL statements can be executed. Possible values are: READS SQL DATA, CONTAINS SQL.

 

Parameter style

Use the drop-down list to specify the conventions used for passing parameters to and returning the value from functions. Possible values are: SQL, DB2GENERAL, JAVA.

 

Cardinality

This option provides an estimate of the expected number of rows to be returned by the function for optimization purposes. Valid values for integer range from 0 to 2 147 483 647 inclusive.

 

Parameter CCSID

Specifies the encoding scheme to use for all string data passed into and out of the procedure. If the Parameter CCSID clause is not specified, the default is Unicode.

 

CheckBox Deterministic

This option specifies whether the function always returns the same results for given argument values (DETERMINISTIC) or whether the function depends on some state values that affect the results (NOT DETERMINISTIC).

 

CheckBox Fenced

This option specifies whether the function is considered "safe" to run in the database manager operating environment's process or address

space (NOT FENCED), or not (FENCED).

 

CheckBox Threadsafe

Check this option to specify that the thread-safe mode is enabled for this function.

 

CheckBox DB information

This option specifies whether certain specific information known by DB2 will be passed to the function as an additional invocation-time argument (DBINFO), or not (NO DBINFO).

 

CheckBox Scratchpad

This option may be used to specify whether a scratchpad is to be provided for an external function. A scratchpad enables a user-defined function save its state from one invocation to the next. The Scratchpad option tells DB2 to allocate and maintain a scratchpad for a routine. The default size for a scratchpad is 100 bytes, but you can determine the size (in bytes) for a scratchpad using the spinner controls.

 

CheckBox Returns NULL

This option may be used to avoid a call to the external function if any of the non-subject arguments is NULL.

 

CheckBox Final call

This option specifies whether a final call is to be made to an external function. The purpose of such a final call is to enable the function to free any system resources it has acquired.

 

CheckBox Allow parallel

This option specifies whether, for a single reference to the function, the invocation of the function can be parallelized.

 

CheckBox External action

This option specifies whether or not the function takes some action that changes the state of an object not managed by the database manager.

 

CheckBox Inherit special registers

This option specifies that updatable special registers in the function will inherit their initial values from the environment of the invoking statement.

 

External part name (for OLEDB functions, External functions)

For external functions this group of options depends on the Language chosen:

  • C

 

Function Editor - Editing function definition - C External name parts

 

Define the library name containing the function. On Windows operating systems, the database manager will look for the function in a directory path that is specified by the LIBPATH or PATH environment variable.

Or you can define the full path name of the file containing the function. On Windows operating systems, for example, 'd:\mylib\myfunc.dll' would cause the database manager to load the dynamic link library, myfunc.dll, from the d:\mylib directory. If an absolute path ID is being used to identify the routine body, be sure to append the *.dll extension.

 

Function ID (for External functions)

Define the entry point name of the function to be invoked.

 

  • Java

 

Function Editor - Editing function definition - Java External name parts

 

Jar ID

Define the jar identifier given to the jar collection when it was installed in the database. It can be either a simple identifier, or a schema qualified identifier. For example, 'myJar' and 'mySchema.myJar'.

 

Class ID

Specify the class identifier of the Java object. If the class is part of a package, the class identifier part must include the complete package prefix, for example, 'myPacks.UserFuncs'. On Windows operating systems, the Java virtual machine will look in directory '...\myPacks\UserFuncs\'.

 

Method ID

Specify the method name of the Java object to be invoked.

 

  • OLE

 

Function Editor - Editing function definition - Ole External name parts

 

Programmatic ID or CLSID

Define the programmatic identifier of the OLE object. It is not interpreted by the database manager but only forwarded to the OLE APIs at run time. The specified OLE object must be creatable and support late binding.

Or define the CLSID - the class identifier of the OLE object to create. It can be used as an alternative for specifying a Programmatic ID in the case that an OLE object is not registered with a Programmatic ID. The CLSID has the form: {nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn} where 'n' is an alphanumeric character. CLSID is not interpreted by the database manager but only forwarded to the OLE APIs at run time.

 

Method ID

Specify the method name of the OLE object to be invoked.

 

 

Server (for OLEDB functions)

Define the local name of a data source.

 

Rowset (for OLEDB functions)

Enter the rowset (table) exposed by the OLE DB provider. Fully qualified table names must be provided for OLE DB providers that support catalog or schema names.

 

Connect string (for OLEDB functions)

String version of the initialization properties needed to connect to a data source. The basic format of a connection string is based on the ODBC connection string. The string contains a series of keyword/value pairs separated by semicolons. The equal sign (=) separates each keyword and its value. Keywords are the descriptions of the OLE DB initialization properties (property set DBPROPSET_DBINIT) or provider-specific keywords.