Modifying a stored procedure

I have an application that runs a lot of stored procedures and some of them get updated as the system evolves. I have noticed, that when I use dbaccess to drop and recreate a stored procedure, existing connections to the database would not know about the change, they have to e.g. disconnect and connect again to see the new version. So far so good. However, when I run UPDATE STATISTICS FOR PROCEDURE nnn in dbaccess after recreating the procedure, the existing connections still would not know about the new version, they have to run UPDATE STATISTICS themselves to see it. Now this seems strange to me, as there is only one sysprocplan table, isn't it? Is the mechanism of processing and executing stored procedures documented in length somewhere? Is there another SQL command to let exieting connections know about modified SPs? (IDS 7.3UC2, Solaris 2.6_x86). Cheers, --Micha

You're running into the stored procedure cache. Have a look at onstat -g spc to see which procedures are currently being cached. Not sure how to flush them out of the cache, although I'm sure it's in the manual. Micha Meier wrote in message <36FA5E86.285078C4@ecrc.de>... >I have an application that runs a lot of stored procedures >and some of them get updated as the system evolves. I have noticed, that when I use dbaccess to drop >and recreate a >stored procedure, existing connections to the database >would not know about the change, they have to e.g. disconnect >and connect again to see the new version. So far so good. >However, when I run UPDATE STATISTICS FOR PROCEDURE nnn >in dbaccess after recreating the procedure, the existing >connections still would not know about the new version, >they have to run UPDATE STATISTICS themselves to see it. >Now this seems strange to me, as there is only one >sysprocplan table, isn't it? Is the mechanism of processing >and executing stored procedures documented in length somewhere? >Is there another SQL command to let exieting connections know >about modified SPs? >(IDS 7.3UC2, Solaris 2.6_x86). > >Cheers, > >--Micha

"Thomas J. Girsch" wrote: > > You're running into the stored procedure cache. Have a look at onstat -g > spc to see which procedures are currently being cached. > > Not sure how to flush them out of the cache, although I'm sure it's in the > manual. > > Micha Meier wrote in message <36FA5E86.285078C4@ecrc.de>... > >I have an application that runs a lot of stored procedures > >and some of them get updated as the system evolves. I have noticed, that > when I use dbaccess to drop > >and recreate a > >stored procedure, existing connections to the database > >would not know about the change, they have to e.g. disconnect > >and connect again to see the new version. Well the doc says "OnLine ... stores the procedure in a cache where it can be accessed by any session". So it should not be possible for different sessions to access different versions of the same stored procedure, should it? --Micha

>I have an application that runs a lot of stored procedures >and some of them get updated as the system evolves. I have noticed, that when I use dbaccess to drop >and recreate a >stored procedure, existing connections to the database >would not know about the change, they have to e.g. disconnect >and connect again to see the new version. So far so good. >However, when I run UPDATE STATISTICS FOR PROCEDURE nnn >in dbaccess after recreating the procedure, the existing >connections still would not know about the new version, >they have to run UPDATE STATISTICS themselves to see it. >Now this seems strange to me, as there is only one >sysprocplan table, isn't it? Is the mechanism of processing >and executing stored procedures documented in length somewhere? >Is there another SQL command to let exieting connections know >about modified SPs? >(IDS 7.3UC2, Solaris 2.6_x86). This was the behaviour in release 5 (and may be 6), where every session had its own SP cache. This was fixed at some stage. 7.3 should not behave this way. Bashar Chalabi