A database method of a structured type is a relationship between a set of input data values and a set of result values, where the first input value (or subject argument) has the same type, or is a subtype of the subject type (also called the subject parameter), of the method. For example, a method called CITY, of type ADDRESS, can be passed input data values of type VARCHAR and the result is an ADDRESS (or a subtype of ADDRESS).
Methods are defined implicitly or explicitly, as part of the definition of a user-defined structured type.
Implicitly defined methods are created for every structured type. Observer methods are defined for each attribute of the structured type. Observer methods allow applications to get the value of an attribute for an instance of the type. Mutator methods are also defined for each attribute, allowing applications to mutate the type instance by changing the value for an attribute of a type instance. The CITY method described above is an example of a mutator method for the type ADDRESS.
Explicitly defined methods, or user-defined methods are methods that are registered to a database in SYSCAT.FUNCTIONS, by using a combination of CREATE TYPE (or ALTER TYPE ADD METHOD) and CREATE METHOD statements. All methods defined for a structured type are defined in the same schema as the type.
With user-defined methods for structured types, DB2 allows users and application developers to extend the function of the database system. This is accomplished by adding method definitions provided by users, or third party vendors, to be applied to structured type instances in the database engine. The added method definitions provide a higher level of performance as opposed to retrieving rows from the database and applying functions on the retrieved data. Defining database methods also enables the database to exploit the same methods in the engine used by an application, providing a greater degree of interaction efficiency between the application and database. This results in higher productivity for application developers, because it is more object-oriented.
A user-defined method can be either external or based on an SQL expression. An external method is defined to the database with a reference to an object code library and a function within that library that will be executed when the method is invoked. A method based on an SQL expression returns the result of the SQL expression when the method is invoked. Such methods do not require any object code library, since they are written completely in SQL.
A user-defined method can return a single-valued answer each time it is called. This value can be a structured type. A method can be defined as type preserving (using SELF AS RESULT), to allow the dynamic type of the subject argument to be returned as the returned type of the method. All implicitly defined mutator methods are type preserving.
A method is identified by its subject type, a method name, the number of parameters and the data types of its parameters. This is called a method signature, and it must be unique within the database.
There can be more than one method with the same name for a structured type provided that:
A method name which has multiple method instances is called an overloaded method. A method name can be overloaded within a type, in which case there is more than one method by that name for the type (all of which have different parameter types). A method name can also be overloaded in the subject type hierarchy, in which case there is more than one method by that name in the type hierarchy, and these methods also must have different parameter types.
A method can be invoked by referring, in an allowable context, to the method name preceded by both a reference to a structured type instance (the subject argument), and the double dot operator. The list of arguments enclosed in parentheses must follow. The method that is actually invoked is determined based on the static type of the subject type, using the method resolution described in the following section. Methods defined WITH FUNCTION ACCESS can also be invoked using function invocation, in which case the regular rules for function resolution are applied.
Given a method invocation, the database manager must decide which of the possible methods with the same name is the "best" fit. Functions (built-in or user-defined) are not considered during method resolution.
An argument is a value passed to a method upon invocation. When a method is invoked in SQL, it is passed the subject argument (of some structured type) and a list of zero or more arguments. They are positional in that the semantics of an argument are determined by its position in the argument list. The subject argument is considered as the first argument. A parameter is a formal definition of an input to a method.
When a method is defined to the database, either implicitly (system-generated for a type) or by a user (user-defined methods), its parameters are specified (with the subject parameter as the first parameter), and the order of their definitions defines their positions and their semantics. Therefore, every parameter is a particular positional input of a method. On invocation, an argument corresponds to a particular parameter by virtue of its position in the list of arguments.
The database manager uses the name of the method given in the invocation, the number and data types of the arguments, all the methods with the same name for the subject argument's static type (and it's supertypes), and the data types of their corresponding parameters as the basis for deciding whether or not to select a method.
The following are the possible outcomes of the decision process:
PROXIMITY(INTEGER) FOR SITE PROXIMITY(DOUBLE) FOR SITE
the following method invocation (where ST is a SITE column, DB is a DOUBLE column):
SELECT ST..PROXIMITY(DB) ...
then, the second PROXIMITY will be chosen.
The following method invocation (where SI is a SMALLINT column):
SELECT ST..PROXIMITY(SI) ...
would choose the first PROXIMITY, since SMALLINT can be promoted to INTEGER and is a better match than DOUBLE, which is further down the precedence list.
When considering arguments that are structured types, the precedence list includes the supertypes of the static type of the argument. The best fit is the function defined with the supertype parameter that is closest in the structured type hierarchy to the static type of the function argument.
SELECT ST..PROXIMITY(C) ...
the argument is inconsistent with the parameter of both PROXIMITY functions.
RISK(INTEGER) FOR DRILLSITE RISK(DOUBLE) FOR SITE
the following method invocation (where DRST is a DRILLSITE column, DB is a DOUBLE column):
SELECT DRST..RISK(DB) ...
then, the second RISK will be chosen since DRILLSITE can be promoted to SITE.
The following method reference (where SI is a SMALLINT column):
SELECT DRST..RISK(SI) ...
would choose the first RISK, since SMALLINT can be promoted to INTEGER, which is closer on the precedence list than DOUBLE, and DRILLSITE is a better match than SITE, which is a supertype.
Methods within the same type hierarchy cannot have the same signatures, considering the parameters other than the subject parameter.
Comparing the data types of the arguments with the defined data types of the parameters of the method under consideration forms the basis for the decision of which method in a group of like-named methods is the "best fit". Note that the data type of the result of the method under consideration does not enter into this determination.
Method resolution is done using the steps that follow.
For each argument, eliminate all functions that are not the best match for that argument. The best match for a given argument is the first data type appearing in the precedence list, corresponding to the argument data type in Table 5 for which there exists a function with a parameter of that data type.
Lengths, precisions, scales and the "FOR BIT DATA" attribute are not considered in this comparison. For example, a DECIMAL(9,1) argument is considered an exact match for a DECIMAL(6,5) parameter, and a VARCHAR(19) argument is an exact match for a VARCHAR(6) parameter.
The best match for a user-defined structured-type argument is itself; the next best match is its immediate supertype, and so on for each supertype of the argument. Notice that only the static type (declared type) of the structured-type argument is considered, and not the dynamic type (most specific type).
The following is an example of successful method resolution.
There are seven FOO methods for three structured types defined in a hierarchy of GOVERNOR as a subtype of EMPEROR as a subtype of HEADOFSTATE, registered with signatures:
CREATE METHOD FOO (CHAR(5), INT, DOUBLE) FOR HEADOFSTATE SPECIFIC FOO_1 ... CREATE METHOD FOO (INT, INT, DOUBLE) FOR HEADOFSTATE SPECIFIC FOO_2 ... CREATE METHOD FOO (INT, INT, DOUBLE, INT) FOR HEADOFSTATE SPECIFIC FOO_3 ... CREATE METHOD FOO (INT, DOUBLE, DOUBLE) FOR EMPEROR SPECIFIC FOO_4 ... CREATE METHOD FOO (INT, INT, DOUBLE) FOR EMPEROR SPECIFIC FOO_5 ... CREATE METHOD FOO (SMALLINT, INT, DOUBLE) FOR EMPEROR SPECIFIC FOO_6 ... CREATE METHOD FOO (INT, INT, DEC(7,2)) FOR GOVERNOR SPECIFIC FOO_7 ...
The method reference is as follows (where I1 and I2 are INTEGER columns, D is a DECIMAL column and E is an EMPEROR column):
SELECT E..FOO(I1, I2, D) ...
Following through the algorithm...
FOO_7 is eliminated as a candidate, because the type GOVERNOR is a subtype of EMPEROR (not a supertype).
FOO_3 is eliminated as a candidate, because it has the wrong number of parameters.
FOO_1 and FOO_6 are eliminated because, in both cases, the first argument (not the subject argument) cannot be promoted to the data type of the first parameter. Because there is more than one candidate remaining, the arguments are then considered in order.
For the subject argument, FOO_2 is a supertype, while FOO_4 and FOO_5 match the subject argument.
For the first argument, the remaining methods, FOO_4 and FOO_5, are an exact match with the argument type. No methods can be eliminated from consideration, therefore the next argument must be examined.
For this second argument, FOO_5 is an exact match while FOO_4 is not, so it is eliminated from consideration. This leaves FOO_5 as the method chosen.
Once the method is selected, there are still possible reasons why the use of the method may not be permitted.
Each method is defined to return a result with a specific data type. If this result data type is not compatible with the context in which the method is invoked, an error will occur. For example, assume that the following methods named STEP are defined, each with a different data type as the result:
STEP(SMALLINT) FOR TYPEA RETURNS CHAR(5) STEP(DOUBLE) FOR TYPEA RETURNS INTEGER
and the following method reference (where S is a SMALLINT column and TA is an column of TYPEA):
SELECT 3 + TA..STEP(S) ...
then, because there is an exact match on argument type, the first STEP is chosen. An error occurs on the statement, because the result type is CHAR(5) instead of a numeric type as required for an argument of the addition operator.
Note that when the selected method is a type preserving method:
This may be a subtype of the result type specified in the type preserving method definition, which in turn may be a supertype of the dynamic type that is actually returned when the method is processed.
In cases where the arguments of the method invocation were not an exact match to the data types of the parameters of the selected method, the arguments are converted to the data type of the parameter at execution using the same rules as assignment to columns (see Assignments and Comparisons). This includes the case where precision, scale, or length differs between the argument and the parameter, but excludes the case where the dynamic type of the argument is a subtype of the parameter's static type.