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

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Database configuration » Database options » Alphabetical list of database options

max_statement_count option

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

Allowed values




  PUBLIC role For current user For other users
Allowed to set permanently? Yes, with SET ANY SYSTEM OPTION Yes, with SET ANY SYSTEM OPTION Yes, with SET ANY SYSTEM OPTION
Allowed to set temporarily? Yes, with SET ANY SYSTEM OPTION Yes (current connection only), with SET ANY SYSTEM OPTION No

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 privileges 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 statements you can execute for the APIs supported by SQL Anywhere:

Interface Statement
ADO RecordSet.Close
ADO.NET SADataReader.Close or SADataReader.Dispose
Java resultSet.Close, Statement.Close
ODBC SQLFreeStmt( hstmt, SQL_DROP ) or SQLFreeHandle( SQL_HANDLE_STMT, hstmt )
Note In Java and .NET, statements should be dropped explicitly. Do 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 regarding when the garbage collection routines are executed.

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.