Restricting Concurrent Queries
Set the -iqgovern switch to specify the number of concurrent queries on a particular server. This is not the same as the number of connections, which is controlled by your license.
Limiting Query Temp Space
Set the QUERY_TEMP_SPACE_LIMIT to specify the maximum estimated amount of temp space before a query is rejected.
Limiting Queries by Rows Returned
Set the value of the QUERY_ROWS_RETURNED_LIMIT option to prevent the optimizer from rejecting queries with large result sets.
Forcing Cursors to be Non-Scrolling
Eliminate the temporary store node in queries that return a very large result set to improve performance.
Limiting the Number of Cursors
Set the MAX_CURSOR_COUNT option to prevent a single connection from taking too much available memory or CPU resources.
Limiting the Number of Statements
Set the MAX_STATEMENT_COUNT option to limit the number of prepared statements for a connection can make.
Prefetching Cache Pages
Set the BT_PREFETCH_MAX_MISS option to control prefetch memory behavior.
Controlling the Number of Prefetched Rows
Set the PrefetchRows and PrefetchBuffer parameters to improve performance on cursors under certain conditions. This is a client option that you can set on the ODBC connection dialog, or in the .odbc.ini file.
Controlling File System Buffering
On some file systems, you can turn file system buffering on or off. Turning file system buffering off usually reduces paging and improves performance.
Optimizing the Cache Partitions
Changing the CACHE_PARTITIONS value may improve load or query performance in a multi-CPU configuration.