DB2 LUW 9.7 added stored procedure support for default and
named arguments. When more than one procedure exists with the
same name, DB2 must perform procedure resolution and pick the
most appropriate procedure to invoke. Although procedure
resolution is performed as documented, this APAR will enhance
procedure resolution to ensure that the best choice
procedureis selected.
Example 1 (full DDL not shown for simplicity).
In this example, the current procedure resolution rules
would choose the first version of procedure P1() because it
has the fewest number of parameters. However, while trying
to assign the value 1 to parameter i2, the fact that there is
no parameteri2 would be recognized and sqlcode SQL20483N,
reason code 1, would be returned. Reason code 1 is, "The
procedure invocation of "P1" includes a named parameter
parameter-name which does notexist in the procedure
definition."
CREATE PROCEDURE P1(i1 int)...
CREATE PROCEDURE P1(i1 int default
0, i2 int default 0)... CALL P1(i2=>1)
Incorrect behaviour: The first version
of P1() is chosen and sqlcode SQL20483N reason code
1 is returned.
Correct behaviour: The first version of
P1() is not considered acandidate for selection because
this version doesn'thave a parameter named i2 (a named
argument in the CALL statement). Therefore, the second
version of P1() is chosen and can be successfully called. I1
is given its default value (0) and i2 is given the
argument value specified in the CALL statement (1).
Example 2 (full DDL not shown
for simplicity).
In this example, the
current procedure resolution rules would choose the
first version of procedure P1() becauseit has the fewest
number of parameters. However, while assigning argument
values to parameters, the fact that no value was specified for
parameter i1 would be recognized and sqlcodeSQL20484N would
bereturned. "The invocation of routine "P1" omits a
parameter "I1" which is not defined with a DEFAULT.
SQLSTATE=428HF".
CREATE PROCEDURE P1(i1 int, i2 int default
2)... CREATE PROCEDURE P1(i1 int default 0, i2
int default 0, i3 int default 0)...
CALL P1(i2=>1)
Incorrect behaviour: The first version of
P1() is chosen and sqlcode SQL20484N is returned.
Correct behaviour: The first version of P1() is
not considered a candidate for selection because the CALL
statement doesn't provide a value (either positionally or by
name) for a parameterdefined without a default (i1).
Therefore,the second version of P1() is chosen and can be
successfully called. I1 is given its default value (0) and
i2 is given the argument value specified in the CALL
statement (1).
Procedure Resolution in the CALL
statement documentation will beupdated as
follows:
"
CALL Statement
.
.
.
Procedure resolution: Given a
procedure invocation, the databasemanager must
decide which of the possible procedures with the same
name to execute.
* Let A be the number of
arguments in a procedure invocation. *
Let P be the number of parametersin a procedure signature. *
Let N be the number of parameters without a default.
Candidate procedures for
resolution of a procedure invocation are
selected based on the following criteria: * Each candidate
procedure has a matching name and an
applicablenumber of parameters. An applicable number of
parameters satisfies the condition N<= A <= P.
* Each candidate procedure has a parameter name which matches
each named argument in the CALL statement and the parameter does
not correspond to a positional (or unnamed) argument.
* Each candidate procedure parameter which
doesn't have a corresponding argument in the CALL
statement, specified by either position or name, is
defined with a default. " |
