Managing spool space allocation for users can be a method to control both space utilization and potentially bad (that is, non-optimized) queries.
Spool Space and Perm Space
Spool space is allocated to a user. If several users are active under the same logon and one query is executed that exceeds the limit of the spool space allocated, all active queries for that user that require spool will likewise be denied additional spool and will be aborted.
If space is an issue, it is better to run out of spool space than to run out of permanent space. A user requesting additional permanent space will do so to execute queries that change tables (inserts or updates, for example). Additional spool requests are almost always done to support a SELECT. Selects are not subject to rollback.
To configure this, see “Cylinders Saved for PERM” in Utilities.
Spool Space Accounting
- Run the Update Space utility or execute the SQL stored procedure FixCurrentSpace, which performs the same functions as the Update Space utility; see FixCurrentSpace Procedure
- Execute the SQL stored procedure FixAllocatedSpaceSpace to fix the inconsistencies between the AMP-level space allocations in the DBC.DatabaseSpace table and the global-level space allocations in the DBC.GlobalDBSpace table; see FixAllocatedSpace Procedure
After running Update Space or FixCurrentSpace, spool space values in DBC.DatabaseSpace reflect actual spool usage.
Phantom spool cases are those in which the DBC.DatabaseSpace table indicates that there is spool, although no spool exists on the disk. Phantom spool cases are not the same as “left-over spool” cases. Left-over spool cases are those in which spool is actually created and exists on the disk, although a request completed its execution.
Using Spool Space as a Trip Wire
Lowering spool space may be a way to catch resource-intensive queries that will never finish or that will run the entire system out of free space if the user is allocated a very high spool space limit.
In the interest of system performance, do not allocate high spool space limits to all users and, in general, be very conservative in setting spool space limits.