Hi,

I am porting a big application from Oracle 8i to DB2 8.2 for Linux and we have more than 300 stored procedures and functions to port. Among some normal problems, we are facing a big one.

As we need to change procedures and UDFs a lot of times, we have to drop these objects and create them again (in Oracle there is a "create or replace procedure" option). But, in order to drop a procedure, we need to drop all the dependent objects. If one UDF has 30 dependencies, we'll have to drop all of them and create them again. It's a lot of work...

If it's right, we are going to have more problems, because this will impact in loosing access to these objects (GRANTs) and we'll have to grant all the privileges in all these procedures again.

Is there a solution to this situation?

thanks,

We are using V8.1 which basically works the same as 8.2. For recreating UDFs, I wrote a java program that will drop the dependant objects (UDFs, Views, and Stored Procedures). Then recreate them all in the proper order. Actually it does this for Views and SPs as well. If I just change a SP and other SPs are dependant on it, they will just be marked invalid (syscat.packages.valid <> 'Y'). These are easily fixed by using REBIND on them. As for dealing with the rights issue, V8.1 and 8.2 have a neat way to do this: GRANT EXECUTE ON PROCEDURE myschema.* to group mygroup. This will grant the execute rights on all current AND FUTURE procedures, functions, and methods in the schema provided.

HTH

Andy

ok,
thanks