LEFT OVER SPOOL IN TERADATA
In this article you will get to know the LEFT OVER SPOOL IN TERADATA
Spool tables are temporary work tables which are created and dropped as queries are executed. It is the job of the database software – not the query – to drop all the spool tables used during the execution of the query. Leftover spool is a case where – because of a database software bug – a spool table is NOT dropped.
Spool tables require a table id. There is a range of tableids exclusively reserved for spool tables (C000 0001 thru FFFF FFFF) and the system cycles through them.
If a spool table is incorrectly not dropped, it remains in existence. Eventually, the system will cycle through all the table ids and reassign the tableid which is in use by our left over spool table. With luck,
1) The presence of this table is detected.
2) The query which was going to use the tableid is aborted with a 2667 error — even though it is innocent of any wrongdoing.
3) The following message is put in the error log:
*** FAILURE 2667 Left-over spool table found : transaction aborted.
In rare cases, the leftover spool table is not detected and the leftover spool is used. Since it was not created by the current transaction, its format is incorrect and the database will crash in an unpredictable way.
Sometimes when a spool table is dropped, but the steps which reduce the tally of spool space currently in use are not completed either because of a hardware error or because the session is aborted. This is phantom or bogus spool. The tallies say space is in use but it is not. Phantom spool does not cause restarts. Unless the space involved is a significant percentage of the total spool reserve, it is just an annoyance.
You need to distinguish left over spool from phantom spool
Left-over spool___spool table is physically left on disk
Phantom spool___spool table no longer exists but DBC.DatabaseSpace table returns spool info “as if” spool still exists
The following query can be run to flag the presence of either variety (real or phantom) of leftover spool:
SELECT DATABASENAME, VPROC, CURRENTSPOOL
WHERE DATABASENAME NOT IN (SEL USERNAME FROM DBC.SESSIONINFO)
AND CURRENTSPOOL > 0
ORDER BY 1,2
Should this query return rows, the next step is to wait five minutes (longer on a very busy system) and run it again.
Then run the utility updatespace for every user on both lists. This can be done while the system is in operation, but it is best done during periods of lower usage.
Update spool space for <<databasename>>
Where <<databasename>> is one of the databasenames returned by the query. Perform this command one time for every unique databasename returned.
Updatespace will correct the phantom spool problem. If the above query still returns rows after updatespace is run, then there are actual leftover spool tables. The way to get rid of them is to perform a database reset. Do not wallow in theory about how to avoid a restart. Do a restart and be operational quickly and safely.
NOTE: The user who caused the leftover spool table to be created must not be logged on when the query is run. If he is, then all spool is considered legitimate by the query. Now, it is not uncommon for some sites to have a user which is nearly always logged on. If the leftover spool was created by such a user, this query will be not report it unless it is run when the system is quiescent.
Filer can also be used by qualified personnel in the GSC to detect real leftover spool, but only on a quiescent system.
So what do you do when you discover leftover spool?
Currently, all supported TDBMS releases have a system setting that eliminates 2667 errors from aborting user transactions and gracefully handle the left-over spool table as noted above. Please contact your Teradata representative to discuss the options and how to enable this setting.
For those with older releases or with a desire to keep things manual, they will need to institute a procedure to detect leftover spool tables on a regular cycle. You want to discover and eliminate them before the system attempts to reuse the id. How quickly the spool table ids are reused is very site dependent, but something near 2 weeks is the median value. You can estimate the time by calculating the time between the last time leftover spool was detect – either in the time signature on the 2667 message, or the time of the database reset – and the previous database restart.
In more recent releases, the user, session, request number, and the time the spool table was created are all displayed in the 2667 error message. If DBQL logs are maintained for the user, it is often possible to isolate the SQL which is responsible. The next step is to get an EXPLAIN plan from that SQL. Sometimes this is enough to find the root cause. If this does not show the problem, it will be necessary to run TSET and get the full information so that an attempt to recreate the problem can be started. When doing this, remember that the query that reported the problem and aborted with the 2667 error is of no interest. The TSET must be run with the query which left the spool behind.
The Space Accounting component of the DBS is constantly updating DBC.DatabaseSpace on a normally running system. The Update Space utility can be run at any time. It will not remove segments that are currently in use. There will be a CPU and I/O activity associated with the operation but amount is so small that it will not be perceptible. The only known issue associated with the use of this utility on an active system is that HUT locks at either the database or table level will cause the associated objects to be skipped. Should this occur, the target object will be automatically re-visited upon completion of the operation.
Below dbsontrol setting can be used to delete leftover spool table
The recommendation is to set it to 9 and it will:
Delete the spool when left over spool found and complete the query processing smoothly. Current txn may be aborted in the event deleting left over spool is not possible.
Check data subtable. If headerless spool found then delete the left over data subtable of the spool. The query is allowed to run smoothly.
DelLeftOverSpool- (Default = 0) This flag is used to enable appropriate actions
on a left over spool or header less leftover spool when encountered.
The flag is bit-encoded field and controls depending on what bits are set.
Bit 0 (0000): (Not Set) Abort when left over spool found.
Bit 0 (0001): (Set) Delete the spool when left over spool found and complete the
query processing smoothly. Current txn may be aborted in the event deleting
left over spool is not possible.
Bit 1 (0010): (Set | Unset) This Bit Set or Unset doesn’t have any impact.
Bit 2 (0100): (Set)Check data subtable. If headerless spool found then delete the
left over headerless spool and abort the query.
Bit 3 (1000): (Set)Check data subtable. If headerless spool found then delete the
left over data subtable of the spool. The query is allowed
to run smoothly.
Note: Bit 0 (set) and Bit 0 (Unset) are complementary to each other in action.
Bit 2 and Bit 3 are complementary to each other in action. When both
of them are set the Bit 2 will be given preference.