16.10 - FixAllocatedSpace Procedure - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

Purpose

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



Syntax Elements

Use the following syntax elements for FixAllocatedSpace:
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:
  • 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;

 *** 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

For more information on the SPAAGGRTRACKINFOGLOBAL UDF, see Functions, Operators, Expressions, and Predicates.

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);