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;