The DBC.DiskSpaceV view returns AMP information about disk space usage at the database or user level, and can report spool space usage. DiskSpaceV figures are calculated only for the space owned by the user submitting the query. To find information about the full system, log on as the topmost user in the hierarchy (usually your site administrative user).
You can determine the amount of space allocated to a profile by querying the MaxProfileSpool and MaxProfileTemp columns of DBC.DiskSpaceV. By querying MaxSpool and MaxTemp of the restricted view (DBC.DiskSpaceVX), you get the space limits for your individual user space. The following queries show the kinds of information you can get from DBC.DiskSpaceV.
| Desired Value | Query |
|---|---|
| Total disk space in system | SELECT SUM(MaxPerm) FROM DBC.DiskSpaceV; |
| Disk space in use | SELECT SUM(CurrentPerm), SUM(MaxPerm), ((Cast(Sum(Currentperm) as float) / NullifZero(Cast(Sum(maxperm) as float)) *100)) (Title '%MaxPerm', Format 'zz9.99') FROM DBC.DiskSpaceV; |
| Disk space for a given database | SELECT sum(maxperm) FROM DBC.DiskSpaceV WHERE databasename='xxxx'; |
| Percent of disk space available for spool | SELECT (((cast(SUM(MaxPerm) as float) - cast(SUM(CurrentPerm) as float)) / NULLIFZERO(cast(SUM(MaxPerm) as float))) * 100) (TITLE'% Avail for Spool', format'zz9.99') FROM DBC.DiskSpaceV; |
| Percent of space used by each database in the system | SELECT Databasename (format 'X(12)') ,SUM(maxperm) ,SUM(currentperm) ,((cast(SUM(currentperm) as float))/ NULLIFZERO (cast(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 PERM space | SELECT Databasename (format 'X(12)') ,SUM(maxperm) ,SUM(currentperm) ,((cast(SUM(currentperm) as float))/ NULLIFZERO (cast(SUM(maxperm) as float)) * 100) (format 'zz9.99%', TITLE 'Percent // Used') FROM DBC.DiskSpaceV GROUP BY 1 HAVING (cast(SUM(currentPerm) as float) / NULLIFZERO(cast(SUM(maxperm) as float))) > 0.9 ORDER BY 4 DESC; |
| Users with high spool use | 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. Sites with more space may have higher tolerance for higher spool usage and spool limits.
|