Options MAIN_RESERVED_DBSPACE_MB and TEMP_RESERVED_DBSPACE_MB provide room for checkpoint, commit, and release savepoint operations.
These options determine the reserve space allocation size in the last readwrite dbfile in IQ_SYSTEM_MAIN or IQ_SYSTEM_TEMP, respectively.
The user with the SET ANY PUBLIC OPTION system privilege can limit the amount of space used per connection. In addition, when SAP Sybase IQ runs out of space in IQ main store or the IQ temporary store, the server no longer suspends the transaction that ran out of space until new space is added. The transaction that runs out of space in the IQ main store or the IQ temporary store fails and is rolled back.
The database option MAX_TEMP_SPACE_PER_CONNECTION limits the amount of IQ temporary store space used per connection and tracks temporary store usage for all Data Manipulation Language (DML) statements, in addition to 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.
The default value of the QUERY_TEMP_SPACE_LIMIT database option is 0, which means there is no limit on temporary store usage by queries. To limit the temporary store usage per connection, the DBA can set the MAX_TEMP_SPACE_PER_CONNECTION option for all DML statements, including queries.
When an SAP Sybase IQ database is upgraded from a release prior to version 15.0, the MAX_TEMP_SPACE_PER_CONNECTION database option is set to the default value of 0. You can use sp_iqcheckoptions to find the default and current values of options before and after upgrading, to help determine if the new option settings are appropriate for the upgraded database.