Specifies the maximum estimated amount of temp space before a query is rejected.
Option can be set at the database (PUBLIC) or user level. When set at the database level, the value becomes the default for any new user, but has no impact on existing users. When set at the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.
Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.
If SAP 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, a user with the SET ANY SYSTEM OPTION system privilege 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, SAP 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.