Question
Error Code: 2646 No more spool space in %DBID
Answer :
What does the error mean?
Error code 2646 could be returned when spool space is not available in the specified database or user for the query.
Error code 2646 may indicate that spool space may have run out on at least one AMP.
Why does the error occur?
The user does not have enough spool space to complete a query.
The profile that the user is assigned to does not have enough spool space. Teradata uses the profile's lower spool space as the max spool space.
Spool has been exhausted on a single AMP. This is a frequent issue caused by an extremely skewed
request. Spool space is allocated to a user, not a session. If multiple people sign on using the same User Id, the last request may run out of spool space because of an excessive request of another session using the same User Id.
What should be done when the error is received?
Check to see if a query being run exceeds the maximum allowed amount of spool space.
The user may have insufficient spool space to run query.
Increase user spool space
The profile the user is assigned to may not have sufficient spool space to run the query.
Increase profile spool space
If spool space keeps running out for the same query.
Check to see that the statistics are up to date
Workaround:
Run UPDATESPACE against database/user who has phantom spool.
> update spool space for systemfe
Query to check phantom spool... can be used to check left over spool, too.
Example:
CurrentSpool should be 0 once query finished.
SELECT DATABASENAME(char(15)), VPROC, CURRENTSPOOL, PEAKSPOOL
FROM DBC.DISKSPACE WHERE DATABASENAME='systemfe';
*** Query completed. 20 rows found. 4 columns returned.
*** Total elapsed time was 1 second.
DatabaseName Vproc CurrentSpool PeakSpool
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
systemfe 4 1,200,000,823,296 1,200,000,823,296
No comments:
Post a Comment