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; |
The following table provides examples of querying Data Dictionary tables for space information: