FixCurrentSpace 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™

Counts the permanent, temporary, spool, and persistent spool space used by each object in a database. For databases that use global space allocation, the allocations across the AMPs are fixed. If any AMP has excessive space, this procedure deallocates space appropriately.

Use this procedure to fix phantom spool problems or correct inconsistencies in the DBC.DATABASESPACE table, which might occur because of rare types of system failures.

Syntax

CALL [SYSLIB.] FixCurrentSpace (
  'FixSpaceOption',
  'AllDatabases',
  'AllProxyUsers',
  'FixDatabaseName',
  FixedDatabaseCount
  Errinfo
) [;]

Syntax Elements

Use the following syntax elements for FixCurrentSpace:
Syntax Element Description
SYSLIB The name of the database where the function is located.
FixSpaceOption
The type of current space to be updated. Specify one of the following options:
  • P (permanent)
  • T (temporary)
  • S (spool)
  • PS (persistent spool)
  • A (all options)
It cannot be NULL.
AllDatabases Update current space for all databases. Specify one of the following values:
  • Y
  • N
  • NULL
AllProxyUsers Update current space for all proxy users. Specify one of the following values:
  • Y
  • N
  • NULL
Only 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 current 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

FixCurrentSpace performs the same functions for space fields as the UpdateSpace utility. Because this procedure both calculates current space use and deallocates unused space, it may take some time. FixCurrentSpace uses AMP worker tasks, unlike the UpdateSpace utility, which has separate, dedicated tasks executing the work. For this reason, consider using the UpdateSpace utility instead of the FixCurrentSpace procedure if there is heavy workload in the system.

Example

The following example shows how to execute the procedure to update all the current space fields for all databases.

call SYSLIB.FixCurrentSpace('A', 'Y', 'N', null, cnt, err);