Deallocates unused permanent, temporary, and spool space allocated for a database that uses global space allocation. It can update space for one or more databases.
Fixes the inconsistencies between the AMP-level space allocations in the DBC.DatabaseSpace table and the global-level space allocations in the DBC.GlobalDBSpace table.
Syntax
CALL [SYSLIB.] FixAllocatedSpace ( 'FixSpaceOption', 'AllDatabases', 'AllProxyUsers', 'FixDatabaseName', FixedDatabaseCount Errinfo ) [;]
Syntax Elements
| Syntax Element | Description |
|---|---|
| SYSLIB | The name of the database where the function is located. |
| FixSpaceOption | The type of allocated space to be updated. Specify one of the following options:
It cannot be NULL. |
| AllDatabases | Update allocated space for all databases. Specify one of the following values:
|
| AllProxyUsers | Update allocated space for all proxy users. Specify one of the following values:
|
| FixDatabaseName | Target database name. It is required if both AllDatabases and AllProxyUsers are not specified (have a value of N or NULL). FixDatabaseName should be NULL if either AllDatabases or AllProxyUsers has a value of Y. |
| FixedDatabaseCount | Output count of databases whose allocated space was fixed. |
| ErrInfo | This is NULL if the fix space operation was successful. Otherwise, it provides information about the error that caused the procedure to stop before the operation completed. |
Authorization
You must have EXECUTE privileges on the stored procedure or on the SYSLIB database.
Argument Types
| Data Type | Value |
|---|---|
| FixSpaceOption | CHAR(2) CHARACTER SET LATIN |
| AllDatabases | CHAR(1) CHARACTER SET LATIN |
| AllProxyUsers | CHAR(1) CHARACTER SET LATIN |
| FixDatabaseName | VARCHAR(128) CHARACTER SET UNICODE |
Usage Notes
Extra space may be allocated to AMPs in expectation of future work, which may not arrive. This extra space may be needed by other AMPs for the same database or by another database in the same AMP. This unused space may increase over time, and the automatic deallocation that is supposed to occur may not have happened for some reason. Use this procedure to return unused space to the global pool.
When the current space usage is 0, the allocated space is not deallocated, even after running this procedure.
When To Use This Procedure
This procedure applies only to databases that are using global space accounting. Use this procedure after executing the SPAAGGRTRACKINFOGLOBAL UDF if the results of that UDF show any inconsistency between allocated space values across vprocs and the total allocated value in DBC.GlobalDBSpace. For example, in the following, the sum of AllocatedPerm across all AMPs is not consistent with the allocated space of DBC.GlobalDBSpace:
select base.databasename databasename,
sum(dbspace.allocatedpermspace) dbspaceallocperm,
syslib.SPAAGGRTRACKINFOGLOBAL(cast(dbglobal.Trackinfo as byte(3880)),'P') delta,
dbglobal.allocatedpermspace globalallocperm
from dbc.databasespace dbspace,dbc.globaldbspace dbglobal,dbc.dbase base
where dbspace.databaseid=dbglobal.databaseid and dbspace.databaseid=base.databaseid and base.databasename='db1' and dbspace.tableid='00000000'XB
group by 1,3,4;
Result:
*** Query completed. One row found. 4 columns returned.
*** Total elapsed time was 1 second.
databasename db1
dbspaceallocperm 1,999,900
delta 100
globalallocperm 2,000,000
Example
The following example shows how to execute FixAllocatedSpace to update all the allocated space fields for the database Travel.
call SYSLIB.FixAllocatedSpace('A', 'N', 'N', 'Travel', cnt, err);