Link to home
Create AccountLog in
Avatar of juliakir
juliakir

asked on

DB2 -- stored proc recompile after function recreated (dropped and created)

Hi Experts ,

As I understand if db2 function is dropped and created then stored procs using that functions needs to be recompiled as well .
What is the best way to find out which stored procs needs to be recomplied ?
What is the best practice to follow after dropping and creatding functions ?

Thank you very much
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

the best practice after dropping and creating functions is to recompile your code
actually it's not recompile but rather rebind the packages

you can read here when to rebind
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/ad/c0005566.htm

if you want to check if a procedure needs to be rebound you can query SYSCAT.PACKAGES and check if the package is marked as invalid or inoperative
Avatar of juliakir
juliakir

ASKER

This is perfect .I will take a look and get back to you . Thank you very much as always.
Uisng SYSCAT.PACKAGES  is it possible to find affected stored proc names based on package name ?

Can REBIND/BIND be done by DBA only on DB server or developers can do it as well ?

Thank you very much
I think I got wayt to find sp names
select * from SYSCAT.ROUTINEDEP where bname in (select pkgname from syscat.packages where valid <>'Y')

Now I need to figure out how to run rebind

Thanks
also from above article ..in case of alter table stored proc gets invalid .
next time when stored proc gets executed it gets rebinded.

We need to do extra care in regards to fucntions.

How to find out which packages become invlaid because function was recreated ?

Thanks a lot
ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
perfect . thank you