Sybase IQ provides a set of database options that the DBA can use to control resources. These options are called resource governors.
You can set database options using the SET OPTION statement, which has the following syntax:
SET [ TEMPORARY ] OPTION ... [ userid. | PUBLIC. ]option-name = [ option-value ]
For reference information about options, see Chapter 2, “Database Options,” in Sybase IQ Reference Manual. For information on the SET OPTION statement, see Chapter 6, “SQL Statements,” in Sybase IQ Reference Manual.
The following database options can be used to manage resources. See Chapter 4, “Managing System Resources” in Sybase IQ Performance and Tuning Guide or see the Sybase IQ Reference Manual for more information on these options.
CURSOR_WINDOW_ROWS Defines the number of cursor rows to buffer.
LOAD_MEMORY_MB Sets an upper bound for the amount of heap memory that subsequent load operations can use.
MAIN_CACHE_MEMORY_MB Sets the size of the cache for the main IQ Store. (The server option -iqmc is the recommended way to set the main cache size.)
MAX_CARTESIAN_RESULT Limits the number of result rows from a query containing a cartesian join.
MAX_IQ_THREADS_PER_CONNECTION Sets the number of processing threads available to a connection for use in IQ operations.
TEMP_CACHE_MEMORY_MB Sets the size of the cache for the IQ Temporary Store. (The server option -iqtc is the recommended way to set the temp cache size.)
QUERY_TEMP_SPACE_LIMIT Limits the amount of temporary dbspace available to any one query.
QUERY_ROWS_RETURNED_LIMIT Tells the query optimizer to reject queries that might consume too many resources. If the optimizer estimates that the result set from the query will exceed the value of this option, the optimizer rejects the query and returns an error message.
The following database options affect the engine, but have limited impact on Sybase IQ:
JAVA_HEAP_SIZE Sets the maximum size (in bytes) of that part of the memory that is 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.
Database option settings are not inherited through the group structure.
