Re: SPL Contention
bozon wrote:
> Make sure that you have explicitly updated statistics on this
> procedure. I believe that you get this problem if the procedure had its
> statistics updated.
updating the statistics of the procedure may resvolve the 211 error
What can cause this problem is that something changes the version
number of a table the procedure uses which invalidates the query plans
of the procdure causing it to be recomplied. Subsequently running
update statistics on the procedure will manually regenrate the queryplans
things that can change the version number of the table include but are
not limited to :
1/ update statistics of a table
2/ adding or revoking priviledges
3/ modifying the columns
4/ adding or removing indexes
Some older versions of the ODBC driver were forcing update statistics
on tables and causing this problem to occur.
run SELECT taname, version FROM systables
for each of the tables your procedure references and see if and when
the value of version changes
>
> Ben wrote:
> > -- Solaris 2.6 --
> > -- IDS 9.4 UC4 --
> >
> > I ran into an apparent contention problem today with a stored procedure.
> >
> > SPL 'X'
> >
> > process 1 executes SPL 'X' many times in serial
> >
> > process 2 executes SPL 'X' once every few minutes
> >
> > process 2 cannot execute SPL 'X' while process 1 is running - returns -211 error.
> >
> > I have searched Google and found similiar issues but no solid solutions - it seems that one or more of the sys* tables is locked in exclusive mode which prevents process 2 from executing the SPL.
> >
> > Any ideas? Thank you in advance.
> >
> > Ben
> > ------=_NextPart_000_001C_01C6A0FF.DA9D14B0
> > Content-Type: text/html; charset=iso-8859-1
> > Content-Transfer-Encoding: quoted-printable
> > X-Google-AttachSize: 1526
> >
> > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > <HTML><HEAD>
> > <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> > <META content="MSHTML 6.00.2900.2873" name=GENERATOR>
> > <STYLE></STYLE>
> > </HEAD>
> > <BODY bgColor=#ffffff>
> > <DIV><FONT size=2>
> > <DIV><FONT size=2>-- Solaris 2.6 --</FONT></DIV>
> > <DIV><FONT size=2>-- IDS 9.4 UC4 --</FONT></DIV></FONT></DIV>
> > <DIV><FONT size=2></FONT> </DIV>
> > <DIV><FONT size=2>I ran into an apparent contention problem today with a
> > stored procedure.</FONT></DIV>
> > <DIV><FONT size=2></FONT> </DIV>
> > <DIV><FONT size=2>SPL 'X'</FONT></DIV>
> > <DIV><FONT size=2></FONT> </DIV>
> > <DIV><FONT size=2>process 1 executes SPL 'X' many times in serial</FONT></DIV>
> > <DIV><FONT size=2></FONT> </DIV>
> > <DIV><FONT size=2>process 2 executes SPL 'X' once every few minutes</FONT></DIV>
> > <DIV><FONT size=2></FONT> </DIV>
> > <DIV><FONT size=2>process 2 cannot execute SPL 'X' while process 1 is running -
> > returns -211 error.</FONT></DIV>
> > <DIV><FONT size=2></FONT> </DIV>
> > <DIV><FONT size=2>I have searched Google and found similiar issues but no solid
> > solutions - it seems that one or more of the sys* tables is locked in exclusive
> > mode which prevents process 2 from executing the SPL.</FONT></DIV>
> > <DIV><FONT size=2></FONT> </DIV>
> > <DIV><FONT size=2>Any ideas? Thank you in advance.</FONT></DIV>
> > <DIV><FONT size=2></FONT> </DIV>
> > <DIV><FONT size=2>Ben</FONT></DIV></BODY></HTML>
> >
> > ------=_NextPart_000_001C_01C6A0FF.DA9D14B0--
Thank you for all of your responses - they have been very helpful and I
really appreciate it.
Have a good weekend!
Ben
----- Original Message -----
From: "scottishpoet" <dryburghj@yahoo.com>
Newsgroups: comp.databases.informix
To: <informix-list@iiug.org>
Sent: Thursday, July 06, 2006 5:49 PM
Subject: Re: SPL Contention
>
> bozon wrote:
>> Make sure that you have explicitly updated statistics on this
>> procedure. I believe that you get this problem if the procedure had its
>> statistics updated.
>
> updating the statistics of the procedure may resvolve the 211 error
>
> What can cause this problem is that something changes the version
> number of a table the procedure uses which invalidates the query plans
> of the procdure causing it to be recomplied. Subsequently running
> update statistics on the procedure will manually regenrate the query> plans
>
> things that can change the version number of the table include but are
> not limited to :
>
> 1/ update statistics of a table
>
> 2/ adding or revoking priviledges
>
> 3/ modifying the columns
>
> 4/ adding or removing indexes
>
> Some older versions of the ODBC driver were forcing update statistics
> on tables and causing this problem to occur.
>
> run SELECT taname, version FROM systables
>
> for each of the tables your procedure references and see if and when
> the value of version changes
>
>
>
>>
>> Ben wrote:
>> > -- Solaris 2.6 --
>> > -- IDS 9.4 UC4 --
>> >
>> > I ran into an apparent contention problem today with a stored
>> > procedure.
>> >
>> > SPL 'X'
>> >
>> > process 1 executes SPL 'X' many times in serial
>> >
>> > process 2 executes SPL 'X' once every few minutes
>> >
>> > process 2 cannot execute SPL 'X' while process 1 is running -
>> > returns -211 error.
>> >
>> > I have searched Google and found similiar issues but no solid
>> > solutions - it seems that one or more of the sys* tables is locked in
>> > exclusive mode which prevents process 2 from executing the SPL.
>> >
>> > Any ideas? Thank you in advance.
>> >
>> > Ben
>> > ------=_NextPart_000_001C_01C6A0FF.DA9D14B0
>> > Content-Type: text/html; charset=iso-8859-1
>> > Content-Transfer-Encoding: quoted-printable
>> > X-Google-AttachSize: 1526
>> >
>> > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
>> > <HTML><HEAD>
>> > <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
>> > <META content="MSHTML 6.00.2900.2873" name=GENERATOR>
>> > <STYLE></STYLE>
>> > </HEAD>
>> > <BODY bgColor=#ffffff>
>> > <DIV><FONT size=2>
>> > <DIV><FONT size=2>-- Solaris 2.6 --</FONT></DIV>
>> > <DIV><FONT size=2>-- IDS 9.4 UC4 --</FONT></DIV></FONT></DIV>
>> > <DIV><FONT size=2></FONT> </DIV>
>> > <DIV><FONT size=2>I ran into an apparent contention problem today
>> > with a
>> > stored procedure.</FONT></DIV>
>> > <DIV><FONT size=2></FONT> </DIV>
>> > <DIV><FONT size=2>SPL 'X'</FONT></DIV>
>> > <DIV><FONT size=2></FONT> </DIV>
>> > <DIV><FONT size=2>process 1 executes SPL 'X' many times in
>> > serial</FONT></DIV>
>> > <DIV><FONT size=2></FONT> </DIV>
>> > <DIV><FONT size=2>process 2 executes SPL 'X' once every few
>> > minutes</FONT></DIV>
>> > <DIV><FONT size=2></FONT> </DIV>
>> > <DIV><FONT size=2>process 2 cannot execute SPL 'X' while process 1 is
>> > running -
>> > returns -211 error.</FONT></DIV>
>> > <DIV><FONT size=2></FONT> </DIV>
>> > <DIV><FONT size=2>I have searched Google and found similiar issues but
>> > no solid
>> > solutions - it seems that one or more of the sys* tables is locked in
>> > exclusive
>> > mode which prevents process 2 from executing the SPL.</FONT></DIV>
>> > <DIV><FONT size=2></FONT> </DIV>
>> > <DIV><FONT size=2>Any ideas? Thank you in advance.</FONT></DIV>
>> > <DIV><FONT size=2></FONT> </DIV>
>> > <DIV><FONT size=2>Ben</FONT></DIV></BODY></HTML>
>> >
>> > ------=_NextPart_000_001C_01C6A0FF.DA9D14B0--
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list