CREATE METHOD statement
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
- IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the method does not exist.
- CREATEIN privilege on the schema, if the schema name of the method refers to an existing schema.
- SCHEMAADM authority on the schema, if the schema name of the method refers to an existing schema.
- DBADM authority.
- CREATE_EXTERNAL_ROUTINE authority on the database.
- SYSADM authority.
- DBADM authority if the DB2_ALTERNATE_AUTHZ_BEHAVIOUR registry variable is set and contains the
value EXTERNAL_ROUTINE_DBADM.Note: Db2 11.1.4.7 security special build 41268 includes changes to the implicit authorities of both the SYSADM and the DBADM authorities. By default, the SYSADM authority, instead of the DBADM authority, implicitly has the authorities CREATE_EXTERNAL_ROUTINE and CREATE_NOT_FENCED_ROUTINE. If the DB2_ALTERNATE_AUTHZ_BEHAVIOUR registry variable is set and contains the value EXTERNAL_ROUTINE_DBADM or NOT_FENCED_ROUTINE_DBADM respectively, then the DBADM authority also implicitly has these privileges.
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
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
andRETURN 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