URGENT: Sysprocplan lock error - sqlcode=-211, ISAM=-144
Hi , Once in a while all users are getting sqlcode=-211, ISAM=-144 on sysprocplan table while trying execute stored procedures from VB5 app. We are using Informix Universal server 9.14.UC2 on solaris 2.6 operating system. Any clue on what could be the cause of the error? Thanks for your help in advance. Venky
In article <7bnuhk$me6$1@news.xmission.com>,
Venky9970@aol.com wrote:
>
> Hi ,
>
> Once in a while all users are getting sqlcode=-211, ISAM=-144 on
> sysprocplan table while trying execute stored procedures from VB5 app.
>
> We are using Informix Universal server 9.14.UC2 on solaris 2.6 operating
> system.
>
> Any clue on what could be the cause of the error?
>
> Thanks for your help in advance.
>
> Venky
>
There are some things, that probably will help you :
1.Issue oncheck command and analyse it's output
2.Perform UPDATE STATISTICS .
3.Perform dbexport/dbimport - this recreates/recompiles your procedures
4.Perform restore from your backup and logical logs, but try point-in-time to
the moment you've found your problem.
If this steps don't help you - probably the problem is in application - check
the way you call procedures.
With best regards, Juri Dovgart,
Bank's "Ukraine" System Administrator‰‰
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Venky9970@aol.com wrote in message <7bnuhk$me6$1@news.xmission.com>...
>
>Hi ,
>
>Once in a while all users are getting sqlcode=-211, ISAM=-144 on
>sysprocplan table while trying execute stored procedures from VB5 app.
1) During EXECURE stored procedures OPTIMIZATION must be set to LOW
(when optimization is HI, then posobility to SP re-created sql statements
plan in runtime)
Exemple:
SET OPTIMIZATION LOW;
EXECUTE PROCEDURE My_SPL();SET OPTIMIZATION HIGTH;
2) Increase number of chash entry in INFORMIX "onconfig" for SP
>
>We are using Informix Universal server 9.14.UC2 on solaris 2.6 operating
>system.
>
>Any clue on what could be the cause of the error?
>
>
>Thanks for your help in advance.
>
>Venky
It looks like locked record. Try "set lock mode to wait [somenumber]" in your stored procedure. -- Regards, Robert Venky9970@aol.com wrote in article <7bnuhk$me6$1@news.xmission.com>... > > Hi , > > Once in a while all users are getting sqlcode=-211, ISAM=-144 on > sysprocplan table while trying execute stored procedures from VB5 app. > > We are using Informix Universal server 9.14.UC2 on solaris 2.6 operating > system. > > Any clue on what could be the cause of the error? > > > Thanks for your help in advance. > > Venky >
Hi All, I used to run Update statistics for procedure <procname> daily, due to locking of sysprocplan records I am running it once a week. I found that sysprocplan for (around 130) stored procedures are updated daily. when is the sysprocplan table updated for a stored procedure other than the following reasons ? :- 1. when the procedure is created 2. tables used within the procedure is altered. 3. when update statistics for procedure is run. In Techinfo product defects knowledge database there were some bugs which mentioned that string concatenation or using opague datatypes in where clause can cause the procplan to be updated every time the stored procedure is run. We are using IUS9.14UC2 on solaris 2.6 Venky *** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ***