Controls a resource governor that limits the maximum number of prepared statements that a connection can use simultaneously.
Integer
50
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.
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 ) |
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.
| Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |