Modifying the Disk Space Currently Assigned to Databases, Users, or Profiles
Modifying the permanent, temporary, and spool space assignments for databases, user, and profiles is a simple operation using MODIFY DATABASE, MODIFY USER, and MODIFY PROFILE requests as appropriate.
You can use the system views listed here to help determine new values to for permanent, temporary, and spool space assignments. For more information about these views, see Data Dictionary.
Use this view to report disk space usage, including spool space, for any account, database, table, or user.
The following request reports how the space currently used by the department table is distributed on each AMP.
SELECT DatabaseName, TableName, AMP, CurrentPerm
FROM DBC.AllSpaceV
WHERE TableName = ’department’
ORDER BY 1,2,3;
DatabaseName TableName AMP CurrentPerm
------------ ---------- ---- ------------
test department 1-0 1,024
test department 1-1 512
test department 1-2 1,024
test department 1-3 512
personnel department 1-0 2,048
personnel department 1-1 1,536
personnel department 1-2 1,536
personnel department 1-3 1,536
user1 department 1-0 2,048
user1 department 1-1 1,536
user1 department 1-2 1,536
user1 department 1-3 1,536
The following request reports the values for the MaxPerm and CurrentPerm columns for each table contained by user. Because user only contains one table, employee, the request only returns information about employee and All, where the All “table” represents all of the tables contained by the specified database or user. In this case, All represents all of the tables contained by the user named user.
The MaxPerm value for All is the amount of permanent space defined for user. Because user contains only one table, the number of bytes on each AMP is the same for both All and employee. All of the reported values represent the size of the employee table.
Note that employee returns 0 bytes in the MaxPerm column because tables do not have MaxPerm space. Only databases and users have MaxPerm space, represented by All.
SELECT Vproc, TableName (FORMAT ‘X(20)’), MaxPerm, CurrentPerm
FROM DBC.AllSpaceV
WHERE DatabaseName = user
ORDER BY TableName, Vproc;
Vproc TableName MaxPerm CurrentPerm
----- --------- --------- -----------
0 All 2,621,440 64,000
1 All 2,621,440 64,000
2 All 2,621,440 112,640
3 All 2,621,440 112,640
… … … …
0 employee 0 41,472
1 employee 0 41,472
2 employee 0 40,960
3 employee 0 40,960
… … … …
Use this view to report disk space usage, including spool space, for any account, database, or user.
The following request reports the permanent disk space across all AMPs.
SELECT AMP, DatabaseName, CurrentPerm, MaxPerm
FROM DBC.DiskSpaceV;
AMP DatabaseName CurrentPerm MaxPerm
--- ------------ ----------- ---------
. . . .
. . . .
0-0 stst14 0 125,000
0-0 ud12 0 125,000
1-0 atest 1,536 125,000
1-0 a1 0 247,500
1-0 btest 3,584 5,000
1-0 b2test 49,664 250,000
. . . .
. . . .
1-1 atest 1,536 125,000
1-1 a1 0 247,500
1-1 btest 3,584 5,000
1-1 b2test 50,688 250,000
. . . .
. . . .
1-2 atest 1,536 125,000
Similarly, you can submit a request like the following to calculate the percentage of space used by a particular database. Note that the request uses a NULLIFZERO specification to avoid a divide by zero exception.
SELECT DatabaseName, SUM(MaxPerm), SUM(CurrentPerm),
((SUM (CurrentPerm))/ NULLIFZERO (SUM(MaxPerm)) * 100)
(FORMAT ‘zz9.99%’, TITLE ‘Percent // Used’)
FROM DBC.DiskSpaceV
GROUP BY 1
ORDER BY 4 DESC;
This request reports the following information from DBC.DiskSpaceV.
DataBaseName Sum(MaxPerm) Sum(CurrentPerm) Percent
Used
------------ -------------- ----------------- -------
Finance 1,824,999,996 1,796,817,408 98.46%
DBC 12,000,000,006 8,887,606,400 73.98%
Spool_Reserve 2,067,640,026 321,806,848 15.56%
CrashDumps 300,000,000 38,161,408 12.72%
SystemFE 1,000,002 70,656 7.07%
Use this view to report disk space usage excluding spool space, for any account or table.
The following request reports the total disk space currently used by the employee table with its peak space usage.
SELECT SUM(PeakPerm), SUM(CurrentPerm)
FROM DBC.TableSizeV
WHERE TableName = ’employee’;
Sum(PeakPerm) Sum(CurrentPerm)
------------- ----------------
260,608 260,608
In this case, the 2 sums match, indicating that the disk space currently used by the employee table is also its peak space usage.
The following request reports poorly distributed tables by returning the CurrentPerm values for table_2 and table_2_nusi, the only tables contained by user across all AMPs.
SELECT Vproc, TableName (FORMAT ‘X(20)), CurrentPerm, PeakPerm
FROM DBC.TableSizeV
WHERE DatabaseName = user
ORDER BY TableName, Vproc;
Vproc TableName CurrentPerm PeakPerm
----- --------- ----------- --------
0 table_2 41,472 53,760
1 table_2 41,472 53,760
2 table_2 40,960 52,736
3 table_2 40,960 52,736
4 table_2 40,960 52,760
5 table_2 40,960 52,760
6 table_2 40,960 52,272
7 table_2 40,960 52,272
0 table_2_nupi 22,528 22,528
1 table_2_nupi 22,528 22,528
2 table_2_nupi 71,680 71,680
3 table_2_nupi 71,680 71,680
4 table_2_nupi 9,216 9,216
5 table_2_nupi 9,216 9,216
6 table_2_nupi 59,392 59,392
7 table_2_nupi 59,392 59,392
The report indicates that table_2 is evenly distributed across the AMPs, but that table_2_nusi is not, with its current permanent space clumped into spaces of 9,216 bytes, 22,528 bytes, 59,392 bytes, and 71,680 bytes across 2 AMPs each.