A Script for Spool Space

Database
Enthusiast

A Script for Spool Space

I have one scrpit out there that shows a session's current spool space.  However, it uses a WITH clause and we are on 13.10 which does not allow WITH clauses.

Does anyone have a script that can pull the current spool space used by session without the WITH?

3 REPLIES
Junior Contributor

Re: A Script for Spool Space

WITH is supported since V2R6, only multiple CTEs are a TD14 feature.

Can you show the script? Spool space is returned by a simple SELECT FROM dbc.DiskSpaceV.

Enthusiast

Re: A Script for Spool Space

You're right.  I mixed up my error msgs.  Here is the query and the error message:

SELECT 

DATABASENAME, VPROC, CURRENTSPOOL

FROM    DBC.DISKSPACE--; --a where AccountName Like 'PFOCUS%';

WHERE DATABASENAME NOT IN (SEL USERNAME FROM DBC.SESSIONINFO b)

AND CURRENTSPOOL > 0

ORDER BY 1,2

WITH    SUM(currentspool);

Executed as Single statement.  Failed [1005 : HY000] Unexpected parcel kind received: 33 

Elapsed time = 00:00:03.352 

Junior Contributor

Re: A Script for Spool Space

This old syntax is only supported by BTEQ/CLI.

You might switch to GROUPING SETS instead:

SELECT 
DATABASENAME, VPROC, SUM(CURRENTSPOOL) AS CURRENTSPOOL
FROM DBC.DISKSPACE--; --a where AccountName Like 'PFOCUS%';
WHERE DATABASENAME NOT IN (SEL USERNAME FROM DBC.SESSIONINFO b)
AND CURRENTSPOOL > 0
GROUP BY GROUPING SETS ((DATABASENAME, VPROC), ())
ORDER BY GROUPING(DATABASENAME), 1, GROUPING(VPROC), 2
;