Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere Server - Database Administration » Configuring Your Database » Database options » Introduction to database options » Alphabetical list of options

 

max_statement_count option [database]

Controls a resource governor that limits the maximum number of prepared statements that a connection can use simultaneously.

Allowed values

Integer

Default

50

Scope

Can be set for an individual connection or for the PUBLIC group. Takes effect immediately. DBA authority required to set this option for any connection.

Remarks

Applications that use prepared statements can receive the error "Resource governor for 'prepared statements' exceeded" if the prepared statements are not explicitly dropped once they are no longer required. The max_statement_count database option is a resource governor that allows a DBA to limit the number of prepared statements used per connection. If an operation would exceed the limit for a connection, an error is generated, indicating that the governor for the resource has been exceeded.

If a connection executes a stored procedure, that procedure is executed under the permissions of the procedure owner. However, the resources used by the procedure are assigned to the current connection.

The database server maintains data structures for each prepared statement a connection creates. These structures are only freed when the application signals to the database server that the prepared statements are no longer needed or if the connection disconnects. To reduce the statement count for a connection, you must execute the equivalent of a DROP STATEMENT request. The following table lists the commands you can execute for the APIs supported by SQL Anywhere:

Interface Statement
ADO RecordSet.Close
ADO.NET SADataReader.Close or SADataReader.Dispose
embedded SQL DROP STATEMENT
Java resultSet.Close, Statement.Close
ODBC SQLFreeStmt( hstmt, SQL_DROP ) or SQLFreeHandle( SQL_HANDLE_STMT, hstmt )
Note

In Java and .NET, it is recommended that you drop statements explicitly. You should not rely on garbage collection to perform this cleanup because the language routines do not issue server calls to deallocate the statement resources. In addition, there is no guarantee of when the garbage collection routines will execute.

If a server needs to support more than the default number of prepared statements at any one time for any one connection, then the max_statement_count setting should be set to a higher value. Note, however, that larger numbers of active prepared statements consume additional server memory. You can disable the prepared statement resource governor entirely by setting the max_statement_count option to 0 (zero), but this is not recommended. Doing so makes the database server vulnerable to shutting down with an out-of-memory condition for applications that do not properly free prepared statements.

See also