17.00 - Example Requests for Space Information - Teradata Database

Teradata Vantage™ - Data Dictionary

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Administration
Programming Reference
featnum
B035-1092-170K

About These Requests

The following table provides examples of querying Data Dictionary tables for space information:
Information Required Example Request
Total disk space in the system
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;
Disk space currently in use
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;
Disk space for a given database
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';
Percentage of disk space that is available for spool
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;
Percentage of space used by each database in the system
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);
Users who are running out of permanent space
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.

Users who are using a lot of spool
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.

Users and databases with a non-zero skew limit on permanent space (global PERM space accounting)
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.

Users and databases having permanent space skewed but defined with 0 skewlimit
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.

Current permanent usage and AMP level space allocations for a given database
SELECT vproc, currentperm, maxperm, AllocatedPerm
FROM DBC.DiskSpaceV
WHERE databasename=‘xxxx’ order by vproc;
Allocation for databases that have skewed permanent usage defined
SELECT databasename, allocatedperm, maxperm
FROM DBC.GlobalDBSpaceV
WHERE permskew > 0 or permskew IS NULL;
Unallocated space remaining in the database
SELECT databasename, allocatedperm, maxperm,maxperm-allocatedperm (TITLE 'UnallocatedSpace'), permskew
FROM DBC.GlobalDBSpaceV;
AMPs that are almost running out physical space
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.

The permanent space use for databases and users associated with a given default map
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;
The size of the tables defined in a given map in a given database
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;
Permanent space use by map for each database in each AMP
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;
Permanent space use by map for each database in the system
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;