Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Possible to set up stored procedures that call each other?

    Hi,

    Is it possible to create stored procedures in this manner --

    CREATE PROCEDURE TEST1 ()
    -------------------
    ------------------
    BEGIN
    if some condition is met THEN
    CALL TEST2 ();
    END IF;
    --------------------
    --------------------
    END
    @


    CREATE PROCEDURE TEST2 ()
    .................................................. ...............
    .................................................. ...............
    BEGIN
    .................................................. ...............
    WHILE condition = 1 DO
    CALL TEST1 ();
    END WHILE;
    END
    @


    The entry point will always be TEST1 ()....if I try to create them using the regular db2 -td@ -vf <filename>, then it complains that the other procedure doesn't exist... is there a way to accomplish this? thanks!!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can try:

    1) create TEST1 without the call to TEST2
    2) create TEST2
    3) recreate TEST1 with the call to TEST2
    4) REBIND TEST2

    Andy

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    great, thanks! will try it out...will the rebind command just be like this --

    db2 "rebind procedure test2" or something else?

    thanks so much for the quick reply!

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No. You rebind packages. Look at the rebind command in the reference. If you are on V9.5 or later you can use this SP to do it easier: IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    Andy

  5. #5
    Join Date
    Nov 2007
    Posts
    265
    thanks, will look into it.. we are using V8.2..

  6. #6
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by ARWinner View Post
    You can try:

    1) create TEST1 without the call to TEST2
    2) create TEST2
    3) recreate TEST1 with the call to TEST2
    4) REBIND TEST2

    Andy

    Hey Andy, thought you might be interested in knowing that I did steps 1 - 3 and the proc worked right after that. It didn't seem to need step 4 which I thought was a little odd since I dropped TEST1 and created it again (step 3).. it's interesting that it didn't mark it inoperative and didn't ask for any rebinding?

Posting Permissions

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