CREATE METHOD statement

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

Group privileges other than PUBLIC are not considered for any table or view specified in the CREATE METHOD statement.

Authorization requirements of the data source for the table or view referenced by the nickname are applied when the method is invoked. The authorization ID of the connection can be mapped to a different remote authorization ID.

Syntax

>>-CREATE------------------------------------------------------->

>--+-METHOD--+-method-name----------+--FOR--type-name-+--------->
   |         '-| method-signature |-'                 |   
   '-SPECIFIC METHOD--specific-name-------------------'   

>--+-●--EXTERNAL--+----------------------+--●--+-----------------------------+--●-+-><
   |              '-NAME--+-'string'---+-'     '-TRANSFORM GROUP--group-name-'    |   
   |                      '-identifier-'                                          |   
   | .-INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST-.                             |   
   '-+----------------------------------------------+--| SQL-method-body |--------'   
     '-INHERIT ISOLATION LEVEL WITH LOCK REQUEST----'                                 

method-signature

|--method-name--(--+----------------------------------------------------+--)-->
                   | .-,----------------------------------------------. |      
                   | V                                                | |      
                   '---+----------------+--data-type1--+------------+-+-'      
                       '-parameter-name-'              '-AS LOCATOR-'          

>--+----------------------------------------------------------------+--|
   '-RETURNS--+-data-type2--+------------+------------------------+-'   
              |             '-AS LOCATOR-'                        |     
              '-data-type3--CAST FROM--data-type4--+------------+-'     
                                                   '-AS LOCATOR-'       

SQL-method-body

|--+-RETURN---------------------+-------------------------------|
   |                        (1) |   
   '-Compound SQL (inlined)-----'   

1. The compound SQL (inlined) statement is only supported for an SQL-method-body in an SQL method definition in a non-partitioned database.

Description

METHOD

Identifies an existing method specification that is associated with a user-defined structured type. The method-specification can be identified through one of the following means:

method-name

Names the method specification for which a method body is being defined. The implicit schema is the schema of the subject type (type-name). There must be only one method specification for type-name that has this method-name (SQLSTATE 42725).

method-signature

Provides the method signature which uniquely identifies the method to be defined. The method signature must match the method specification that was provided on the CREATE TYPE or ALTER TYPE statement (SQLSTATE 42883).

method-name

Names the method specification for which a method body is being defined. The implicit schema is the schema of the subject type (type-name).

parameter-name

Identifies the parameter name. If parameter names are provided in the method signature, they must be exactly the same as the corresponding parts of the matching method specification. Parameter names are supported in this statement solely for documentation purposes.

data-type1

Specifies the data type of each parameter. Array types are not supported (SQLSTATE 42815).

For a more complete description of each built-in data type, see "CREATE TABLE".

Character and graphic string data types cannot specify string units of CODEUNITS32.

AS LOCATOR

For the LOB types or distinct types which are based on a LOB type, the AS LOCATOR clause can be added.

RETURNS

This clause identifies the output of the method. If a RETURNS clause is provided in the method signature, it must be exactly the same as the corresponding part of the matching method specification on CREATE TYPE. The RETURNS clause is supported in this statement solely for documentation purposes.

data-type2

Specifies the data type of the output. Array types are not supported (SQLSTATE 42815).

AS LOCATOR

For LOB types or distinct types which are based on LOB types, the AS LOCATOR clause can be added. This indicates that a LOB locator is to be returned by the method instead of the actual value.

data-type3 CAST FROM data-type4

This form of the RETURNS clause is used to return a different data type to the invoking statement from the data type that was returned by the function code.

AS LOCATOR

For LOB types or distinct types which are based on LOB types, the AS LOCATOR clause can be used to indicate that a LOB locator is to be returned from the method instead of the actual value.

FOR type-name

Names the type for which the specified method is to be associated. The name must identify a type already described in the catalog (SQLSTATE 42704). In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names.

SPECIFIC METHOD specific-name

Identifies the particular method, using the specific name either specified or defaulted to at CREATE TYPE time. The specific-name must identify a method specification in the named or implicit schema; otherwise, an error is raised (SQLSTATE 42704).

EXTERNAL

This clause indicates that the CREATE METHOD statement is being used to register a method, based on code written in an external programming language, and adhering to the documented linkage conventions and interface. The matching method-specification in CREATE TYPE must specify a LANGUAGE other than SQL. When the method is invoked, the subject of the method is passed to the implementation as an implicit first parameter.

If the NAME clause is not specified, "NAME method-name" is assumed.

NAME

This clause identifies the name of the user-written code which implements the method being defined.

'string'

The 'string' option is a string constant with a maximum of 254 bytes. The format used for the string is dependent on the LANGUAGE specified. For more information about the specific language conventions, see "CREATE FUNCTION (External Scalar) statement".

identifier

This identifier specified is an SQL identifier. The SQL identifier is used as the library-id in the string. Unless it is a delimited identifier, the identifier is folded to upper case. If the identifier is qualified with a schema name, the schema name portion is ignored. This form of NAME can only be used with LANGUAGE C (as defined in the method-specification on CREATE TYPE).

TRANSFORM GROUP group-name

Indicates the transform group that is used for user-defined structured type transformations when invoking the method. A transform is required since the method definition includes a user-defined structured type.

It is strongly recommended that a transform group name be specified; if this clause is not specified, the default group-name used is DB2_FUNCTION. If the specified (or default) group-name is not defined for a referenced structured type, an error results (SQLSTATE 42741). Likewise, if a required FROM SQL or TO SQL transform function is not defined for the given group-name and structured type, an error results (SQLSTATE 42744).

INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST or INHERIT ISOLATION LEVEL WITH LOCK REQUEST

Specifies whether or not a lock request can be associated with the isolation-clause of the statement when the method inherits the isolation level of the statement that invokes the method. The default is INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST.

INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST

Specifies that, as the method inherits the isolation level of the invoking statement, it cannot be invoked in the context of an SQL statement which includes a lock-request-clause as part of a specified isolation-clause (SQLSTATE 42601).

INHERIT ISOLATION LEVEL WITH LOCK REQUEST

Specifies that, as the method inherits the isolation level of the invoking statement, it also inherits the specified lock-request-clause.

SQL-method-body

The SQL-method-body defines how the method is implemented if the method specification in CREATE TYPE is LANGUAGE SQL.

The SQL-method-body must comply with the following parts of method specification:

• DETERMINISTIC or NOT DETERMINISTIC (SQLSTATE 428C2)
• EXTERNAL ACTION or NO EXTERNAL ACTION (SQLSTATE 428C2)
• CONTAINS SQL or READS SQL DATA (SQLSTATE 42985)

Parameter names can be referenced in the SQL-method-body. The subject of the method is passed to the method implementation as an implicit first parameter named SELF.

For additional details, see "Compound SQL (inlined) statement" and "RETURN statement".

Rules

• The method specification must be previously defined using the CREATE TYPE or ALTER TYPE statement before CREATE METHOD can be used (SQLSTATE 42723).
• If the method being created is an overriding method, those packages that are dependent on the following methods are invalidated:
  • The original method
  • Other overriding methods that have as their subject a supertype of the method being created
• The XML data type cannot be used in a method.

Notes

• If the method allows SQL, the external program must not attempt to access any federated objects (SQLSTATE 55047).
• Privileges: The definer of a method always receives the EXECUTE privilege on the method, as well as the right to drop the method.

  If an EXTERNAL method is created, the definer of the method always receives the EXECUTE privilege WITH GRANT OPTION.

  If an SQL method is created, the definer of the method will only be given the EXECUTE privilege WITH GRANT OPTION on the method when the definer has WITH GRANT OPTION on all privileges required to define the method, or if the definer has SYSADM or DBADM authority. The definer of an SQL method only acquires privileges if the privileges from which they are derived exist at the time the method is created. The definer must have these privileges either directly, or because PUBLIC has the privileges. Privileges held by groups of which the method definer is a member are not considered. When using the method, the connected user's authorization ID must have the valid privileges on the table or view that the nickname references at the data source.

• Table access restrictions: If a method is defined as READS SQL DATA, no statement in the method can access a table that is being modified by the statement which invoked the method (SQLSTATE 57053).

Examples

• Example 1:

     CREATE METHOD BONUS (RATE DOUBLE)
       FOR EMP
       RETURN SELF..SALARY * RATE

• Example 2:

     CREATE METHOD SAMEZIP (addr address_t)
       RETURNS INTEGER
       FOR address_t
       RETURN
         (CASE
           WHEN (self..zip = addr..zip)
             THEN 1
           ELSE 0
         END)

• Example 3:

     CREATE METHOD DISTANCE (address_t)
       FOR address_t
       EXTERNAL NAME 'addresslib!distance'
       TRANSFORM GROUP func_group