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

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

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!

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

thanks, will look into it.. we are using V8.2..

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?