The CREATE METHOD statement is used to associate a method
body with a method specification that is already part of the definition
of a user-defined structured type.
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
The privileges held by the
authorization ID of the statement must include at least one of the
following:
- CREATEIN privilege on the schema of the structured type referred
to in the CREATE METHOD statement
- The
owner of the structured type referred to in the CREATE METHOD statement
- DBADM authority
To associate an external method body with its method
specification, the privileges held by the authorization ID of the
statement must also include at least one of the following:
- CREATE_EXTERNAL_ROUTINE authority on the database
- DBADM authority
When creating an SQL method, the privileges
held by the authorization ID of the statement must also include at
least one of the following for each table, view, or nickname identified
in any fullselect:
- CONTROL privilege on that table, view, or nickname
- SELECT privilege on that table, view, or nickname
- DATAACCESS authority
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)-----'
Notes:
- 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".
- 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 on 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