"Resource governor for prepared statements exceeded"

#1

I'm getting rathar frustrated at this error which has been reported to me
from onsite, our tech rep there is also blaming some database engine
instabilities on the utility that has been throwing this error message.

This utility was written by me and uses ODBC 3.x commands. I have gone
through and checked to make sure that every hstmt that is allocated is
freed. I have also gone through the "-zr all" log and matched each
STMT_PREPARE to a STMT_DROP. I am starting to run out of ideas of what
might be wrong.

The following is the list of all the SQL commands that are generated by this
utility. Are these commands "safe" to be executed once a second or so? Do
they "leak" hstmts? Is there a safer/better way to retrieve this kind of
information? This utility is indended for places that do not have a Sybase
Central installation running...

SA_DB_INFO
SELECT CURRENT TIMESTAMP
SA_CONN_INFO
SA_CONN_PROPERTIES(x)

#2

Considering that these errors only appear very rarely, I'm guessing while
it's been running for hours, I'm thinking that this is actually a statement
leak of some kind, and upping the maximum statement count probably wouldn't
have much effect. Especially after I've gone through the database
request-level log and verified that I'm never using more than one open
statement at a time.

Thank you for your input though.

"Michael Gould" <mgould@*I*h*a*t*e*s**p$a*m*omnicc.com> wrote

Quote:

Erik,

Depending on what software you are using to write your application (we use
Clarion) we had to up the limit on the prepared statements. It defaults to
50. You can do this by setting the max_statement_count option. It
defaults
to 50 and can be set up to 255. If you don't want to limit at all, then
you
can set it to 0. I would try moving up by say 25 statements at a time. If
you find a level before 255 that works then your ok. If not, you have a
couple of options. You can see what is causing all the prepare statements
and do you have control over them (we don't, the drivers control the
prepare
statement executions) so we then set them to 0.

HTH's

Michael Gould

"Erik Anderson" <erikba (AT) teamworkgroup (DOT) com> wrote in message
news:3fd66279$1 (AT) forums-1-dub (DOT) ..
I'm getting rathar frustrated at this error which has been reported to
me
from onsite, our tech rep there is also blaming some database engine
instabilities on the utility that has been throwing this error message.

This utility was written by me and uses ODBC 3.x commands. I have gone
through and checked to make sure that every hstmt that is allocated is
freed. I have also gone through the "-zr all" log and matched each
STMT_PREPARE to a STMT_DROP. I am starting to run out of ideas of what
might be wrong.

The following is the list of all the SQL commands that are generated by
this
utility. Are these commands "safe" to be executed once a second or so?
Do
they "leak" hstmts? Is there a safer/better way to retrieve this kind
of
information? This utility is indended for places that do not have a
Sybase
Central installation running...

SA_DB_INFO
SELECT CURRENT TIMESTAMP
SA_CONN_INFO
SA_CONN_PROPERTIES(x)

#3

There should be no problems with the statements you listed.

To track down the problem, I'd suggest lowering the value of
Max_statement_count so the problem reproduces sooner. If the statements
listed below can be executed more frequently, that will also help shorten
the time needed to reproduce the error. You can monitor the number of
prepared statements for the connection from DBISQL using:
select connection_property('PrepStmt',conn-d)
once you know the connection id for the utility. If you see the value
creeping up, you can confirm a leak. If you are to add some debugging code
to the utility to grab the above value and display/record it, that would
help pinpoint where the problem lies.

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

"Erik Anderson" <erikba (AT) teamworkgroup (DOT) com> wrote

Quote:

I'm getting rathar frustrated at this error which has been reported to me
from onsite, our tech rep there is also blaming some database engine
instabilities on the utility that has been throwing this error message.

This utility was written by me and uses ODBC 3.x commands. I have gone
through and checked to make sure that every hstmt that is allocated is
freed. I have also gone through the "-zr all" log and matched each
STMT_PREPARE to a STMT_DROP. I am starting to run out of ideas of what
might be wrong.

The following is the list of all the SQL commands that are generated by
this
utility. Are these commands "safe" to be executed once a second or so?
Do
they "leak" hstmts? Is there a safer/better way to retrieve this kind of
information? This utility is indended for places that do not have a
Sybase
Central installation running...

SA_DB_INFO
SELECT CURRENT TIMESTAMP
SA_CONN_INFO
SA_CONN_PROPERTIES(x)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

"Resource governor for prepared statements exceeded"

sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general

"Resource governor for prepared statements exceeded" - 12-09-2003 , 06:02 PM

Re: "Resource governor for prepared statements exceeded" - 12-09-2003 , 08:07 PM

Re: "Resource governor for prepared statements exceeded" - 12-10-2003 , 09:42 AM