Inoperative packages vs. invalid packages

[Artur]

(DB2 9) Inoperative packages must be explicitly rebound, but invalid
packages can be rebound automatically. Do you know what is the
difference?

Example:

create function f1
create procedure p1, that is using f1

If I recreate function f1, then when executing p1 automatically I got
an error SQL0572N, Package "DB2ADMIN.P0014 410" is inoperative. This
error is very painful when using developer workbench. I have to leave
gui -- one should expect to do this automatically by DB2 or DWB.

If I create 2 procedures, p1 which executes p2; Recreating p2 can make
the p1 ackage invalid, but procedure p1 will run, bacause the package
will be automatically rebound.
-- Artur Wronski

[Serge Rielau]

Artur wrote:

(DB2 9) Inoperative packages must be explicitly rebound, but invalid
packages can be rebound automatically. Do you know what is the
difference?

When a package is inoperative the user has to intervene to decide
whether the package should be rebound with conservative or non
conservative semantics. Especially in the context of functions which
have rich overloading capabilities there is significant reason to
believe that the package may behave differently after a non-conservative
rebind because a different function could be picked. (changes in
authorizations, other matches along PATH etc...)
The most prominent example for the difference between conservative and
non-conservative rebind is SELECT *. Any subsequently added column is
not visible until the package is rebound non conservatively (i.e.
explicitly).

Now, you will find that improvements to ALTER TABLE in DB2 9 have
started to poke holes into this thinking. I.e. when you change a column
type DB2 will auto-revalidate (non conservative) triggers, SQL Functions
and Views.
I think we can expect non-conservative, implicit revalidation in the
future.

Thanks for bringing this up. It confirms the path I'm pushing.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Artur]

On 23 Cze, 14:29, Serge Rielau <[e-mail] .comwrote:

Artur wrote:

(DB2 9) Inoperative packages must be explicitly rebound, but invalid
packages can be rebound automatically. Do you know what is the
difference?

When a package is inoperative the user has to intervene to decide
whether the package should be rebound with conservative or non
conservative semantics. Especially in the context of functions which
have rich overloading capabilities there is significant reason to
believe that the package may behave differently after a non-conservative
rebind because a different function could be picked. (changes in
authorizations, other matches along PATH etc...)
The most prominent example for the difference between conservative and
non-conservative rebind is SELECT *. Any subsequently added column is
not visible until the package is rebound non conservatively (i.e.
explicitly).

Now, you will find that improvements to ALTER TABLE in DB2 9 have
started to poke holes into this thinking. I.e. when you change a column
type DB2 will auto-revalidate (non conservative) triggers, SQL Functions
and Views.
I think we can expect non-conservative, implicit revalidation in the
future.

Thanks for bringing this up. It confirms the path I'm pushing.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge, Thanks.
Keep pushing ;-)

-- Artur

[Artur]

Keep pushing ;-)

Especially for the cases where udf is recreated -- the only option is
to rebind with resolve any. If this is the only option - should be
done implicitly.

-- Artur