Spool Space in TD

Spool space is allocated to a logon with a spool space limit parameter. This spool space parameter assigns a spool space limit to each amp. Each amp gets an equal share of the spool space limit. 

Therefore,

                                                      Logon spool space limit
Amp spoolspace limit =   ---------------------------------------------------

                                                            Number of amps

The message “Failure 2646 No more spool space in username” occurs whenever any amp exceeds its individual spoolspace limit.

For example, if the logon is assigned 150MB of spool space, and there are 50 amps, then each amp will get a 3MB spool space limit. And, the 2646 message will occur if any amp exceeds 3MB of spool space.

To check the amount of spool space that is being used:-

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'xband
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,

vprocSQL 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;

To increase user’s spool space:

MODIFY USER  AS SPOOL = xxxxx BYTES;

To increase profile’s spool space:

MODIFY PROFILE  AS SPOOL = xxxxx BYTES;

To check what the user's spool limit is:

SELECT SUM(MaxSpool)
FROM
DBC.DiskSpace
WHERE DatabaseName='';

Run the following query to check whether the user has spool space limited through a profile:

SELECT vproc,maxspool,maxprofilespool,
currentspool,peakspool
FROM dbc.diskspace
WHERE databasename = ''
WITH sum(maxspool), sum(maxprofilespool),
sum(currentspool), sum(peakspool)
ORDER BY 1;