SysProcPlan lock problem -211

Henry Benoit

unread,

Mar 16, 2000, 3:00:00 AM3/16/00

to

Hello,

We use : Informix Dynamic Server Version 7.31.UC2A
on a Siemens unix box : ReliantUNIX-Y dbserver 5.45 A1023 RM600
6/3072 R10000

Our database have about 90 views and 175 stored procedures.

Lots of views use stored procedures, for example :
create view .... as
select field,sp_abc(field1,field2),sp_xyz(field3) ....

Sometimes, 'selects' on these views failed within our application
with this error :
INFORMIX ERROR (SQLSTATE: IX000): ISAM error: key value locked
INFORMIX ERROR (SQLSTATE: IX000): Cannot read system catalog
(sysprocplan).
SQLCODE -211: Cannot read system catalog (sysprocplan).

We already investigate, but with no luck.
Has anobody seen this problem ?
What can we do to solve it ???
Is there some bugs related to this ?

Thanks in advance for you help / ideas / advices !

Ben

Rudy Fernandes

unread,

Mar 16, 2000, 3:00:00 AM3/16/00

to

Try updating statistics for the sys tables (the tables in your database with
tabid <= 99)

Rudy

Peter J Diaz de Leon

unread,

Mar 16, 2000, 3:00:00 AM3/16/00

to

On Thu, 16 Mar 2000 12:10:28 +0100, Henry Benoit
<Benoit...@Galvalange.lu> wrote:

>
>
> Hello,
>
>
> We use : Informix Dynamic Server Version 7.31.UC2A
> on a Siemens unix box : ReliantUNIX-Y dbserver 5.45 A1023 RM600
>6/3072 R10000
>
> Our database have about 90 views and 175 stored procedures.
>
> Lots of views use stored procedures, for example :
> create view .... as
> select field,sp_abc(field1,field2),sp_xyz(field3) ....
>
>
> Sometimes, 'selects' on these views failed within our application
>with this error :
> INFORMIX ERROR (SQLSTATE: IX000): ISAM error: key value locked
> INFORMIX ERROR (SQLSTATE: IX000): Cannot read system catalog
>(sysprocplan).
> SQLCODE -211: Cannot read system catalog (sysprocplan).
>
>
> We already investigate, but with no luck.
> Has anobody seen this problem ?
> What can we do to solve it ???
> Is there some bugs related to this ?
>
> Thanks in advance for you help / ideas / advices !
>
> Ben

It might be related to update statistics. If you do an update
statistics high you also need to do an update statistics on the stored
procedures. Otherwise, the stored procedures will get re optimized
the first time they are executed after the update statistics high.
Update statistics on the stored procedure needs to be run as well if
an index that is used in the execution of an SQL statement inside of a
procedure is dropped.

Hope this helps
Peter

Bill Williams

unread,

Mar 16, 2000, 3:00:00 AM3/16/00

to

Henry,

What's happening is that a procedure is accessing a table which has been
altered, and IDS is having to reoptimize the procedure. When this happens a
lock is held on the system catalog for an extensive period of time, leading to
contention problems.

You need to do an 'update statistics for procedure' on a regular basis. Note
that altering any table referenced by a procedure requires that procedure
statistics be updated as well. At a site where I saw a great deal of this kind
of thing, we set up a job in cron to run update statistics for procedure every
day (at a very non-peak time) and this problem all but disappeared. It only
recurred when a DBA altered a table and did not update statistics for procedure
immediately after.

In article <8aqhjf$fmc$1...@news.xmission.com>, Henry says...

>
>
>
> Hello,
>
>
> We use : Informix Dynamic Server Version 7.31.UC2A
> on a Siemens unix box : ReliantUNIX-Y dbserver 5.45 A1023 RM600
>6/3072 R10000
>
> Our database have about 90 views and 175 stored procedures.
>
> Lots of views use stored procedures, for example :
> create view .... as
> select field,sp_abc(field1,field2),sp_xyz(field3) ....
>
>
> Sometimes, 'selects' on these views failed within our application
>with this error :
> INFORMIX ERROR (SQLSTATE: IX000): ISAM error: key value locked
> INFORMIX ERROR (SQLSTATE: IX000): Cannot read system catalog
>(sysprocplan).
> SQLCODE -211: Cannot read system catalog (sysprocplan).
>
>
> We already investigate, but with no luck.
> Has anobody seen this problem ?
> What can we do to solve it ???
> Is there some bugs related to this ?
>
> Thanks in advance for you help / ideas / advices !
>
> Ben

Bill Williams
Informix Software, Inc.

Paul Watson

unread,

Mar 16, 2000, 3:00:00 AM3/16/00

to

This is an update stats problem, run update stats on your SPL

Henry Benoit wrote:
>
> Hello,
>
> We use : Informix Dynamic Server Version 7.31.UC2A
> on a Siemens unix box : ReliantUNIX-Y dbserver 5.45 A1023 RM600
> 6/3072 R10000
>
> Our database have about 90 views and 175 stored procedures.
>
> Lots of views use stored procedures, for example :
> create view .... as
> select field,sp_abc(field1,field2),sp_xyz(field3) ....
>
> Sometimes, 'selects' on these views failed within our application
> with this error :
> INFORMIX ERROR (SQLSTATE: IX000): ISAM error: key value locked
> INFORMIX ERROR (SQLSTATE: IX000): Cannot read system catalog
> (sysprocplan).
> SQLCODE -211: Cannot read system catalog (sysprocplan).
>
> We already investigate, but with no luck.
> Has anobody seen this problem ?
> What can we do to solve it ???
> Is there some bugs related to this ?
>
> Thanks in advance for you help / ideas / advices !
>
> Ben

--
Paul Watson #
WF Software Ltd # You are only young once
Tel: +44 1436 674729 # but you can be immature
Fax: +44 1436 678693 # for ever
www.wfsoftware.com #