Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    106

    Unanswered: Moving Stored Procedure from one db to another

    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??

    You are the creator of your own destiny!

  2. #2
    Join Date
    Nov 2003
    Location
    kualaumpur
    Posts
    33

    Thumbs up

    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

  3. #3
    Join Date
    Aug 2003
    Posts
    106
    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?
    Last edited by db2guru1; 08-18-04 at 18:25.

    You are the creator of your own destiny!

  4. #4
    Join Date
    Nov 2003
    Location
    kualaumpur
    Posts
    33

    Smile Try this..

    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

  5. #5
    Join Date
    Sep 2003
    Posts
    85
    Also, don't forget about granting EXECUTE permissions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •