Re: Stored procedures locking behaviour

In article <5krr6r$mpr@cssun.mathcs.emory.edu>, Peter Tashkoff <TASHKOP@kiwi.co.nz> writes >I have today downloaded Rafal Czerniawski's >excellent article on Stored Procedures from the >IIUG website. > >It mentions exclusive locking of system tables when >creating and re-optimising SPs. This is not >covered as far as I can tell in TFM. > >Does anyone know of where I can get more detail >on this or alternatively provide the answers to the >following questions? > >1. What tables are locked during the create >process. sysprocplan, sysprocbody(?) sysproctext(?). >2. What tables are locked during re-optimisation. again sysprocplan >3. What table access is required to *run* an SP >(without re-optimising.) Generally the sysproc tables in share mode i.e. nothing to worry about. > >Our current policy is to allow new or changed SPs >to go live with an application level outage only. On Sounds fine. >reading Rafal's article I am wondering whether we >have been lucky so far, and need to only put SPs >live during a general database outage. > The only problem is that no-one must be exeucting the procedure whilst it is being updated ot else they will run the old one. >TIA >Peter Tashkoff <tashkop@zespri.co.nz> >Zespri International Limited >This posting may not be used by any party to vilify >another. Standard disclaimers apply. > > -- David Williams