Re: SQL: Using restrict when dropping function and stored

You are my hero! Just what I needed. Thank you so much Romano

man. 31. aug. 2020 kl. 19.00 skrev Sue Romano <slromano@xxxxxxxxxx>:

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

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe, or change list options,

visit: https://lists.midrange.com/mailman/listinfo/midrange-l

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx

Before posting, please take a moment to review the archives

at https://archive.midrange.com/midrange-l.



Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.



Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com