I have two database in the same server let us call it MYDB1 and MYDB2.

I backed up all the SQL SP from MYDB1 using help of this forum (thanks to Sathyaram). Now I want to move the SP just restored to MYDB2. While doing so I want the following features:

1. If same name exists in the destination just overwrite it
2. After copying automatically bind all the SP

How do I do this??

Use the "get routine" from MYDB1 AND
"put routine" function in MYDB2. but make sure to drop the SP's in MYDB2 before that . This way all the SP's are available in MYDB2.

Hope this will solve your prob.

Cheers !

Trinmoy

Thanks Trinmoy.

Questions

1. If I have 100 SP's then do I have to do the GET & PUT individually?
2. When I PUT routine does DB2 copy the SP and BIND it too?
3. Is there a CLP command to drop all SPs with a particular schema?
3. Is there a CLP command to BIND all SPs with a particular schema?

1. If I have 100 SP's then do I have to do the GET & PUT individually?
2. When I PUT routine does DB2 copy the SP and BIND it too?
3. Is there a CLP command to drop all SPs with a particular schema?
3. Is there a CLP command to BIND all SPs with a particular schema?


Answers :



First of all I am bit curious do you need to drop the SP's freequently ??

Anyways simple way is write a bat file for the same.

1. yes.
2. yes but you might need to rebind the perticular package .
3. open up a batch file ...
a> list all SP's putting a select on syscat.procedures and use the "drop procedure" command.
4. list all the packages(specificname) for those SP's and use "rebind schemaname.packagename"

Hope this will solve your problem.

Cheers..

Trinmoy

Also, don't forget about granting EXECUTE permissions.