Troubleshooting Spool Space Problems
The system automatically creates and drops spool files which it uses as temporary work tables to execute queries. Sometimes, one of two potential problems can occur with the spool files:
Leftover spool. Leftover spool occurs when the system fails to properly drop the spool file after the execution of a query completes. The spool that the query was using appears frozen without a session. This may result in error 2667 which aborts the transaction. Note that with error 2667, the system terminates the query not because the query was faulty, but as a result of the leftover spool problem.
- “ Phantom”spool. Phantom spool occurs when the DBC.DatabaseSpace table is not properly updated to reflect current and actual space usage.
To check for either types of spool problems, run the following query when the system is most quiescent and when there are the least amount of users logged on:
SELECT DATABASENAME, VPROC, CURRENTSPOOL
WHERE DATABASENAME NOT IN (SEL USERNAME FROM DBC.SESSIONINFO)
AND CURRENTSPOOL > 0
ORDER BY 1,2
Note: If the user whose query unintentionally caused the spool space problem is logged on at the time you run this query, the query will not detect the user because the query is looking for spool usage for a user that is holding spool space but is not logged on.
If the query returns rows, run the Update Space (updatespace) utility to update the spool space accounting in DBC.DatabaseSpace for each of the databases.
Using the Update Space Utility
The Update Space utility (updatespace) recalculates the permanent, temporary, or spool space used by either of the following:
A single database and its individual tables
- All databases in a system and their individual tables
- The Update Space utility accomplishes this by:
- Examining storage descriptors and adding up space for each table.
- Seting values in CurrentPermSpace, CurrentTempSpace, or CurrentSpoolSpace in the DBC.DatabaseSpace table for each table and for the containing database as a whole.
A different utility, Update DBC (updatedbc), recalculates the maximum allowed values for permanent, temporary, and spool space in the DBC.Dbase and DBC.DatabaseSpace tables.
The following are the difference between the Update DBC and Update Space utilities:
- Update DBC recalculates maximum allowed values for permanent, temporary, and spool space.
- Update Space recalculates current usage for permanent, temporary, and spool space.
- The only reason to use Update Space is to correct inconsistencies in the DatabaseSpace table, which might occur as the result of rare types of system failures.
- The format of the command to use with the “Update Space” utility is:
If the problem is fixed using Update Space, then the problem was phantom spool. If running Update Space does not fix the problem, you have leftover spool and the only way to fix the problem is to restart the system.