Limiting Temporary dbspace Use By a Query

Set the QUERY_TEMP_SPACE_LIMIT to specify the maximum estimated amount of temp space before a query is rejected.

The QUERY_TEMP_SPACE_LIMIT option causes queries to be rejected if their estimated temp space usage exceeds the specified size. By default, there is no limit on temporary store usage by queries.

Sybase IQ estimates the temporary space needed to resolve the query. If the estimate exceeds the current QUERY_TEMP_SPACE_LIMIT setting, Sybase IQ returns an error:

Query rejected because it exceeds total space resource limit

If this option is set to 0 (the default), there is no limit, and no queries are rejected based on their temporary space requirements.

To limit the actual temporary store usage per connection, set the MAX_TEMP_SPACE_PER_CONNECTION option for all DML statements, including queries. MAX_TEMP_SPACE_PER_CONNECTION monitors and limits the actual run time temporary store usage by the statement. If the connection exceeds the quota set by the MAX_TEMP_SPACE_PER_CONNECTION option, an error is returned and the current statement rolls back.