ASA Database Administration Guide
  Managing User IDs and Permissions

Managing the resources connections use

Building a set of users and groups allows you to manage permissions on a database. Another aspect of database security and management is to limit the resources an individual user can use.

For example, you may wish to prevent a single connection from taking too much of the available memory or CPU resources, so you can avoid a having a connection slow down other users of the database.

Adaptive Server Anywhere provides a set of database options that the DBA can use to control resources. These options are called resource governors.

Setting options 

You can set database options using the SET OPTION statement, with the following syntax:

SET [ TEMPORARY ] OPTION ... [
     userid. | PUBLIC. ]option-name = [
     option-value ]

For more information about options, see Database Options. For information on the SET OPTION statement, see SET OPTION statement.

Resources that can be managed 

You can use the following options to manage resources:

• JAVA_HEAP_SIZE    Sets the maximum size (in bytes) of the part of the memory allocated to Java applications on a per connection basis.

• MAX_CURSOR_COUNT    Limits the number of cursors for a connection.

• MAX_STATEMENT_COUNT    Limits the number of prepared statements for a connection.

• BACKGROUND_PRIORITY    Limits the impact requests on the current connection have on the performance of other connections.

Database option settings are not inherited through the group structure.