Example Requests for Space Information - Advanced SQL Engine - Teradata Database

SELECT SUM(MaxPerm) FROM DBC.DiskSpaceV;

You could also use the following request. The information is in a single row for a given database and the request is more efficient. DBC.DiskSpaceV has one row per database per AMP, so it scans more rows.

SELECT SUM(MaxPerm) FROM DBC.GlobalDBSpaceV;

SELECT SUM(CurrentPerm), SUM(MaxPerm),
(cast(SUM(currentperm) as float) / cast((NULLIFZERO(SUM(maxperm))) as float) * 100)
(TITLE '%Diskspace_in_use', FORMAT 'zz9.99')
FROM DBC.DiskSpaceV;

SELECT sum(MaxPerm) FROM DBC.DiskSpaceV WHERE
databasename='xxxx';

The preceding request uses a partition scan to obtain the result. This processing is expected to be very efficient. Alternatively, use the following request; notice aggregation is not required, and this request is even more efficient because it is a row-hashed look up.

SELECT MaxPerm
FROM DBC.GlobalDBSpaceV WHERE
databasename='xxxx';

SELECT ((cast((SUM(MaxPerm) - SUM(CurrentPerm)) as float)/cast((NULLIFZERO(SUM(MaxPerm))) as float))*100) (TITLE'% Avail for Spool', format'zz9.99')
FROM DBC.DiskSpaceV;

SELECT Databasename (format 'X(12)')
,SUM(maxperm)
,SUM(currentperm)
,(((cast(SUM(currentperm) as float))/
cast(NULLIFZERO(SUM(maxperm)) as float)) * 100)
(FORMAT 'zz9.99%', TITLE 'Percent // Used')
FROM DBC.DiskSpaceV
GROUP BY 1
ORDER BY 4 DESC
WITH SUM (currentperm), SUM(maxperm);

SELECT Databasename (format 'X(12)')
,SUM(maxperm)
,SUM(currentperm)
,(((cast(SUM(currentperm) as float))/
cast(NULLIFZERO(SUM(maxperm)) as float)) * 100)
(format 'zz9.99%', TITLE 'Percent // Used')
FROM DBC.DiskSpaceV
GROUP BY 1 HAVING ((cast(SUM(currentperm) as float))/cast(NULLIFZERO(SUM(m
axperm)) as float)) > 0.9
ORDER BY 4 DESC;

You can change the value 0.9 to whatever threshold ratio is appropriate for your site.

SELECT databasename
,SUM(peakspool)
FROM DBC.DiskSpaceV
GROUP BY 1 HAVING SUM(peakspool) > 5000000000
ORDER BY 2 DESC;

You can change the value 500000000 to whatever value is appropriate for your site. Some sites with more space may have greater tolerance for higher spool usage and spool limits.

SELECT databasename
,permskew
FROM DBC.GlobalDBSpaceV
WHERE permskew > 0
ORDER BY 2 DESC;

The request provides the users and databases for which the system allocates space dynamically to AMPs as needed. The skew limit allows some AMPs to exceed the per-AMP quota.

SELECT
COALESCE((cast(MAX(currentPerm) as float)/cast(NULLIFZERO(AVG(currentPerm)) as float) - 1), 0) * 100
(FORMAT '9999.99') AS spaceskew, databasename
FROM DBC.diskspacev GROUP BY 2
HAVING spaceskew > 125.0
WHERE permskew = 0;

The request checks databases that have space usage skewed beyond a normal 25%. Change 125.0 to any value that suits your needs. For resulting databases, you may want to consider defining a smaller permskewlimit to better manage space. SkewLimit > 0 implies need-based space allocations for AMPs.

SELECT vproc, currentperm, maxperm, AllocatedPerm
FROM DBC.DiskSpaceV
WHERE databasename=‘xxxx’ order by vproc;

SELECT databasename, allocatedperm, maxperm
FROM DBC.GlobalDBSpaceV
WHERE permskew > 0 or permskew IS NULL;

SELECT databasename, allocatedperm, maxperm,maxperm-allocatedperm (TITLE 'UnallocatedSpace'), permskew
FROM DBC.GlobalDBSpaceV;

SELECT SUM(maxperm) m, SUM(currentperm+currentspool+currenttemp) c ,
cast(((m-c)*100.00) as float)/cast(m as float) (format 'zz9.99%')  p, vproc
FROM DBC.diskspacev group by vproc having p < 5.0;

The preceding request returns all AMPs whose actual usage leaves less than 5% of their total space remaining. Change 5.00 to another value as required.

SELECT dbv.databasename, sum(currentperm), sum(maxperm)
FROM DBC.DiskSpaceV dbspace, DBC.DatabasesV dbv WHERE
dbspace.databasename = dbv.databasename
AND defaultmapname='td_map5' GROUP BY 1;

SELECT tabsz.databasename, tabsz.tablename, vproc, sum(currentperm)
FROM DBC.TableSizeV tabsz, DBC.TablesV tabv
WHERE tabsz.databasename = tabv.databasename
AND tabsz.tablename = tabv.tablename AND tabv.mapname='td_map1' and tabsz.databasename='SysAdmin'
GROUP BY 1,2,3
ORDER BY 1,2,3;

SELECT tabv.mapname, tabv.databasename, vproc, sum(currentperm)
FROM DBC.TableSizeV tabsz, DBC.TablesV tabv
WHERE tabsz.databasename = tabv.databasename
AND tabsz.tablename = tabv.tablename
GROUP BY 1,2,3
ORDER BY 1,2,3;

SELECT tabv.mapname, tabv.databasename, sum(currentperm)
FROM DBC.TableSizeV tabsz, DBC.TablesV tabv
WHERE tabsz.databasename = tabv.databasename
AND tabsz.tablename = tabv.tablename
GROUP BY 1,2
ORDER BY 1,2,3;

The maximum allocated peak spool for a given user

SELECT databasename, PeakAllocatedSpool from DBC.GlobalDBSpaceV;