spool space

Analytics

spool space

I am new to Teradata in general and TD SQL Assistant... how do I check the amount of spool space that is being used when I ran a query?
1 REPLY
Enthusiast

Re: spool space

Here's one way of doing it:

1. zero the peak spoolspace for the user/logon (see sql below)
2. run the sql
3. view the peak spoolspace for the user/logon (see sql below)

SQL to zero peak spoolspace:
lock table dbc.dbase for access
update dbc.databasespace
set peakspoolspace = 0
where tableid = '000000000000'xb
and databaseid = (
sel databaseid
from dbc.dbase
where databasenamei = 'USERNAME')

SQL to view peak spoolspace (all amps):
lock table dbc.diskspace for access
sel databasename (format 'X(10)'),
vproc,
peakspool (format 'z,zzz,zzz,zz9')(char(13)),
maxspool (format 'z,zzz,zzz,zz9')(char(13)),
currentspool (format 'z,zzz,zzz,zz9')(char(13))
from dbc.diskspace
where peakspool > 0
and databasename = 'USERNAME'
order by databasename, vproc

SQL to view peak spoolspace (totals):
lock table dbc.diskspace for access
sel max(peakspool) (format 'z,zzz,zzz,zz9')(char(13)),
min(peakspool) (format 'z,zzz,zzz,zz9')(char(13)),
((min(peakspool)*100)/nullifzero(max(peakspool)))(format 'zz9%')(char(4)),
sum(peakspool) (format 'z,zzz,zzz,zz9')(char(13)),
databasename (format 'X(10)')
from dbc.diskspace
where peakspool > 0
and databasename = 'USERNAME'
group by databasename
order by max(peakspool) desc, databasename