Stored procedure.

Hi all. I have a serious doubt regarding the stored procedures. The question is: When creating a stored procedure the environment variables that I had defined in that moment they did affect to their yield and behavior? This doubt takes place for the following reason: I created a series of stored procedures having defined the variables of environment PSORT_NPROCS, PSORT_DBTEMP, PDQPRIORITY (this variables aren't defined at user environment), now every time that an user executes a stored procedure that user it generates, with the same session number, a great quantity of sessions in online. This causes that online requires many more resources (more memory ram, etc) Thanks. --------------------------------------- Isidre PONS ROCA BASE - Gesti' d'Ingressos Locals (Diputacio de Tarragona) Servei de Sistemes de Informacio Av President Lluis Companys 12-C 43005 - Tarragona SPAIN Tel # +34 977 236731 Fax # +34 977 227302 http://www.altanet.org ipons@dtgna.altanet.org ---------------------------------------

In article <8091go$od6$1@news.xmission.com>, "Pons Roca, Isidre" <ipons@dtgna.altanet.org> wrote: > > Hi all. > > I have a serious doubt regarding the stored procedures. > > The question is: > When creating a stored procedure the environment variables > that I had defined in that moment they did affect to their yield and > behavior? > > This doubt takes place for the following reason: > I created a series of stored procedures having defined the variables > of environment PSORT_NPROCS, PSORT_DBTEMP, > PDQPRIORITY (this variables aren't defined at user environment), > now every time that an user executes a stored procedure that user > it generates, with the same session number, a great quantity of > sessions in online. This causes that online requires many more > resources (more memory ram, etc) > I ran into a similar problem. The environment IS taken during the compile of the SP. However(!), if you do an update statistics for procedure, that recompiles the stored procedure and the procedure takes it's new enviroment from whatever is calling the update statistics. If you have a script that updates statistics in parrellel, you might want to modify it so that it sets the environment variables to whatever the stored procedures need just before it executes the update stats for procedure statement. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.