SQL Error Code: -211

Hi, I have encounter below error while application is running on my informix 7.31.UD6 (solaris 8). When the below error happen, it slow down the application. As a workaround, i have to turn off the application service,run update stat and start back the application service. In long run, this is not a plan, so please share your experty. SQL Error Code: -211 SQL State: IX000 SQL Message: Cannot read system catalog (sysprocplan). (-144)ISAM error: key value locked

This will happen when some session modifies a table that is referenced by some stored procedure. When that happens, the affected stored procedure must be recompiled (by an implied update statistics for procedure.....) the next time that it is executed in order to create a new query plan because the existing query plan has been invalidated by the ALTER TABLE, DROP INDEX, DROP TABLE, DROP TEMP TABLE, or CREATE INDEX statement that altered the system catalog. Your application may be the one causing the recompile or it may be a victim. It is just hitting a lock on sysprocplan while the offending procedure is being recompiled so it cannot scan the index on sysprocplan to get to the query plan for the procedure that it wants to execute - probably the same one that's being recompiled. Solutions: - Anytime you alter the system catalog, recompile all stored procedures that might reference the altered table. Usually it's easiest to just compile them all, but if you know what's affected, you can save some time. - Make sure that your applications have SET LOCK MODE TO WAIT <N seconds>; after opening the database to minimize these lockouts. - Avoid creating and dropping temp tables within stored procedures. These procedures will recompile themselves EVERY TIME they are executed. Besides, the code to manage temp tables in routines that return multiple rows from them is nasty looking. ;-( - If you create a temp table outside a routine and reference it inside the routine, recompile that one procedure manually after creating the temp table and before executing the routine. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Mar 22, 2009 at 11:09 PM, CHEE KHUAN LEONG <cloudlck@hotmail.com>wrote: > Hi, > I have encounter below error while application is running on my informix > 7.31.UD6 (solaris 8). When the below error happen, it slow down the > application. As a workaround, i have to turn off the application > service,run > update stat and start back the application service. In long run, this is > not a > plan, so please share your experty. > > SQL Error Code: -211 > SQL State: IX000 > SQL Message: Cannot read system catalog (sysprocplan). > (-144)ISAM error: key value locked > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016361e81347cd74c0465cb0ff4