467,189 Members | 1,173 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,189 developers. It's quick & easy.

Inoperative packages vs. invalid packages

(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.P0014410" 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

Jun 22 '07 #1
  • viewed: 6312
Share:
3 Replies
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
Jun 23 '07 #2
On 23 Cze, 14:29, Serge Rielau <srie...@ca.ibm.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

Jun 23 '07 #3
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

Jun 23 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Debian User | last post: by
2 posts views Thread by Peter Saffrey | last post: by
reply views Thread by Rich Burridge | last post: by
7 posts views Thread by Anthony Robinson | last post: by
reply views Thread by giovanni gherdovich | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.