17.00 - Examples: Using DiskSpaceV - 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

Example: Using DiskSpaceV to Show Disk Space Currently in Use

To display disk space currently in use:

SELECT SUM(CurrentPerm), SUM(MaxPerm),
((CAST(SUM(currentperm) AS FLOAT)/ NULLIFZERO(SUM(maxperm)) * 100))
(TITLE '%MaxPerm', FORMAT 'zz9.99')
FROM DBC.DiskSpaceV;

Example: Using DiskSpaceV to Show the Percentage of Disk Space Available for Spool

To display the percentage of disk space that is available for spool:

SELECT ((CAST((SUM(MaxSpool) - SUM(CurrentSpool)) AS FLOAT) /
NULLIFZERO(SUM(MaxSpool))) * 100)
(TITLE'% Avail for Spool', format'zz9.99')
FROM DBC.DiskSpaceV;

Example: Using DiskSpaceV to Show the Percentage of Space Used by Each Database

To display percentage of space used by each database in the system:

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

Example: Using DiskSpaceV to Show Users Who are Running Out of Permanent Space

To display the users who are running out of permanent space:

SELECT Databasename (format 'X(12)')
,SUM(maxperm)
,SUM(currentperm)
,(CAST(SUM(currentperm) AS FLOAT)/
NULLIFZERO (SUM(maxperm)) * 100)
(format 'zz9.99%', TITLE 'Percent // Used')
FROM DBC.DiskSpaceV
GROUP BY 1 HAVING (CAST(SUM(currentPerm) AS FLOAT)/
NULLIFZERO(SUM(maxperm))) > 0.5
ORDER BY 4 DESC;
You can change the value 0.9 to whatever threshold ratio is appropriate for your site.

Example: Using DiskSpaceV to Show AMPs That are Running Out of Physical Space

To display AMPs that are close to running out physical space:

SELECT SUM(maxperm) m, SUM(currentperm+currentspool+currenttemp) c , 
((m-c)*100.00)/m (decimal(5,2))  p, vproc 
FROM DBC.diskspacev GROUP BY vproc HAVING p < 5.00 ;
The above query returns all AMPs whose actual usage is leaving less than 5% of their total space. Change 5.00 to another suitable value to as required.

Example: Using DiskSpaceV to Show Users Who are Using a Lot of Spool

To display the 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 a value that is appropriate for your site. Some sites with more space may have a higher tolerance for higher spool usage and spool limits.

Example: Using DiskSpaceV to Show Databases with Permanent Space Skewed

To show users or databases that have permanent space skewed, but are defined with a 0 skewlimit:

SELECT 
COALESCE((MAX(currentPerm)/NULLIFZERO(AVG(currentPerm)) - 1), 0) * 100 (FORMAT '999.99') AS spaceskew, databasename 
FROM DBC.diskspacev GROUP BY 2 
HAVING spaceskew > 125.0
WHERE permskew = 0;
The example query checks databases that have the space usage skewed beyond the normal 25% usage. Change 25.0 to any other value that best suits your query needs. For resulting databases, consider defining a permskewlimit with reduction in the maximum limit to better manage space to avoid over-defining the maximum space limit. SkewLimit > 0 implies need-based space allocations happen to AMPs instead of allocating the sufficient per-AMP quota to each of the AMPs without getting out-of-space errors.

Example: Using DiskSpaceV to Show Current Permanent Usage and AMP Space Allocations

To show current permanent usage and AMP level space allocations for a given database:

SELECT vproc, currentperm, maxperm, AllocatedPerm
FROM DBC.DiskSpaceV
WHERE databasename = 'xxxx'; 
Replace 'xxxx' with a database name, such as 'SYSBAR'.

Example: Using DiskSpaceV to Show the Permanent Space Usage for Databases in a Map

To show the permanent space usage 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 mapname = 'MyMap1' GROUP BY 1;