re: SQL: Using restrict when dropping function and stored

When you create a function or procedure, if you do not qualify the
referenced object names there is no way for the DROP statement to enforce
the RESTRICT option. The object could exist in several different
libraries. In your example, since function A is not qualified in the
function B definition it has no effect on RESTRICT processing on the DROP.
If the definition had been RETURN MYLIB.A(), the DROP FUNCTION A RESTRICT
would have failed.

This is pointed out in the CREATE FUNCTION(SQL scalar) documentation:

Dependent objects: An SQL routine is dependent on objects that are
referenced in the SQL-routine-body.
The names of the dependent objects are stored in catalog view
SYSROUTINEDEP. If the object reference
in the SQL-routine-body is a fully qualified name or, in SQL naming, if an
unqualified name is qualified by
the current schema, then the schema name of the object in SYSROUTINEDEP
will be set to the specified
name or the value of the current schema. Otherwise, the schema name is not
set to a specific schema
name. Unqualified function names, variable names, and type names will have
a schema name of
CURRENT PATH. If the name is not set to an actual schema name, then DROP
and ALTER statements will
not be able to determine whether the routine is dependent on the object
being altered or dropped.

Hello.

What have I misunderstood regarding:

drop function xxxx *restrict;*

Why does the IBM i not complain in the following example that function a()
is in use of b() and can not be dropped using the restrict keyword .. I

had

the impression the it was the idea of "restrict"

create function a () returns int
begin
return 1;
end;

create function b () returns int
begin
return a();
end;

drop function a restrict;

Same issue applies to procedures as well.

Sue Romano
Db2 for IBM i Development