Re: Problem with STORED PROCEDURES

I don't use TEMP tables in the SP. Is there any way to avoid the SP re-optimization?? Because of that one of process produces an error because the another process locks the system catalog ( sysprocplan ) during the optimization. I'm grafetul for your interest and your time. Thank you. }Your outgoing email gets your IP address rather than you domain name }sunatxx.gob.pe, which makes replying a nuisance. } }<<< RCPT To:<jtaipe@161.132.37.1> }421 Host 161.132.37.1. not found for mailer ddn. }550 <jtaipe@161.132.37.1>... Host unknown } }Date: Tue, 26 Mar 1996 11:34:04 -0800 }From: johnl@informix.com (Jonathan Leffler) }To: jtaipe@161.132.37.1 }Subject: Re: Problem with STORED PROCEDURES } }Do you use TEMP tables in the SP? If so, don't. They cause the SP to }be re-optimized every time it is run, because the temp table is different }every time it is run. } }Yours, }Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> } }>Date: Tue, 26 Mar 96 11:54 PST }>From: jtaipe@161.132.37.1 (John E. Taipe Arias) }>X-Informix-List-Id: <list.9191> }> }>Hello : }> }>Before anything, please excuse me if I do any mistakes, because }>english isn't natural language. }>My name is John E. Taipe Arias, I'm peruvian system analist. }>After I have read a group of manuals and magazines ( very simple }>for the serie of STORED PROCEDURES (SP) limitations and strenghts ) }>and have proved a lot of posible solutions; I write this mail to }>ask any comment, help or posible solutions for my }>problem. }> }>I have developed various SP invoked by many programs. When I run }>a program, that calls to the SP, I didn't have any problem. But }>when I run more than one program at the same time, one of the }>processes produce the following error : }> }> SQL statement error number -211 }> Cannot read system catalog ( sysprocplan ) }> SYSTEM error number -144 }> ISAM error : Key value locked }> }>I have tried the following solutions : }> }>1. SET LOCK MODE TO WAIT in the program and/or SP : the problem }> is solved, but it's very dangerous to remain an indefinite wait for the }> other tables of my application. I don't considerate this solution. }> }>2. SET LOCK MODE TO WAIT 30 in the program ans/or SP : it produces }> the next error : }> }> SQL statement error number -312 }> Cannot update system catalog ( sysprocplan ) }> SYSTEM error number -100 }> ISAM error : Duplicate value for a record with unique key }> }>3. SET OPTIMIZATION LOW in the program y de SP : it produces the }> original error. }> }>I am working with the following configuration : Computer Alpha Server 2100 }>4/275, Database Informix to DEC Alplha Models V3.2, }>Online 5.06.UC1 and I-4gl 4.14.UC1. }> }>I hope Informix Corporation or another person could help me to solve }>this problem that would involve a great limitation to the SP uses. }>I'll keep in contact for any consult or to talk about my problem. }> }>Sincerely yours, }> }>{***********************************************************************} }>SUPERINTENDENCIA DE ADMINISTRACION TRIBUTARIA - SUNAT }>Convenio SUNAT-CIAT-BID. }>Av.Garcilaso de la Vega 1472 - Lima - Peru }>John Edison Taipe Arias }>email : jtaipe@sunatxx.gob.pe }>Fono : 051 1 348-2459 }> 051 1 432-9535 Anexo 4208 }>Fax : 051 1 433-3012 }>{***********************************************************************} } } {***********************************************************************} SUPERINTENDENCIA DE ADMINISTRACION TRIBUTARIA - SUNAT Convenio SUNAT-CIAT-BID. Av.Garcilaso de la Vega 1472 - Lima - Peru John Edison Taipe Arias email : jtaipe@sunatxx.gob.pe Fono : 051 1 348-2459 051 1 432-9535 Anexo 4208 Fax : 051 1 433-3012 {***********************************************************************}