2667 Left-over spool table found

Problem:

Query aborted with error 2667 Left-over spool table found.

Following error in DBQL log.

Following message, you can see in the /var/log/messages

# psh "grep -B9 -A9 '2667 #Left-over spool table found' /var/log/messages|grep session"

Example Output:

<--------------------- localhost -------------------------------->

Apr 17 13:06:29 SMP001-01 Teradata[10581]: session number: 10558771

Apr 17 13:06:29 SMP001-01 Teradata[11119]: session number: 10558771

Apr 18 07:53:58 SMP001-01 Teradata[11523]: session number: 10521169

Apr 18 07:53:58 SMP001-01 Teradata[11119]: session number: 10521169

Apr 18 08:07:03 SMP001-01 Teradata[10776]: session number: 10558771

Apr 18 08:07:03 SMP001-01 Teradata[10523]: session number: 10558771

....

Cause:

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.

Solution:

Please note that there is a distinguish between 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

There is an internal setting that can be set to delete left over spool. The internal dbscontrol parameter is “Delete Left Over Spool”. No database restart is needed. This change will become effective after the DBS Control Record has been written. The setting does NOT prevent left-over spool, but it will be deleted when it is detected.

Following way, you can change the parameter. If you have Teradata support, then consult with Teradata Support before doing the change as this is internal parameter.

# dbscontrol

_______

| | |

| ___ __ ____ | ____ __|__ ____

| / |/ \ ____| ____| ____| | ____|

| --- | / | / | / | | / |

| \___ | \____| \____| \____| |__ \____|

Release 16.20.24.01 Version 16.20.24.01

DBSControl Utility (Dec 99)

The current DBS Control GDO has been read.

Enter a command, HELP, or QUIT:

mod systemfe=T

The SystemFE field has been modified from FALSE to TRUE.

WARNING: Running DBSControl in SystemFE mode may result in

inconsistent system behavior!

Are you sure you want to do this (Y/N)?

Y

Enter a command, HELP, or QUIT:

DISPLAY INTERNAL

.

171. Delete Left Over Spool = 0

.

Enter a command, HELP, or QUIT:

modify internal = true

The Internal field has been modified from FALSE to TRUE.

Enter a command, HELP, or QUIT:

modify internal 171 = 9

The Delete Left Over Spool field has been modified from 0 to 9.

NOTE: This change will become effective after the DBS Control Record

has been written.

Enter a command, HELP, or QUIT:

modify internal = false

The Internal field has been modified from TRUE to FALSE.

Enter a command, HELP, or QUIT:

mod systemfe=F

The SystemFE field has been modified from TRUE to FALSE.

Enter a command, HELP, or QUIT:

write

Locking the DBS Control GDO...

Updating the DBS Control GDO...

Enter a command, HELP, or QUIT:

QUIT

Exiting DBSControl...

Information about the Parameter “Delete Left Over Spool”

171. 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.

Actions Bits to be set.

======= ===============

Left Over Spool Found

*********************

Abort left over spool 1. Bit 0 set to false or

2. All bits are zero i.e.,DelLeftOverSpool = 0

Delete the left over 1. Bit 0 is set to true.

spool when found and run

the query smoothly.

Header Less Spool Found

***********************

Delete the header less spool 1. Bit 2 set to true or

and abort the query. 2. Bit 2 and Bit 3 are both set to true

Delete on the left-over data 1. Bit 3 is set to true.

suitable of the spool and the

query allowed to run smoothly.

Take snapshot dump when a left over 1. Bit 4 is set to true.

spool is detected.

If you have issue with Phantom spool space please visit http://www.nazmulhuda.info/2646-no-more-spool-space-for-phantom-spool-space