Managing Spool Space
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
Spool space for users is updated in the DatabaseSpace table. However, given the possibility of phantom spool, running UPDATESPACE is necessary to clear spool space for users that have logged off, whether they were aborted users or not. After UPDATESPACE has been run, spool space values in DBC.DatabaseSpace reflects 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.