Specifies the maximum estimated amount of temp space before a query is rejected.
DBA permissions are not required to set this option. Can be set temporary for an individual connection or for the PUBLIC group. Takes effect immediately.
If Sybase IQ receives a query that is estimated to require a temporary result space larger than value of this option, it rejects the query with this message:
Query rejected because it exceeds total space resource limit
When set to zero (the default), there is no limit on temporary store usage by queries.
Users may override this option in their own environments to run queries that can potentially fill up the entire temporary store. To prevent runaway queries from filling up the temporary store, the DBA can set the option MAX_TEMP_SPACE_PER_CONNECTION. The MAX_TEMP_SPACE_PER_CONNECTION option monitors and limits actual temporary store usage for all DML statements, not just queries.
In a distributed query processing transaction, Sybase IQ uses the values set for the QUERY_TEMP_SPACE_LIMIT and MAX_TEMP_SPACE_PER_CONNECTION options for the shared temporary store by limiting the total shared and local temporary space used by all nodes participating in the distributed query. This means that any single query cannot exceed the total temp space limit (from IQ_SYSTEM_TEMP and IQ_SHARED_TEMP dbspaces), no matter how many nodes participate.
For example, if the limit is 100 and four nodes use 25 units of temporary space each, the query is within limits. If the sum of the total space used by any of the nodes exceeds 100, however, the query rolls back.