FixAllocatedSpace Procedure - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantageā„¢

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:
  • P (permanent)
  • S (spool)
  • A (all options)

It cannot be NULL.

AllDatabases Update allocated space for all databases. Specify one of the following values:
  • Y
  • N
  • NULL
AllProxyUsers Update allocated space for all proxy users. Specify one of the following values:
  • Y
  • N
  • NULL
Only the proxy users with an assigned profile qualify for the fix space operation.
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

Expressions passed to this procedure must have the following data 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);