Phantom Spool

[SiMCard]

I have a couple of big runs that rack up to near my MAXSPOOL (150Gb). Whenever I run these, DBC.ALLSPACE continues to report me as using spool (amount varies - 70Mb - 12Gb), even when all my sessions have ended. The only way I've found of correcting this is to bounce my UserID.

Does anyone know if this is preventable / fixable without dropping and re-creating my User ID?

Help much appreciated.

SiM Card

 

[BillDHS]

With appropriate privledges, you can manually reset dbc.DiskSpace.PeakSpool to zero by using the ClearPeakDisk Macro in user DBC.

 

[SiMCard]

Thx, but I didn't quite explain myself properly - the problem I'm having is with CURRENTSPOOL - it is showing as the amounts given above, even when I (and no-one else, I checked!) have no sessions running.

Is there a DBC macro that does this?

SiM Card

 

[tdatgod]

Hi,
What do you mean by 'BOUNCE your user id'? Do you mean drop and recreate it?

Have you reported this problem to the GSC?

--

 

[SiMCard]

Yes and, er, yes (well I've told every on-site NCR bod about it - none have got back to me yet). This happened at a previous site as well, and not just to me! It's not a major issue for running stuff - it is just a pain when my (self-built) monitoring tool has to be adjusted to show the true spool until such time as the DBAs can bounce (yes, drop and re-create) my ID.

If I get any answers to this, I'll post 'em on here - unless someone beats me to it!

SiM Card

 

[tdatgod]

Hi,
After everyone is logged off have you run FERRET SHOWSPACE to verify that SPOOL cylinders is 1 per AMP?

If SPOOL is more than 1 per AMP then there really is left over SPOOL tables.



Do you have any Volatile or Global temporary tables involved?

Cursors?

Although again if everyone is logged off all these should be unmaterialized (deleted) and therefore the sum should be 0.

Have you validated whether the Current spool space is invalid across all AMPS or just on a single AMP?

Is it just your User id or is it incorrect across multiple databases?


Assuming you have access to these tables, both these questions can be answered by the following query (aasuming every one is logged off ( except for the job running this query ).

/* if you are using BTEQ you need a little wider window */
.width 120

SELECT Dbase.DatabaseName (Named DatabaseName),
DataBaseSpace.Vproc (NAMED Vproc),
sum(DataBaseSpace.CurrentSpoolSpace)
(NAMED CurrentSpool,
FORMAT '---,---,---,---,--9'),
sum(DataBaseSpace.PeakSpoolSpace)
(NAMED PeakSpool,
FORMAT '---,---,---,---,--9')
FROM DBC.dbase, DBC.DataBaseSpace
WHERE DataBaseSpace.TableID = '000000000000'XB
AND DataBaseSpace.DatabaseId=dbase.DatabaseId
group by 1,2
order by 1,2
having CurrentSpool > 0;




Do you have any down amps?

 

[SiMCard]

OK - I'll handle things in order...

Unfortunately, there is very rarely time when no-one is logged on to the box (production machine in a retail environment) - I'll just have to monitor it, and try to catch it at the right time.

No - I've never heard of this utility, I could certainly try it.

No - no Volatile / Global temps / cursors involved.

CURRENTSPOOL shows varying amounts across all the AMPs, with no discernable pattern (i.e. it's never the same twice, either - even after my ID is bounced and I re-run the same job against the same data).

At this site it's just my User Id - but I'm the only one running this size of query. At my previous site, it happened intermittently to anyone running large queries (we were building a large financial VA system, so many of us used large amounts of spool).

I'll try the query if I can catch the circumstances noted above. I simply use:

SELECT
 VPROC
,SUM(CURRENTSPOOL)
FROM DBC.ALLSPACE
WHERE
    DATABASENAME = '<USERID>'
AND TABLENAME <> 'ALL' -- EXCLUDE WHEN EXAMINING DATABASES --
GROUP BY 1 ORDER BY 1
;

Don't think any AMPs are down (if it was this regular, I'd be seriously concerned) - I'm sure that either the DBAs or me would have noticed, especially at the previous site.

Thanx thus far - I've got some things to try next week.

SiM Card

 

[tdatgod]

Hi,
But SPOOL is calculated on the 'ALL' table. Therefore your query above should always return '0' since you are excluding ALL.

SELECT
VPROC
,SUM(CURRENTSPOOL)
FROM DBC.ALLSPACE
WHERE
DATABASENAME = '<USERID>'
AND TABLENAME <> 'ALL' -- EXCLUDE WHEN EXAMINING DATABASES --
GROUP BY 1 ORDER BY 1
;

*** Query completed. 2 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

Vproc Sum(CurrentSpool)
------ -------------------
0 0
1 0



if you are actually getting something other than '0' I would be surprised. Your query above works fine for PERM but not for spool.


changing this to '=' I get...


SELECT
VPROC
,SUM(CURRENTSPOOL)
FROM DBC.ALLSPACE
WHERE
DATABASENAME = '<USERID>'
AND TABLENAME = 'ALL' -- USE ALL WHEN EXAMINING SPOOL --
GROUP BY 1 ORDER BY 1
;

*** Query completed. 2 rows found. 2 columns returned.
*** Total elapsed time was 9 seconds.

Vproc Sum(CurrentSpool)
------ -------------------
0 170,089,472
1 170,089,472


---

 

[SiMCard]

Sorry - I cut and pasted the wrong query in - I have a couple next to each other in a reference sheet. The one for the SPOOL calculation, obviously, does include 'ALL'.

I've not had an opportunity to test your questions / interrogation methods yet, but I hope to by the end of the week.

SiM Card

 

[Guest_imported]

Hi Tdatgod,
I also had the same problem and our NCR DBA said this is 'dirty spool' (I posted a message about this before with no answers). The query I used to run is below.

locking dbc for access
select a.databasename ,
commentstring ,
sum(currentspool) as total ,
max(currentspool) as maxi ,
min(currentspool) as mini ,
sum(currentspool) / 40 as peramp ,
100 * (min(currentspool) / max(currentspool) ) as eff ,
(maxi / max(maxspool )) * 100 as failprob ,
count(*)
from dbc.diskspace a,
dbc.databases b
where a.databasename = b.databasename
and currentspool > 0
group by 1 ,2
order by 3 ;

This used to show currentspool usage for users who are not logged in. Some had a session open but are not logged in (they killed their queryman in NT but the logged session in Teradata was left open) whereas some had no sessions open at all. This causes severe problems when one needs to run a huge query and has this 'd'/'p' spool as the per amp quota is hit sooner in atleast one of the amps...
(now that he is gone we are left out)

 

[vijayvg]

My 2 cents.. sorry if I am giving useless info but still there it is .. as passed to us by a NCR DBA..

Phantom Spool can be resolved in several ways :
By a restart of the database
By a utility called update space but this requires access to the remote console
OR the simplest way is to drop and recreate the user. As all the user access should be granted at Usergroup level, recreating a user in the same usergroup will automatically pick up the group access (except in cases it being given additional access rights)..

we now drop and recreate the user id.. Hope somebody finds it useful or does not agree.

 

[teradataman]

Good answer - it seems that there is no known recurring cause, but that it does happen quite a lot. We run the utility that sorts it out.