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
>
>
> 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
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.
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 #