Online Documentation for SQL Manager for DB2

Methods


The Methods tab of UDS Type Editor lists the methods defined for the structured type. Methods enable you to define behaviors for structured types. Methods are routines that extend SQL. In the case of methods, however, the behavior is integrated solely with a particular structured type.

 

Use items of the Navigation bar to manage methods of the UDS Type.

 

UDS Type Editor - Methods

 

 

The Method Editor dialog allows you to add a new or edit an existing method of the UDS type.

 

Use the Edit tab of the Method Editor dialog to create/edit a method and specify its definition.

 

UDS Type Editor - Methods - Method Editor

 

 

Method type

Use the drop-down list to select the preferable method type. Possible values are: EXTERNAL, SQL.

 

Name

Enter a name for the method being defined. Note that the name must be an unqualified SQL identifier.

 

Specific Name

Provide a unique name for the instance of the method that is being defined. This specific name can be used when creating the method body or dropping the method, but it cannot be used to invoke the method.

 

External name (for external methods)

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

 

Environment options

 

Language

Use this drop-down list to specify the language interface convention to which the user-defined method body is written. This option is available for EXTERNAL methods only. Possible values are: C, JAVA, OLE.

 

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, NO SQL.

 

Parameter style

Use the drop-down list to specify the conventions used for passing parameters to and returning the value from methods. This option is available for EXTERNAL methods only. Possible values are: SQL, DB2GENERAL.

 

CheckBox Deterministic

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

 

CheckBox Fenced

This option specifies whether the method 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 method.

 

CheckBox DB information

This option specifies whether certain specific information known by DB2 will be passed to the method 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 method. A scratchpad enables method to 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 method 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 method. The purpose of such a final call is to enable the external method to free any system resources it has acquired.

 

CheckBox Allow parallel

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

 

CheckBox External action

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

 

External name parts (for external methods only)

This group of options depends on the Language chosen:

  • C

 

Function Editor - Editing function definition - C External name parts

 

Library ID or Path ID (for External functions)

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.

 

If SQL is specified as the method type, you can proceed to the SQL tab of the dialog to input SQL statement for the method being defined.

 

 

The Parameters tab of the New Method dialog allows you to manage parameters of the method.

 

UDS Type Editor - Methods - Method Editor - Parameters

 

 

Use the iconPlus Add parameter and the iconMinus Remove parameter buttons to manage parameters of the method.

 

Specify parameter Name and Data type.

 

If necessary, you can apply filtering for the available data types list by checking the CheckBox Use only SYSTEM datatypes option.

 

Use the Data type drop-down list below to select the data type to be returned by the function.